数据库锁（二）——应用

    上一篇博客大概介绍了数据库锁的机制、类型，是理论基础，这篇博客重点从代码的角度总结。

    先来看一些实例：

    1、独占锁（排它锁X）

    第一个连接：在一个线程中启用了一个事物。

use DRP                            ----事先建好的数据库
begin tran     
update t_One 
    set A='aa'    where B='b1'
    waitfor delay '00:00:30'    ---延时30秒后执行
commit tran

    第二个连接：新的线程中，启用另一个事物，去访问已有的事物中，独占锁占用的资源（是同一行数据，默认是RID，行锁）

use DRP 
begin tran
	select * from t_One 
	where B='b1'		--需要等待第一个连接结束后才可以进行，所以这里也被延时了30秒
				---但要限制是同一行数据，这里如果改成B='b2'，则不延时。也就是说默认的是行级锁。
						
commit tran 

    第二个连接必须等待第一个连接，执行完后，才可以读出数据。因为在第一个线程的事物中，update语句默认给所要更新的行记录，上了独占锁，在更新的事物完成之前，其他任何事物，必须等待该事物，执行完后才可进行其他操作。

    值得注意的是，如果在第二个事物中，访问的不是第一个事物要更改的行记录，是不会等待的。

    2、共享锁：

    第一个连接：

begin tran 
	select * from t_One with (holdlock)	--针对整个表上锁，其他事物可以读取，但不可以更新。
	where B='b2'
	waitfor delay '00:00:30'	        ---延时30秒后执行
commit tran

     第二个连接：

begin tran 
	select A,C from t_One	where B='b2'	
	update t_One set A='aa' where B='b2'	
commit tran

    如果将第二个连接改成下面的就更可以说明问题：

begin tran 
	select A,C from t_One	where B='b2'	
	--update t_One set A='aa' where B='b2'	
commit tran

或：

begin tran 
	--select A,C from t_One	where B='b2'	
	update t_One set A='aa' where B='b2'	
commit tran

    同时执行上面两个线程中的事物语句，第二个事物可以同时读取第一个事物使用共享锁的资源，但不可以在第一个事物释放锁之前，修改数据。

   3、死锁：

    第一个线程：

begin tran
	update t_One set A='aa' where B='b2' waitfor delay '00:00:30'
	update t_Two set D='d5' where E='e1'	
commit tran

    第二个线程：

begin tran 
	update t_Two set D='d5' where E='e1' waitfor delay '00:00:10'
	update t_One set A='aa' where B='b2'
commit tran

     运行结果：

    消息 1205，级别 13，状态 45，第 3 行
事务(进程 ID 55)与另一个进程被死锁在 锁 资源上，并且已被选作死锁牺牲品。请重新运行该事务。

    综上，对比以下的语句：

begin tran    
    update t_One set A='aaa'
    select A from t_One holdlock
 commit tran

begin tran	
	select A from t_One holdlock
	update t_One set A='aaa'
 commit tran

    显然后面这句，很容易导致死锁。修改成第一句，会降低死锁的风险，但只有一个事物可以获得独占锁，会大大降低性能。

    4、如何解决重复读？

    参考下面的一个实例：

   如果一个事务需要检索出titles表中所有书的平均价格，并保证在update被应用前，结果不会改变，优化器就会分配一个独占的表锁定。    

begin tran
    update titles set title='aaa'
       where 1=2
    if (selectavg(price)fromtitles)>$15
    begin
        /*其他的代码 */
    end
    update titles set price＝price*1.10
    where price<(select avg(price)from titles)
commit tran

    这里使用where 1=2的条件，将资源独占，保证在事物结束之前，不会有其他事物对书的价格进行更改。

    5、其他相关知识拾摘：

      1.行级锁：

SET TRANSACTION ISOLATION LEVEL READ UNCOMMITTED
SELECT * FROM t_One ROWLOCK WHERE A = 'a1'

     2.表级锁：

SELECT * FROM t_One WITH (HOLDLOCK) --其他事务可以读取表,但不能更新删除
SELECT * FROM t_Two WITH (TABLOCKX) --其他事务不能读取表,更新和删除

    

     3.查看锁的信息：

    执行 EXEC SP_LOCK   （查询分析器中按Ctrl+2）

     4.设置超时阻塞时间：   

SET LOCK_TIMEOUT 2000	--设置超时阻塞时间
@@LOCK_TIMEOUT		--返回当前会话的当前锁超时设置，单位为毫秒 


    5.避免死锁：
    所有的SP都要有错误处理（通过@error）；
    设置死锁超时参数；
    避免进程悬挂