SQL Server2000中的事务和锁

1.       事务的回顾

事务的ACID属性

           原子性(Atomicity )、一致性( Consistency )、隔离性( Isolation)和持久性(Durabilily)。

显示事务

                  Begin tran

                  Commit/rollback

隐式事务

                  Set implicit_transactions on

事务中的锁一直保持，直到事务结束     。共享锁是个例外。

 

2.       锁是什么

确保数据在并发系统中一致

锁和并发性是相逆的

由系统自动生成

用户只能通过设定事务隔离级别来间接的影响系统生成的锁

 

3.       锁的类型：从锁的行为来分

共享锁

排他锁

更新锁

意向锁

 

4.       锁的类型：从锁的资源来分

DB级锁

表级锁

页面锁 锁定整个数据页8KB

行级锁

键锁 有聚集索引时

 

5.       共享锁

通常由select语句引发

多个共享锁之间相互兼容

在默认情况下，共享锁在语句结束之后就会释放，即便在事务中。可以通过设定不同的事务隔离级别来改变这种情况。

 

6.       排他锁

通常由insert, update, delete语句引发

多个排他锁之间不兼容，和共享锁也不兼容

默认情况下，排他锁将一直保持到事务结束才会被释放。一次只有一个进程可以保持对特定数据源的排他锁。如果一个进程已经请求了排他锁，并且锁定了数据资源，那么其他进程将不能获得任何类型的锁，也就是说其他进程不能访问被锁定的数据资源。如果其他进程正准备访问这些数据，那么将处于锁等待状态，等待数据资源上的锁被释放。

可以通过修改事务隔离级别来查看其他进程正在修改的数据。

有聚集索引的情况下，键锁会替代行锁。

 

7.       更新锁

不是一种独立类型的锁，介于共享锁和排他锁之间

在执行修改操作之前，SQL Server搜索表以查找将要被修改的资源时，获得更新锁

不仅用于更新操作，还用于insert和delete

 

8.       意向锁：IS、IX、SIX

不是一种独立类型的锁，是前面三种锁的另一种状态

意向共享、意向排他、意向更新

例：当修改或更新表中的一行或几行时，在表级别上是意向排他锁，在行级别上是排他锁

通常情况下，意向锁都是在表级或页面级的。

聚集索引可以提高并发性

 

9.       锁的提升

把行锁、键锁和页面锁提升为表锁

SQL Server自动的升级

 

10.   锁的兼容性

现有锁类型
请求的锁类型	IS	S	U	IX	X	SIX
IS	是	是	是	是	否	是
S	是	是	是	否	否	否
U	是	是	否	否	否	否
IX	是	否	否	是	否	否
X	否	否	否	否	否	否
SIX	是	否	否	否	否	否

共享锁和排他锁是不兼容的，但如果在同一个事物中，则兼容。

 

11.   事务隔离级别

set transaction isolation level

read committed(默认) 在读取数据时，控制共享锁以避免脏读。目的：当某一个事务中有数据更改的时候，别的事务不能访问正在更改的数据。只能读已经提交的数据。

read uncommitted 也称之为脏读。不发出共享锁。也就是说，当一个事务在修改某一行时，另一个事务要读取正在修改的行时，不发出共享锁。这样就可以得到未提交的数据。

repeatable read 锁定查询中的所有数据（共享锁），以防止其他用户更新数据。将select语句发出的共享锁保持到事务结束为止。这样其他事务就不能更改或删除该记录，以避免不可重复读的现象。

Serializable 避免了幻象的发生。

 

示例1：脏读

在read uncommitted隔离级别下，事务1正在修改某行记录，事务2可以读取该行数据，读取到的数据是事务1更改后的数据。

事务1：

begin tran

update HumanResources.Employee set title='test' where employeeid=1

事务2：

set transaction isolation level read uncommitted

select * from HumanResources.Employee where employeeid=1

 

如果事务1回滚，则事务2读到的是脏数据。

不建议使用该隔离级别，但在某些情况下可以使用，如：只是看一些趋势。

 

示例2：避免脏读

默认隔离级别下，事务1正在更新某行数据，事务2不能访问该行数据。

事务1：

begin tran

update HumanResources.Employee set title='test' where employeeid=1

事务2：

select * from HumanResources.Employee where employeeid=1

先执行事务1中的语句，再执行事务2中语句，事务2处于等待的状态。

 

示例3：不可重复读

默认隔离级别下，事务1第一次查询所有记录，employeeid为1的行其title列的值为” Production Technician - WC60”，事务2中更改employeeid为1的行的title为”test”并提交，当事务1中再次读取所有记录时，与第一次读取的值不一致。此现象即为不可重复读。为了避免这种情况，可以使用可重复读事务隔离级别。

事务1：

begin tran

--first read

select * from HumanResources.Employee

--second read

select * from HumanResources.Employee

事务2：

update HumanResources.Employee set title='test' where employeeid=1

 

示例4：可重复读

设置事务的隔离级别为repeatable read，此时事务中的共享锁会保持到事务结束为止。即当事务1查询某些数据时，这些数据在事务2中不能被修改或删除。以保证可重复读。

事务1：

set transaction isolation level repeatable read

begin tran

--first read

select * from HumanResources.Employee

--second read

select * from HumanResources.Employee

事务2：

update HumanResources.Employee set title='nanjing' where employeeid=1

