Transaction Isolation Levels - SQL Server Forums

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 2000 Forums

Transact-SQL (2000)

Transaction Isolation Levels

Author

Topic

axisperfect
Starting Member

21 Posts

Posted - 2004-09-09 : 00:38:42

Hi,

I've created stored procedure that makes "reservation" of serial numbers for users. For instance, if a user needs two numbers to be reserved, it'll mark two currently unreserved rows in the table of numbers as being reserved by that user.

I'm worried about a row being reserved twice by accident (the second reservation overwriting the first reservation), and believe I have to set SOME locking level to prevent this. Unfortuntely, I'm not sure which; I believe it's a toss between repeatable read and serializable. On the safe side, I'd choose serializble but I know that I'd be asking for a higher risk of deadlocking -- and with the traffic I'm looking at, it's VERY possible.

The following's my algorithm. So, can I safely use repeateable read? Could two concurrent execution of the store procedure "accidentally" read the same unreserved ids, with one overwriting the other?


- Receive userid, Qty to reserve (as parameter)
- Set Lock Level
- Begin Trans
- select top (Qty) of unreserved ids
- update those id as being reserved by userid
- Commit Trans

hgorijal
Constraint Violating Yak Guru

277 Posts

Posted - 2004-09-09 : 01:09:21

try sp_getapplock

Hemanth Gorijala
BI Architect / DBA

axisperfect
Starting Member

21 Posts

Posted - 2004-09-09 : 02:33:11

quote:
Originally posted by hgorijal

try sp_getapplock

Hemanth Gorijala
BI Architect / DBA

Hmm.. I looked up sp_getapplock and have a vague understanding of how it works now. Are you suggesting this instead of setting the transaction isolation level? Any reason why?

So, my algorithm would be:
- Receive userid, Qty to reserve (as parameter)
- Begin Trans
- try to get the app lock, if failed -- ROll back


EXEC @result = sp_getapplock @Resource = 'Reservation', 
            @LockMode = 'Exclusive'
IF @result < 0
BEGIN
      ROLLBACK TRAN

- select top (Qty) of unreserved ids
- update those id as being reserved by userid
- Commit Trans

Do I have the following assumptions corect?
- committing will automatically releast the lock
- I can give @resource any name.
- I should use 'Exclusive' (again, is this the most appropriate lockmode for my situation?)

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources