SQL Server 的事务和锁（举例）——在其他博客上做些笔误修改



SQL Server的事务和锁（举例）

原料：创建Test表

CREATETABLE [dbo].[Test](

   [id] [int] NULL,

   [name] [nchar](10)COLLATEChinese_PRC_CI_AS NULL,

   [info] [nchar](10)COLLATEChinese_PRC_CI_AS NULL

)ON [PRIMARY]

插入一条记录

INSERTINTO Test VALUES(2,'kk',null)

查询显示有此记录

SELECT * FROM Test  Wherename='kk'

id  name   info

1   kk      NULL

 

【测试一：丢失更新】

--事务1  

begintran 

select* from dbo.Test(nolock)where name = 'kk' 

waitfor delay'00:00:05' 

update Tset info = 'A更改'from Test T(nolock)where name = 'kk'

committran 

--事务2  

begintran 

   select *from dbo.Test(nolock)where name = 'kk' 

   waitfor delay '00:00:05' 

   update T setinfo= 'B更改'from Test T(nolock)where name = 'kk'  

committran  --事务1

begintran

  select * from dbo.Test(nolock) where name ='kk'

  waitfor delay '00:00:05'

  update T set info = 'A更改' from Test T(nolock)where name = 'kk'

committran

 

--事务2

begintran

  select * from dbo.Test(nolock) where name ='kk'

  waitfor delay '00:00:05'

  update T set info = 'B更改' from Test T(nolock)where name = 'kk'

committran

 

事务1和事务2的运行结果：

id  name   info

1   kk      NULL

 

事务结束最后结果：

SELECT* FROM Test  Wherename='kk'

id  name   info

1   kk      B更改

 

说明：【事务1】更改了数据，但是没有被读到。最终【事务2】的更改覆盖了【事务1】的更改值

 

解决：允许对事务操作加锁，即取消(nolock)，nolock级别最低。

 

当前使用：SETTRANSACTION ISOLATIONLEVEL READ UNCOMMITTED

（READ UNCOMMITTED：即未提交的数据只能读取，不能更改）

 

 

【测试二：脏读】

--事务1  

SETTRANSACTION ISOLATIONLEVEL READ UNCOMMITTED

begintran 

update Tset info = 'A更改'from Test T wherename = 'kk'

waitfor delay'00:00:05' 

rollbacktran 

--事务2  

SETTRANSACTION ISOLATIONLEVEL READ UNCOMMITTED

begintran 

   select *from dbo.Testwhere name = 'kk' 

committran  --事务1

begintran

  select * from dbo.Test(nolock) where name ='kk'

  waitfor delay '00:00:05'

  update T set info = 'A更改' from Test T(nolock)where name = 'kk'

committran

 

--事务2

begintran

  select * from dbo.Test(nolock) where name ='kk'

  waitfor delay '00:00:05'

  update T set info = 'B更改' from Test T(nolock)where name = 'kk'

committran

 

 

--事务1

SETTRANSACTION ISOLATION LEVEL READ UNCOMMITTED

begintran

  update T set info = 'A更改' from Test T where name= 'kk'

  waitfor delay '00:00:05'

rollbacktran --回滚

 

--事务2

SETTRANSACTION ISOLATION LEVEL READ UNCOMMITTED

begintran

  select * from dbo.Test where name = 'kk'

committran

 

事务2的运行结果：

id  name   info

1   kk      A更改

 

事务结束最后结果：

SELECT* FROM Test  Wherename='kk'

id  name   info

1   kk      NULL

 

说明：【事务1】更改了数据，【事务2】读取了表中更改的值，【事务1】回滚。最终的表是没有被更改的。

 

解决一：提高隔离级别

SETTRANSACTION ISOLATIONLEVELREADCOMMITTED

ALTERDATABASEDBNameSETREAD_COMMITTED_SNAPSHOTOFF（默认OFF）

此时【事务1】等待事务【事务2】完成后才读取表中的值。

（READ COMMITTED：更改的数据只能是提交后才能读取）

 

解决二：

SETTRANSACTION ISOLATION LEVEL READ COMMITTED

ALTERDATABASEDBNameSETREAD_COMMITTED_SNAPSHOTON（默认OFF）

此时【事务2】读到的是表中之前的快照值（不必等待【事务1】完成）。

此时相当于：

SETTRANSACTION ISOLATIONLEVELREADCOMMITTED

select* from dbo.Test WITH(READPAST)wherename='kk'

 

解决三：

事务2用表提示READ COMMITTED LOCK把语句级别改为READ COMMITTED

--事务2

SETTRANSACTION ISOLATIONLEVEL READ UNCOMMITTED

begintran

   select *from dbo.TestWITH(READCOMMITTEDLOCK)wherename='kk'

committran

 

如果将READ_COMMITTED_SNAPSHOT设置为OFF（默认设置），设置时关闭所有对此数据库的连接，

