Locks

Hi! When using COMMITTED READ as the isolation level, if a transaction (A) executes a SELECT...<an unique row>...FOR UPDATE and another transaction (B) does the same over the same row, this last transaction (B) will get the row... How can I know within transaction B that the record is being locked by another transaction? Is there a way to do this without using the sysmaster database (syslocks table)? I want the select in transaction B to wait until the lock is released (or get an error message, if the LOCK MODE is set to NOT WAIT). I tried to use REPEATABLE READ as the isolation level and it worked, but (why is there always a "BUT"?) I can't use repeatable read because it locks every row that evaluates... Which leads me to another question: can two transactions update a table at a time, if they're changing different rows? Thanks in advance, Mariana. -----------== Posted via Deja News, The Discussion Network ==---------- http://www.dejanews.com/ Search, Read, Discuss, or Start Your Own

marianat@my-dejanews.com wrote: > > Hi! > > When using COMMITTED READ as the isolation level, if a transaction (A) > executes a SELECT...<an unique row>...FOR UPDATE and another transaction (B) > does the same over the same row, this last transaction (B) will get the > row... How can I know within transaction B that the record is being locked by > another transaction? Is there a way to do this without using the sysmaster > database (syslocks table)? I want the select in transaction B to wait until > the lock is released (or get an error message, if the LOCK MODE is set to NOT > WAIT). I tried to use REPEATABLE READ as the isolation level and it worked, > but (why is there always a "BUT"?) I can't use repeatable read because it > locks every row that evaluates... Which leads me to another question: can two > transactions update a table at a time, if they're changing different rows? So let me get this straight. You are using DIRTY READ so only the currently fetched row is locked but you want to lock other users out of updating other rows? Or you just do not want the other user to FETCH that row? Assume the latter. You have to implement concurrency handling ala Sybase. Add a timestamp column to the table and make sure, perhaps through a trigger that the column will always be updated with the row. User A fetches and modifies the row, before A executes the UPDATE...WHERE CURRENT OF... user B reads the row and begins to modify it. Now A updates and B goes to update. B re-fetches the row, by rowid or primary key, and checks the timestamp against the original one. It has been modified by user A's update. User B is notified that someone else has modified the row and presented with the modified row A saved to modify again. Art S. Kagel

marianat@my-dejanews.com wrote: > > Hi! > > When using COMMITTED READ as the isolation level, if a transaction (A) > executes a SELECT...<an unique row>...FOR UPDATE and another transaction (B) > does the same over the same row, this last transaction (B) will get the > row... How can I know within transaction B that the record is being locked by > another transaction? Why should the one (B) who got the lock know that another transaction is waiting for him ? If you need it you must write a logical locking mechanism like Art S. Kagel replied. > Is there a way to do this without using the sysmaster > database (syslocks table)? I want the select in transaction B to wait until > the lock is released (or get an error message, if the LOCK MODE is set to NOT > WAIT). Now this sounds a bit confusing. As you mentioned in your first sentence, transaction B got the row and so I think the lock ? > I tried to use REPEATABLE READ as the isolation level and it worked, > but (why is there always a "BUT"?) I can't use repeatable read because it > locks every row that evaluates... If you would use a CURSOR ... FOR UPDATE, the server will place an UPDATE LOCK on the current row/page. A second process has to wait if it set LOCK MODE TO WAIT otherwise it will get an error message. This is true for all isolation levels. The first difference between REPATABLE READ and the other levels is that REPEATABLE READ will not release the UPDATE LOCK on a row, even if you didn't change the row/page. > Which leads me to another question: can two > transactions update a table at a time, if they're changing different rows? Sure, as long as you ( or the optimizer/server ) will not place a lock on the table. The optimizer might place a lock on the whole table if you use REPEATABLE read ( the second difference between REPEATABLE read and the other isolation levels ). > > Thanks in advance, > Mariana. > > -----------== Posted via Deja News, The Discussion Network ==---------- > http://www.dejanews.com/ Search, Read, Discuss, or Start Your Own Best regards, Stefan Weideneder