当事务1中执行”first read”后，执行事务2中的代码，事务2处于等待状态。事务1执行”second read”，读取到的数据和第一次读取结果一致。待事务1提交或回滚后，事务2才能执行。

 

示例5：幻读

在repeatable read事务隔离级别下，在事务1中第一次查询所有记录，然后在事务2中插入新行，在事务1中第二次查询所有数据，发现与第一次的结果不一致，多了一行。这种不可重复读，称之为幻读。为了避免幻读，可以使用序列化事务隔离级别。

事务1：

set transaction isolation level repeatable read

begin tran

--first read

select * from Person.AddressType

--second read

select * from Person.AddressType

事务2：

insert into Person.AddressType(Name, rowguid, ModifiedDate)

values('TestAddressType',newid(),getdate())

 

示例6：避免幻读

使用序列化事务隔离级别，防止其他事务更新数据集或将新行插入数据集。使用sp_lock会发现，行级或键级上存在共享范围锁。

事务1使用序列化事务隔离级别，在事务1中第一次查询所有记录，在事务2中插入记录，事务2将处于等待状态（即不能插入记录），在事务1中第二次查询所有记录，与第一次查询的结果一致。即避免了幻读。此隔离级别的并发性非常低，类似于单用户操作，不建议使用。

事务1：

set transaction isolation level serializable

begin tran

--first read

select * from Person.AddressType

--second read

select * from Person.AddressType

事务2：

insert into Person.AddressType(Name, rowguid, ModifiedDate)

values('TestAddressType2',newid(),getdate())

 

12.   表的锁提示（锁暗示）

（1）       From字句后可跟表的锁提示，要求系统发出指定的锁

（2）       粒度提示：PAGLOCK、NOLOCK、ROWLOCK、TABLOCK或TABLOCKX。

（3）       隔离级别提示：HOLDLOCK、NOLOCK、READCOMMITTED、REPEATABLEREAD或SERIALZABLE

语法：SELECT COUNT(*) FROM Person.AddressType WITH(TABLOCK,HOLDLOCK)

示例：

在事务1中使用默认事务隔离级别，更新employeeid为1的记录title列为”hello”，在事务1中可以查询到更改。但在事务2中使用” select * from HumanResources.Employee

”语句，事务2将处于等待状态。但若使用” select * from HumanResources.Employee with(nolock)”语句，则可以查询到结果，且结果是title已经更改为”hello”的。和未提交读事务隔离级别是一样的效果。

事务1：

set transaction isolation level read committed

begin tran

update HumanResources.Employee set title='hello' where employeeid=1

 

select * from HumanResources.Employee

事务2：

--无法读取

select * from HumanResources.Employee

--可以读取

select * from HumanResources.Employee with(nolock)

 

13.   死锁

当两个事务，分别锁定一个资源，又相互请求对方的资源时，会发生死锁。

死锁不是锁等待。锁等待指事务1锁定了某个资源，事务2等待事务1释放那个资源。

SQL Server会自动解决死锁，选择一个代价较小的事务作为牺牲品。

避免死锁的方法：以相同的次序访问对象。

         死锁是无法避免的，但应该尽量避免死锁的发生。

         主要会发生循环死锁和转换死锁。

示例：循环死锁

事务1：

--第一步

begin tran

update customers set cust_name='c1'

--第三步

update employees set salary=10000

事务2：

--第二步

begin tran

update employees set salary=20000

--第四步

update customers set cust_name='c2'

--第五步

Commit

 

提交未作为牺牲品的事务后，在事务3中查询。

select * from customers

select * from employees

 

14.   锁超时

默认情况下锁等待将一直持续

SET LOCK_TIMEOUT 1800

为0表示不等待，默认是-1（一直等待）

超出时限则回滚事务

示例：锁超时

事务1中更新表A中的一行记录，事务2查询表A，因为事务2无法获取共享锁，所以会一直等待，直到达到数据库设置的“最大查询时间”后终止。若使用锁超时设置，则可以在达到锁超时限制后终止事务。

事务1：

begin tran

update HumanResources.Employee set title='t1' where employeeid=1

事务2：

--直到达到数据库设置的“最大查询时间”后终止

select * from HumanResources.Employee

 

--等待秒后终止

set lock_timeout 3000

select * from HumanResources.Employee

 

15.   最佳实践

让事务尽可能的小，避免保持过多的锁过长的时间

仅在必要的时候才使用事务隔离级别和锁暗示

给应用程序设置锁超时

应用程序设计时通过中间件来提高并发和事务控制

 

16.   friendly_sp_lock.sql

if object_id('tempdb..#temp') is not null

begin

         truncate table #temp

         drop table #temp

end

go

 

create table #temp(

         spid int,

         dbid int,

         objid bigint,

         indid int,

         type nvarchar(100),

         Resource nvarchar(100),

         mode nvarchar(100),

         Status nvarchar(100))

go

 

insert into #temp exec sp_lock

 

select

                            lock.spid

                            ,db.name as DBName

                            ,object_name(objid) as ObjectName

                            ,lock.type

                            ,lock.mode

                           

                            ,lock.indid

                            ,lock.resource

                            ,lock.status

                           

from                   #temp lock

                            inner join master..sysdatabases db

                            on lock.dbid=db.dbid

                  

where                lock.dbid=10

order by   type