Locking and 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 2005 Forums

Transact-SQL (2005)

Locking and isolation levels

Author

Topic

PHUser
Starting Member

11 Posts

Posted - 2010-03-03 : 12:37:34

When two transactions are running the same process concurrently i need to make sure that the other transaction waits till the first one finishes.
Following is an example,

BEGIN Tran

SELECT LastUsedIndex from SeqNumbers
DECLARE @num
SET @num = @num + 1
UPDATE SeqNumbers SET LastUsedIndex = @num

Commit Tran

when two users run the same stored procedure, the second transaction needs wait till the first transaction commits the changes. Otherwise if both transactions run the select at the same time, they both will end up with the same LastUsedIndex. How can we make sure that only one transaction runs at a time or is there another way to resolve this issue?

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2010-03-03 : 12:40:54

use a appropriate isolation level. default isolation level causes 2nd tran to wait until 1st gets over

------------------------------------------------------------------------------------------------------
SQL Server MVP
http://visakhm.blogspot.com/

PHUser
Starting Member

11 Posts

Posted - 2010-03-03 : 12:43:37

The second transaction will only wait to commit the transaction. But both transactions will be running parallely. Ideally both transactions could be running the select query at the same time.

quote:
Originally posted by visakh16

use a appropriate isolation level. default isolation level causes 2nd tran to wait until 1st gets over

------------------------------------------------------------------------------------------------------
SQL Server MVP
http://visakhm.blogspot.com/

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2010-03-03 : 12:44:52

nope. it will get only commited value from tran 1

------------------------------------------------------------------------------------------------------
SQL Server MVP
http://visakhm.blogspot.com/

PHUser
Starting Member

11 Posts

Posted - 2010-03-03 : 12:47:50

Nope you are wrong. the select query in the second transaction will not wait for the first transaction to commit if the first transaction is alos running the select query at the same time. It will only wait if the first one is running the insert query. you can verify this with profiler.

quote:
Originally posted by visakh16

nope. it will get only commited value from tran 1

------------------------------------------------------------------------------------------------------
SQL Server MVP
http://visakhm.blogspot.com/

Sachin.Nand

2937 Posts

Posted - 2010-03-03 : 12:50:55

What isolation level are you using?

PBUH

PHUser
Starting Member

11 Posts

Posted - 2010-03-03 : 12:57:24

It's the default.

quote:
Originally posted by Idera

What isolation level are you using?

PBUH

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources