I'm interested in how I can implement pessimistic locking, with very specific behavior. (The reason I tagged the question with Sybase+Oracle+MSSQL, is because I'd be happy with a solution or "that's impossible!" for any one of them)
What I want is this: 1 - be able to lock a row (so that process can later do update, but no other process can lock the row) 2 - when another process tries to lock same row it should get notification that record is locked - I don't want this process to hang (I believe simple timeout can be used here) 3 - when another process tries to read record, it should be able to read it the way it is currently in database (but I don't want to use dirty reads).
The above 3 requirements are currently solved by application using shared memory - and performing record-locking outside database. I'd like to move the locking into the database.
So far, I'm having conflicts between #1 and #3 - if I lock record by doing 'update ...' by updating a field to same value, than 'select' from another process hangs.
Edit: I'm having some luck now with snapshot isolation level on MSSQL. I can do both the locking, and reads without using dirty reads.
The reason I don't want to use dirty-reads, is that if a report is running, it might read multiple tables, and issue multiple queries. Snapshot gives me a consistent snapshot of the datatabase. With a dirty read, I'd have mismatching data - if there were any updates in the middle.
I think Oracle has snapshot as well, so now I'm most interested in Sybase.
In Oracle you can use select for update nowait to lock a record.
select * from tab where id=1234 for update nowait;
If another process try to execute the same statment it gets an exception:
ORA-00054: resource busy and acquire with NOWAIT specified
until the first process(session) performs commit or rollback.
normally, oracle don't permit dirty reads