关于sql 锁和并发的一些记录

故事来源于《sql server2012 深入解析和性能分析》

1.丢失更新

/* session 1 */
use AdventureWorks2012;
declare @safetyStockLevel int =0,
        @Uplift           int =5;
begin tran;
select @safetyStockLevel =SafetyStockLevel
from Production.Product
where ProductID=1;
set @safetyStockLevel=@safetyStockLevel+@Uplift;
waitfor delay '00:00:05.000';
Update Production.Product
set SafetyStockLevel=@safetyStockLevel
where ProductID=1;
select SafetyStockLevel From Production.Product where ProductID=1;
commit tran;

/* session 2*/
use AdventureWorks2012;
declare @SafetyStockLevel int =0,@Uplift int =100;
begin tran;
select @SafetyStockLevel=SafetyStockLevel
from Production.Product where ProductID=1;

set @SafetyStockLevel=@SafetyStockLevel +@Uplift;
update Production.Product
set SafetyStockLevel=@SafetyStockLevel
where ProductID=1;
select SafetyStockLevel from Production.Product where ProductID=1;
commit tran;

SafetyStockLevel的初始值是1000，两个sql语句执行完之后，最终SafetyStockLevel的值是多少？
答案如下：

答案：1005

View Code

2.赃读

/* Session 1 */
use AdventureWorks2012;
begin tran;
update Person.Person
set FirstName='James'
where LastName='Jones';
waitfor delay '00:00:05.000';
RollBack tran;
select * from Person.Person
where LastName='Jones';

/* Session 2 */
use AdventureWorks2012;
set tran isolation level read uncommitted;
select FirstName,LastName from Person.Person
where LastName='Jones';

3.不可重复读

/* Session 1 */
use AdventureWorks2012;
set tran isolation level 
--read committed;
repeatable read;
begin tran;
select Top 5 FirstName,MiddleName,LastName,Suffix
From Person.Person
order By LastName;
waitfor delay '00:00:05.000';
select Top 5 FirstName,MiddleName,LastName,Suffix
From Person.Person
order By LastName;
commit tran;

/* Session 2 */
use AdventureWorks2012;
begin tran;
update Person.Person
set Suffix ='Junior'
where LastName='Abbas'
and FirstName='Syed';
commit tran;
/*
update Person.Person
set Suffix =NULL 
Where LastName='Abbas'
and FirstName='Syed';
*/

4.幻影读

/* Session 1 */
use AdventureWorks2012;
set tran isolation level
--read committed;
serializable
begin tran;
select top 5 
FirstName,MiddleName,LastName,Suffix
FROM Person.Person
order by LastName;
waitfor delay '00:00:05.000';
select top 5 
FirstName,MiddleName,LastName,Suffix
FROM Person.Person
order by LastName;
commit tran;

/* session 2 */
use AdventureWorks2012;
begin tran;
insert into Person.BusinessEntity(rowguid,ModifiedDate)
values (NEWID(),CURRENT_TIMESTAMP);
declare @Scope_indentity int;
select @Scope_indentity =SCOPE_IDENTITY();
insert into Person.Person (BusinessEntityID,PersonType,NameStyle,Title,FirstName,MiddleName,LastName,Suffix,EmailPromotion,AdditionalContactInfo,Demographics,rowguid,ModifiedDate)
values(@Scope_indentity,'EM','0','Mr.','James','Anthony','A',null,0,null,null,NEWID(),CURRENT_TIMESTAMP);
exec sp_executesql
N'print ''delete from Person.Person where BusinessEntityID='' +CAST(@Scope_indentity as varchar(8));
print ''delete from Person.BusinessEntity where BusinessEntityID=''+CAST(@Scope_indentity as varchar(8));'
,N'@Scope_indentity int',@Scope_indentity=@Scope_indentity

Select @Scope_indentity as BusinessEntityID
commit tran;

5.重复读

/* session 1 part 1 */
use AdventureWorks2012;
set tran isolation level
read committed;
begin tran;
update Person.Person
set LastName='Raheem_double_read_block'
where LastName='Raheem'
and FirstName='Tommy';

/* session 1 part 2 */
update Person.Person
set LastName='Raheem_double_read_block'
where LastName='Raheem'
and FirstName='Bethany';
commit tran;

/* Session 2 */
use AdventureWorks2012;
set tran isolation level read committed;
select FirstName,LastName from Person.Person where LastName like 'Raheem%';