我在开发医院HIS系统项目的时候,由于是多用户并发运行,门诊高峰时期,经常出现几十个用户同时操作同一个表的情况。
这个时候门诊收费人员经常反映系统速度很慢,情况就像是死机似的,多个用户等待同一个用户的事务提交,数据堵塞了。
除了数据堵塞,可能还有死锁等其他原因导致系统慢,这就不在本文里讨论了。
我们用sql server2000自带的pubs库来测试一下数据堵塞。
1、首先开一个查询窗口(窗口1),执行以下语句:
use pubs
update titles
set price = 3
where title_id = 'BU2075'
得出结果:
title_id price
BU2075 2.9900
2、我们再开另外一个查询窗口(窗口2),执行以下语句:
use pubs
begin tran
update titles
set price = 3
where title_id = 'BU2075'
3、然后回到窗口1,运行开始输入的语句。我们发现,窗口一直在运行,没有得到结果。
4、再开一个窗口3,执行以下语句:
select * from master..sysprocesses where blocked<>0
从得出的结果看,很明显,一个数据堵塞就这样产生了。
5、再开窗口4,再执行以下语句:
use pubs
select title_id,price
from titles
该窗口也一直在运行,再执行一下窗口3的语句,数据堵塞记录变成两条,很明显,第二个数据堵塞产生了。
6、再开窗口5,执行以下语句:
use pubs
select title_id,price
from titles
where title_id = 'BU1032'
得出结果:
title_id price
BU1032 19.9900
7、回到窗口2,执行:
rollback
执行之后,我们再看窗口1和窗口4,由一直运行的状态,变成有查询结果了。
由以上试验可以看出,当一个用户更新数据的事务没有完成之前,sql server会对数据表加行级锁,
如果另外用户想在这个时候查询加锁的数据,该用户就要一直等待,直到事务完成,才能继续执行。
除了查询加锁的数据,查询整个表也不行。但加上查询条件,查询表内不加锁的数据就可以有结果。
那如何解决好呢?
1、对业务表加索引,加快查询速度。
2、把不必要的查询移出事务外查询,尽量减少事务执行时间。
3、对数据查询功能,加上WITH (NOLOCK) 关键字。
以下简单介绍一下WITH (NOLOCK) 关键字,回到2窗口,运行:
use pubs
begin tran
update titles
set price = 3
where title_id = 'BU2075'
新增窗口6,运行:
use pubs
select title_id,price
from titles WITH (NOLOCK)
where title_id = 'BU2075'
得出结果:
title_id price
BU1032 3.0000
由此看出,加了WITH (NOLOCK) 关键字之后,不会像之前那样一直运行了,而是会得出update后的结果。
如果对整个表查询,会怎么样呢?新增窗口7,运行:
use pubs
select title_id,price
from titles WITH (NOLOCK)
也可以得出update后的结果。
由此看出,加了WITH (NOLOCK) 关键字之后,sql server会跳过加锁的数据,得到需要的结果。
但这样可能会读取到脏数据,所以使用的时候一定要谨慎。
至此,简单的数据堵塞分析完毕,希望对大家有帮助。