Pessimistic locking (Oracle)

[hussux]

HELP!
I need to know if Pessimistic locking is possible in Oracle, and if so how do you implement it???

Reason being is I need to implement record locking in to multi-user environment, so that when a user has a record open - no other user can access it until the user has 'moved' away from the record.

techy bits: Using Oracle 8i, Visual Basic 6, ADO direct connection.

any help is greatful!

 

[donpablo]

You can explicitly lock a record using the "select for update" statement. Oracle will lock the record and keep it
locked until you issue a commit or a rollback. I have used this in OLE and it works fine. One caveat however, make sure your application does in fact issue a commit or rollback (even if the user reboots his/her machine) otherwise that record will be locked forever. (well maybe not forever but you get the picture).

Example SQL of "select for update"

select * from customers where cust_id = 123 for update

 

[hussux]

i've tried the Select for update statement without success,

SELECT * From myQueries where QueryNo = 5 FOR UPDATE
I get an error:-

ORA-01002: Fetch out of sequence

any suggestions?