Click here to monitor SSC
SQLServerCentral is supported by Red Gate Software Ltd.
 
Log in  ::  Register  ::  Not logged in
 
 
 
        
Home       Members    Calendar    Who's On


Add to briefcase

Isolation level serializable Expand / Collapse
Author
Message
Posted Thursday, March 4, 2010 1:47 PM
Forum Newbie

Forum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum Newbie

Group: General Forum Members
Last Login: Thursday, February 14, 2013 2:19 PM
Points: 7, Visits: 32
Can someone explain isolation level serializable?
if two processes run the same stored proc,

Set transaction isolation level serializable
begin tran
declare @currentnum int
Select @currentnum = currentnum from refnumbers where id = 1;
update refnumbers set currentnum =
@currentnum where id = 1;
commit tran

so if user1 and user2 run the select query at the same time, does it mean user2 will not be able to select anything until user1 transaction commits? keep in mind user1 has not ran the update yet, both users are running select.

Can you also explain how serializable level eliminates lost updates?
Post #877144
Posted Thursday, October 27, 2011 5:01 AM
SSC-Enthusiastic

SSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-Enthusiastic

Group: General Forum Members
Last Login: Monday, October 27, 2014 11:48 PM
Points: 180, Visits: 529
yes
Post #1196826
Posted Thursday, October 27, 2011 5:26 AM
SSCrazy

SSCrazySSCrazySSCrazySSCrazySSCrazySSCrazySSCrazySSCrazy

Group: General Forum Members
Last Login: Thursday, November 20, 2014 12:22 PM
Points: 2,125, Visits: 5,544
If both users will run the select statement at the exact same time, then you’ll have a deadlock. When you work with serializable isolation level, the shared locks that were acquired by both processes will be held until the end of the transactions. Since both processes will next try to update the table, each one of them will have to wait for the other process to release the shared lock. This will cause a deadlock and one of the transactions will be rolled back.

Adi


--------------------------------------------------------------
To know how to ask questions and increase the chances of getting asnwers:
http://www.sqlservercentral.com/articles/Best+Practices/61537/

For better answers on performance questions, click on the following...
http://www.sqlservercentral.com/articles/SQLServerCentral/66909/
Post #1196830
« Prev Topic | Next Topic »

Add to briefcase

Permissions Expand / Collapse