Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

All Forums  SQL Server 2005 Forums  Transact-SQL (2005)  XLOCK, READPAST -- READ COMMITTED SNAPSHOT

Author  Topic  rkudu2 Starting Member 1 Post Posted - 2010-05-25 : 19:23:08 I am working on an application in which IDs of specific format are created and stored in a configuration table (config_IDPool) . When ever a process requests an ID, we return an ID which is not consumed and insert a record in a table (table_IDPool) specifying that the ID has been consumed by that user. Isolation at the DB level is READ COMMITTED SNAPSHOT. I am opening an explicit transaction in the stored procedure with READ COMMITTED isolation. And, I am using XLOCK, ROWLOCK, READPAST in my select query. If process A has exclusive lock on an ID, we want process B to go to the next available ID. I had to open transaction in READ COMMITTED isolation because XLOCK didn't make difference with READ COMMITTED SNAPSHOT. I did the following test: CONNECTION 1: SET TRANSACTION ISOLATION LEVEL READ COMMITTED BEGIN TRAN SELECT TOP 10 * FROM config_IDPool WITH (XLOCK,ROWLOCK,READPAST) WHERE ID NOT IN ( SELECT ID FROM table_IDPool ) --COMMIT TRAN This returned 10 records. CONNECTION 2: SET TRANSACTION ISOLATION LEVEL READ COMMITTED BEGIN TRAN SELECT TOP 10 * FROM config_IDPool WITH (XLOCK,ROWLOCK,READPAST) WHERE ID NOT IN ( SELECT ID FROM table_IDPool ) --COMMIT TRAN As per my understating, this is supposed to return me the next 10 records, but it is waiting for the 1st transaction to COMMIT. Is this the right way to handle this situation? Thanks, Rakesh