CODEWITHSUNDEEP
sql-serveroracle-databasesap-ase
Alex
Alex

Reputation: 1232

Implement pessimistic locking

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.

Upvotes: 2

Views: 893

Answers (2)

schurik
schurik

Reputation: 7928

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

Upvotes: 2

Argeman
Argeman

Reputation: 1353

Your described conflict between #1 and #3 is a logical one: you can either let the database do dirty reads OR you block the reads. If you could read the locked row, it is a dirty read by definition. That has nothing to do with the specific database system you use!

So if you want it that way: Yes, what you want is impossible with all 3 systems because it hurts the definition of "dirty read".

Upvotes: 0

Related Questions