目录
一、锁类型
二、sqlserver锁种类
三、概念
四、死锁的处理
五、锁超时
六、常用
一、锁类型
(1)锁的类型
--SQL Server支持三种基本的封锁类型:共享( S)锁,排它(X)锁和更新(U)锁\
(2)共享(S)锁:用于读操作
--允许多个事务封锁一个共享数据。
--任何事务都不能修改加S锁的数据。
--加S锁的数据被读取完毕,S锁立即被释放。
(3)独占(X)锁:用于写操作
--仅允许一个事务封锁此共享数据
--其它任何事务必须等到X锁被释放才能对该数据进行访问
--X锁一直到事务结束才能被释放
(4)更新(U)锁。
--预定要对此页施加X锁
--它允许其它事务读,但不允许再施加U
(5)总结
--读操作(SELECT)获得共享锁
--写操作( INSERT、DELETE)获得独占锁
--而更新操作可分解为一个有更新意图的读和一个写操作,故先获得更新锁,然后再升级为独占锁。
二、sqlserver锁种类
NOLOCK(无锁):不要发出共享锁,并且不要提供排它锁。当此选项生效时,可能会读取未提交的事务或一组在读取中间回滚的页面。有可能发生脏读。仅应用于SELECT语句。
ROWLOCK(行锁):使用行级锁,而不使用粒度更粗的页级锁和表级锁。
TABLOCK(表锁):使用表锁代替粒度更细的行级锁或页级锁。在语句结束前,SQLServer一直持有该锁。但如果同时指定HOLDLOCK,那么在事务结束之前,锁将被一直持有。
TABLOCKX(排他表锁):使用表的排它锁。该锁可以防止其它事务"读取"或更新表,并在语句或事务结束前一直持有。
PAGLOCK(页锁):此选项为默认选项,当被选中时,SQL Server使用共享页锁。
UPDLOCK(更新锁):读取表时使用更新锁,而不使用共享锁,并将锁一直保留到语句或事务的结束。允许您读取数据(不阻塞其它事务)并在以后更新数据,同时确保自从上次读取数据后数据没有被更改。
HOLDLOCK(保持锁): 会将此共享锁保持至整个事务结束,而不会在途中释放。 (HOLDLOCK等同于SERIALIZABLE)
XLOCK:使用排它锁并一直保持到由语句处理的所有数据上的事务结束时。可以使用PAGLOCK或TABLOCK指定该锁,这种情况下排它锁适用于适当级别的粒度
READUNCOMMITTED:等同NOLOCK。
READCOMMITTED:用与运行在提交读隔离级别的事务相同的锁语义执行扫描。默认情况下,SQLServer2000在此隔离级别上操作。
READPAST: 跳过锁定行。此选项导致事务跳过由其它事务锁定的行(这些行平常会显示在结果集内),而不是阻塞该事务,使其等待其它事务释放在这些行上的锁。READPAST锁提示仅适用于运行在提交读隔离级别的事务,并且只在行级锁之后读取。仅适用于SELECT语句。
REPEATABLEREAD:用与运行在可重复读隔离级别的事务相同的锁语义执行扫描。
三、概念
(1)不仅仅使用行级锁的数据库使用一种称为混和锁(lock escalation)的技术来获取较高的性能
(2)SQL Server开始是用行级锁的,但是经常会扩大为页面锁和表锁,最终造成死锁。
(3)死锁危害:如果两个用户以相反的顺序修改位于不同表的记录,而这两条记录虽然逻辑上不相关,
但是物理上是相邻的,操作就会先引发行锁,然后升级为页面锁。这样, 两个用户都需要对方锁定的东西,就造成了死锁。
四、死锁的处理
(1)数据库会经常去检测是否有死锁存在,如果有,就把其中的一个事务撤销,好让另一个事务能顺利完成
(2)一般来说,都是撤销那个修改数据量少的事务,回滚的开销就比较少。
(3)使用行级锁的数据库很少会有这个问题,因为两个用户同时修改同一条记录的可能性极小,而且由于极其偶然的修改数据的顺序而造成的锁也少
五、锁超时
(1)数据库使用"锁超时"来避免让用户等待时间过长。查询超时的引入也是为了同样目的
(2)服务器负载较高,混合锁的SQL Server锁机制表现不会很好, 锁争用(Lock Contention)。锁争用造成死锁和锁等待
(3)一改多查的情况随时会产生锁,,用户争先恐后的获取锁来确认自己操作的正确性,死锁频繁
六、常用
(1)容许一定程度的数据一致性,尽量使用行锁或者无锁(金融行业需要注意)
SELECT COUNT(UserID) FROM Users WITH (NOLOCK) WHERE Username LIKE 'foobar'
UPDATE Users WITH (ROWLOCK) SET Username = 'fred' WHERE Username = 'foobar'
(2)ROWLOCK只使用行级锁(使用在select update delete)
--update中有指定的主键总会引起行级锁
--update批处理几个有指定的主键的语句,数据正好在同一个页面(page),当页面锁引发后,事情变得糟糕了
--update或者delete时没指定主键,数据库当然认为很多数据会收到影响,直接引发页面锁,事情同样变得糟糕
--总结:使用行级锁可以避免,但你错误地使用在过多行上,DB并不会聪明到自动将行级锁升级到页面锁,服务器会因为
锁的开销消耗大量的CPU和内存,直至无法响应,恰恰应该庆幸锁超时和死锁的问题减少了
--扩展
企业管理器中"管理/当前活动"(Management/Current Activity)这一项。该项会花较长的时间来载入锁的信息。
当你使用行级锁后,你如果在"锁/处理"(Locks/Processes)下看到几百个锁
(3)UPDLOCK
--读取表时使用更新锁,锁一直保留到语句或者事务结束! (保证上次读取数据后数据没有被更改)
--实例
更新时其它的线程或事务在这些语句执行完成前是不能更改ID是1,2,3的记录的
但是可以读,1,2,3的只能读,要是修改的话只能等这些语句完成后才能操作.从而保证的数据的修改正确.
BEGIN TRANSACTION --开始一个事务
SELECT Qty FROM myTable WITH (UPDLOCK) WHERE Id in (1,2,3)
UPDATE myTable SET Qty = Qty - A.Qty FROM myTable AS A INNER JOIN @_Table AS B ON A.ID = B.ID
COMMIT TRANSACTION --提交事务
(4)性能
--使用小事务并且以批处理的形式执行(不错,实际经验就是如此),
--使用低级别的隔离措施 (也没错,NOLOCK就是一个极端的例子)
--建议你有限的连接,从而让处理器进行合作