SQL Server 锁类型
在数据库中主要存在两种锁: S(共享锁)和X(排他锁)
S(共享锁):在执行查询数据时,SQL server会将行锁定,这时只能查询数据,删,改被阻塞,
X(排他锁):在插入和删除数据时,将行锁定,这时增,删,改都被阻塞
以上两种锁都会引起死锁:
死锁定义:在两个或多个任务中,如果每个任务锁定了其他任务试图锁定的资源,此时会造成这些任务永久阻塞,从而出现死锁
这里模拟一下死锁环境:
建立环境:
----死锁例子,建立表数据
create table [dbo].[[zping.com1]]](
A varchar(2)
,B varchar(2)
,C varchar(2))
--插入数据
insert into [dbo].[[zping.com1]]]
select 'a1','b1','c1'
union all select 'a2','b2','c2'
union all select 'a3','b3','c3'
--建立表数据
create table [dbo].[[zping.com2]]]
(D varchar(2)
,E varchar(2))
--插入数据
insert into [dbo].[[zping.com2]]]
select 'd1','e1'
union all select 'd2','e2'
1. 1 排他锁引起的死锁
执行语句:
begin tran
update [dbo].[[zping.com2]]]
set D='d5'
where E='e1'
waitfor delay '00:00:05'
update [dbo].[[zping.com1]]]
set A='aa'
where B='b2'
begin tran
update [dbo].[[zping.com1]]]
set A='aa'
where B='b2'
waitfor delay '00:00:05'
update [dbo].[[zping.com2]]]
set D='d5'
where E='e1'
新建两个窗口,在5秒钟内执行上面语句,不久就会出现死锁提示。(结束后记住要把事务回滚啊)
begin tran
update [dbo].[[zping.com2]]]
set D='d5'
where E='e1'
waitfor delay '00:00:05'
select * from [dbo].[[zping.com1]]]
where B='b2'
begin tran
update [dbo].[[zping.com1]]]
set A='aa'
where B='b2'
waitfor delay '00:00:05'
select * from [dbo].[[zping.com2]]]
where E='e1'
新建两个窗口,在5秒钟内执行上面语句。不久就会出现死锁提示。(结束后记住要把事务回滚啊)
知道死锁产生的原因,在生产环境产生的死锁就类似这两种情况。
后来在网上查阅了很多资料,包括sql server 2005的帮助文档。总结有以下有主要几点:
1,降低隔离级别或者使用行版本控制隔离级别
2,提高数据的访问速度
3,减少事务长度
4,将按顺序访问热点表(如将访问频繁的表放在最后访问)
遇到的困难
但在我们这次优化中,有些是不太好处理的 如:
1,减少事务长度,事务的大小不是我们来决定的,是由业务逻辑来决定的(来自tom的《Oracle 9i/10g深入内部体系机构》中)
2,按顺序访问热点表,我们发现代码中方法间互相调用很频繁,经常一个表调用多次,要修改表的访问顺序是比较困难的。
采用的方法
后来我们就使用了以下方法:
1,将数据库隔离级别改成行版本控制隔离级别。(没有了共享锁死锁)
2,重建和优化索引,优化SQL语句和采用分区视图等方法。提高访问速度。(减少了锁定时间)
3,水平拆分表(分区)并在程序读写时尽量做到分区消除,减少读写的行数,降低锁定升级的频率和时间。 (减少锁的升级)
通过4个月左右的运行,系统就发生过一次死锁,比以前大大降低。