数据库引擎会使用共享锁防止其他事务在当前事务执行读取操作期间修改行。

共享锁还会阻止语句在其他事务完成之前读取由这些事务修改的行。

 

 

 

【测试三：不可重读】

 

--事务1  

SETTRANSACTION ISOLATIONLEVEL READ COMMITTED 

   begin tran 

   select *from dbo.Testwhere name = 'kk' 

   waitfor delay '00:00:05' 

   select *from dbo.Testwhere name = 'kk' 

committran  

--事务2  

SETTRANSACTION ISOLATIONLEVEL READ COMMITTED 

   begin tran 

   update T setinfo= 'B更改'from Test T wherename = 'kk' 

committran 

--事务1

SETTRANSACTION ISOLATION LEVEL READ COMMITTED

begintran

  select * from dbo.Test where name = 'kk'

  waitfor delay '00:00:05'

  select * from dbo.Test where name = 'kk'

committran

 

--事务2

SETTRANSACTION ISOLATION LEVEL READ COMMITTED

begintran

  update T set info = 'B更改' from Test T where name= 'kk'

committran

 

事务2的运行结果：

id  name   info

1   kk      NULL

 

id  name   info

1   kk      B更改

 

事务结束最后结果：

SELECT* FROM Test  Wherename='kk'

id  name   info

1   kk      B更改

 

说明：【事务1】读取了数据，【事务2】更改数据，【事务1】再读取数据。【事务1】两次读取都不一样！

 

解决一：提高隔离级别

SETTRANSACTION ISOLATIONLEVEL REPEATABLEREAD

（REPEATABLE READ：允许重复读取数据，但读取的都是第一次读取的副本）

 

解决二：更改为快照级别

ALTERDATABASE DBName SETALLOW_SNAPSHOT_ISOLATIONON

SETTRANSACTION ISOLATION LEVEL SNAPSHOT

 

此时【事务1】的运行结果2次都一样，读到的都是未更改前的值：

id  name   info

1   kk      NULL

 

 

 

【测试四：幻读】

--事务1   

SETTRANSACTION ISOLATIONLEVEL REPEATABLEREAD

begintran 

   select *from dbo.Testwhere name = 'kk' 

   waitfor delay '00:00:05' 

   select *from dbo.Testwhere name = 'kk' 

committran 

    

--事务2  

SETTRANSACTION ISOLATIONLEVELREPEATABLE READ

begintran 

   INSERT INTOTestVALUES(2,'kk',null)  

committran  --事务1

SETTRANSACTION ISOLATION LEVEL SERIALIZABLE

begintran

  select * from dbo.Test where name = 'kk'

  waitfor delay '00:00:05'

  select * from dbo.Test where name = 'kk'

committran

 

--事务2

SETTRANSACTION ISOLATION LEVEL SERIALIZABLE

begintran

  INSERT INTO Test VALUES(2,'kk',null)

committran

 

事务2的运行结果：

id  name   info

1   kk      NULL

 

id  name   info

1   kk      NULL

2   kk      NULL

 

事务结束最后结果：

SELECT* FROM Test  Wherename='kk'

id  name   info

1   kk      NULL

2   kk      NULL

 

说明：【事务1】读取了数据，【事务2】插入了数据，【事务1】再读取数据。【事务1】两次读取都不一样！幻觉？

 

解决：提高隔离级别

SETTRANSACTION ISOLATIONLEVEL SERIALIZABLE 

此时【事务2】等待【事务1】完成。

事务1的运行结果2次都一样：

id  name   info

1   kk      NULL

 

范围锁：范围锁处于与事务中执行的每个语句的搜索条件相匹配的键值范围之内。这样可以阻止其他事务更新或插入任何行，从而限定当前事务所执行的任何语句。这意味着如果再次执行事务中的任何语句，则这些语句便会读取同一组行。在事务完成之前将一直保持范围锁。这是限制最多的隔离级别，因为它锁定了键的整个范围，并在事务完成之前一直保持范围锁。因为并发级别较低，所以应只在必要时才使用该选项。该选项的作用与在事务内所有SELECT语句中的所有表上设置HOLDLOCK相同。

 

 

如果在批处理中设置REPEATABLE READ，并且该批处理调用一个将隔离级别设置为SERIALIZABLE的存储过程，则当该存储过程将控制返回给该批处理时，隔离级别就会恢复为REPEATABLEREAD。

 

并发事务引起的问题

     问题          

                   描述                      

             结果             

                         解决                     

丢失更新

A读—B读—A改—B改

A更改丢失

READ UNCOMMITTED

脏读

A改—B读—A回滚

B读无效值

READ COMMITTED

不可重读

A读—B改—A读

A读不一致

REPEATABLE READ

不可重读

A读—B改—A读

A读不一致

SNAPSHOT

幻读

A读—B增删—A读

A读或多或少

SERIALIZABLE