查询未提交事务个数
select @@TRANCOUNT
前受MSSQL系统管理指南的影响,以为锁表就是status列是wait状态的,
其实如果是未提交的事务,那该进程过一段时间就不会是wait了,
不过即使不是wait,用sp_lock还是可以看到一些信息,
找到未提交的事务的进程SPID,执行下面语句
dbcc inputbuffer(SPID),可以跟踪该进程正在执行的SQL语句,
一般嵌套事务使用不好最容易导致未提交的事务,
还要结合sp_who2、master..sysprocesses 等来判断,
综合判断才能知道问题出在哪里,
我以前就碰到过几次这样的问题,系统运行变的很慢很慢
执行 sp_lock,并没有status = wait 的列,
不过有几个活动的spid,再执行sp_who、sp_who2、查阅sysprocesses,dbcc inputbuffer
基本能确定是执行什么语句导致锁住了数据,
并查看出问题的客户机(sysprocesses里有),
然后再去问那台机的使用者,最近执行了什么操作,他回答操作了XXX,
然后自己建测试库,执行同样的操作,跟踪,才找到问题的症结,
很郁闷的,
有A和B两个存储过程,B存储过程可以单独调用,B有事务,
A需要调用B,A也有事物,如:
Procedure A
begin tran ta
exec b
commit tran ta
end
procedure B
begin tran tb
delete stb
if(@@rowcount=0) begin
rollback tran tb
return
end
commit tran tb
end
单独调用B时,正常,
A调用B时,如果执行 rollback tran tb 语句时就出错,
提示“无法回滚 tb。没有找到任何该名称的事务或保存点”
嵌套事务里,回滚存在问题,
if(@@trancount = 1 ) begin --为1表示是最外层事务,大于1时表示是嵌套事务,
rollback tran tb
end else begin
commit tran tb --这里即使提、提交,外层事务回滚一样会把提交的数据给回滚的
end
1.1 行级锁
行级锁是针对行来锁定的,比如在事务里,进程A执行了一条update语句:
update student set name='xx' where id=13
则行级锁会锁住student表里id=13的记录,不让别的进程对它操作,
只有等事务完成后才解除锁,举个例子,以 SQL SERVER为例,
同时打开两个查询分析器,在第一个查询分析器里写:
use northwind
select * from suppliers
begin transaction update suppliers set CompanyName='xx' where SupplierID=3 waitfor delay '00:00:20'
commit transaction
在第二个查询分析器里写:
select * from suppliers
然后先运行第一个查询分析器里的代码,再运行第二个查询分析器里的
代码,可以看到第一个查询分析器一直运行,运行了大概20秒后执行
完毕,第二个查询分析器也一样,运行了大概20秒才停止,
这说明执行 select * from suppliers 时在等待,如果不运行第一个
查询分析器里的代码,直接运行第二个查询分析器里的代码,那几乎不
用等待就可以看到结果了;
修改第二个查询分析器的代码为:
select * from suppliers where SupplierID<>3
然后先运行第1个查询分析器的代码,再运行第二个查询分析器的代码,
可以看到第二个查询分析器一运行马上就出结果了,没有等待,
再修改代码为:
select * from suppliers where SupplierID=3
重复前面的操作,可以看到需要等待,等待约20秒后才看到结果
这很明显的告诉我们,行级锁会锁住修改的行,让别的进程无法操作
那些行,只有事务完成后别的进程才可以操作,而没有修改的行,别的
进程就可以任意操作,不会有限制
1. 事务
1.2 页级锁(1)
先理解页这个概念,在SQL SERVER里建表时,如果字段大小比较大时,
往往会有提示: 表中允许的最大行大小 8060
比如在SQL SERVER2000 里新建一个表:
create table Test(Fld1 char(5000),Fld2 char(5000))
会提示建立失败,原因如:
“创建表 'Test' 失败,因为行大小将为 10021(包括内部开销),而该值超过了表中允许的最大行大小 8060。”
为什么它会限制行大小为8060呢?因为在 SQLSERVER2000里,一页的
大小为8K,这8K里包括96字节的页头、36字节的其它信息、8060字节的
数据区,而数据就存储在8060字节的数据区里,
一页能存储多少行呢?这要看行的大小了,比如如果行大小为2000,
则一页能存储4行,注意行大小不包括文本和图象字段,
比如数据库northwind的customers表的行大小为 298,
则一页可以存储 27 行
看看行大小计算的问题:
use northwind
alter table customers add xx char(8000)
运行结果有警告:
-----------------------------------------
警告: 已创建表 'customers',但其最大行大小(8578)超过了每行的最大字节数(8060)。如果结果行长度超过 8060 字节,则此表中行的
INSERT 或 UPDATE 将失败
-----------------------------------------
它提示行大小为8578,则修改前的customers的行大小为578,
可为什么我将各个字段的大小加起来才268呢,
这有两个原因,一方面,数据库用两个字节存储一个nvarchar类型的字符
nchar也一样,而customers的字段类型为nchar和nvarchar,所以实际大小为 268*2=536 ,那还有42呢?42是表的其它开销。
从行与页的关系可以看出,行的大小越小,则一页能存储的行数越多,
数据库查询时,从一页读到另一页,比只读一页的记录要慢得多,
所以要减少跨页读取的次数,
比较下面的两个语句:
create table x1(a char(5000),b char(5000))
create table x2(a varchar(5000),b char(5000))
运行结果为:
------------------------------------
服务器: 消息 1701,级别 16,状态 2,行 2
创建表 'x1' 失败,因为行大小将为 10021(包括内部开销),而该值超过了表中允许的最大行大小 8060。
警告: 已创建表 'x2',但其最大行大小(10023)超过了每行的最大字节数(8060)。如果结果行长度超过 8060 字节,则此表中行的 INSERT 或
UPDATE 将失败。
------------------------------------
x1创建失败,x2创建成功,但有警告,为什么呢?
这要比较char和varchar的区别了,当创建x1时,最大行大小10023就是
实际的行大小,因为char是定长的,大小总是10023,而x2不同,
varchar是变长的,虽然最大行大小是10023,而实际行大小却不一定的,
实际行大小随字段a的值的大小的变化而变化,
所以,每页能存储的行数,如果是定长的,那在建表时就可以确定了,
如果是变长的,那要根据表中的数据来确定,当然,SQL SERVER存储记录
时,对于页的选择还会考虑一些问题,也并不完全是这样 看看Northwind数据库的Customers表吧,
Customers的主键字段为CustomerID,主键是聚集索引的,
主键的顺序代表了行的实际存储顺序,
比如你往Customers里插入一条记录:
insert into Customers(CustomerId,CompanyName) values('cvcvc','ff')
然后用select * from customers查看数据,
可以看到新插入的记录自动排在了CustomerId等于CONSH的后面,
看起来就和 select * from customers order by customerId
查出来的数据一样,聚集索引就是这样,记录的物理存储顺序与
聚集索引的顺序是一样的,
1.2 页级锁(2)
看看Customers表,打开三个查询分析器,在第一个表写:
begin transaction
update customers with(PagLock) set Address=Address
where customerId='ALFKI'
waitfor delay '00:00:30'
commit transaction
在第二个查询分析器里写:
select * from customers where customerId='GREAL'
在第三个查询分析器里写:
select * from customers where customerId='GROSR'
先运行第一个查询分析器,然后运行第二个,再运行第三个,
可以看到,第一和第二个查询分析器等待执行了20秒,
而第三个查询分析器没有等待立即就显示运行结果了,
我更新的是'ALFKI',因为是页锁,所以它锁住了一页的数据,
从'ALFKI'到'GREAL'的行都锁住了,这也说明,
'ALFKI'到'GREAL'之间的共34行都是属于同一页的,
你可以将第一个查询分析器的'ALFKI'换成'DRACD',可以看到运行
结果是一样的,如果换成'HANAR',那结果就变了,
变成第一个和第三个查询分析器在等待,而第二个查询分析器不用等待,
因为'HANAR'和'GROSR'属于同一页,而'GREAL'在其它的页,
页锁概念是比较简单的,但页的概念却比较复杂,
页是在SQLSERVER的内部管理的,用户看不到,页比较抽象,
对于变长的数据类型,页的分配是随数据的变化而变化的,
请参考数据库相关的资料。
1.3 表锁
在第一个查询分析器写:
begin transaction tran1
update customers with(TabLock) set City=City
where CustomerId='ALFKI'
waitfor delay '00:00:20'
commit transaction tran1
在第二个查询分析器写:
select * from customers where customerId='WOLZA'
先运行第一个查询分析器,再运行第二个,两个查询分析器都在等待.
注意customerId='WOLZA'是表的最后一条记录
1.4 阻塞
前面的例子里一个事务未提交,导致别的事务必须等待,这就是阻塞,
查看阻塞可以用sp_lock,打开三个查询分析器,
第一个写:
begin transaction tran1
update products set productName=productName+'A'
where ProductId=1
waitfor delay '00:00:30'
commit transaction tran1
第二个写:
select * from products
第三个写:
sp_lock
依次运行第一个、第二个、第三个,
然后查看第三个分析器,看看Status列,
看是否有Status='Wait'的行,比如我这里查看有这么一行:
53 6 117575457 1 KEY (010086470766) S WAIT
其中ObjId=117575457
然后运行:
use northwind
select object_name(117575457)
可以看到对应的表为 Products
1.5 死锁
同时打开两个查询分析器,
第一个写:
begin transaction tran2
update products set productName=productName+'A'
where ProductId=2
waitfor delay '00:00:10'
update products set productName=productName+'A'
where ProductId=1
commit transaction tran2
第二个写:
begin transaction tran1
update products set productName=productName+'A'
where ProductId=1
waitfor delay '00:00:10'
update products set productName=productName+'A'
where ProductId=2
commit transaction tran1
先运行第一个,再运行第二个
然后等待它们执行,等待大概十多秒,
检查运行结果,可以看到其中一个出错,错误提示如:
服务器: 消息 1205,级别 13,状态 50,行 1
在查询分析器里按F1打开帮助,在帮助里选择索引选项卡,
输入 1205 ,你仔细查看帮助文档是如何描述 1205 错误的,
为什么会死锁呢?看看执行过程,为了简单,我将productId简写为id
先是分析器1更新id=2的记录,并锁住id=2的记录,那别的进程都无法
操作id=2的记录,
然后分析器2更新id=1的记录,并锁住id=1的记录,同样别的进程无法
操作id=1的记录,
然后分析器1更新id=1的记录,因为id=1的记录被分析器2锁住了,
所以必须等待,分析器1被阻塞
同样分析器2更新id=2的记录,因为id=2的记录被分析器1锁住了,
所以也要等待,分析器2被阻塞
两个分析器都要等待对方,所以就出现死锁,哪个都不能执行,
当然,SQLSERVER2000为了解决死锁问题,它会干掉其中一个进程
来结束死锁。
1.6 占用读
占用读指可以读别的进程未提交的数据,
打开两个查询分析器,第一个写:
begin transaction tran1
update products set productName=productName+'C'
where ProductId=1
waitfor delay '00:00:15'
commit transaction tran1
第二个写:
set transaction isolation level read uncommitted
select * from products where ProductId=1
依次运行第一个和第二个,
可以看到第一个在等待,而第二个不用等待,
因为我在第二个里设置了隔离级别为read uncommitted,
就是允许读别的事务未提交的数据,
你看看第二个的运行结果,找到products列,看到products列已经修改了
如果你修改第二个查询分析器代码为:
set transaction isolation level read committed
select * from products where ProductId=1
同样运行,那第二个也要等待了,因为隔离级别是read committed,
只能读提交后的数据,不能读未提交的修改,这样就防止了
占用读,SQLSERVER2000里默认是read committed
说明,占用读也叫脏读,脏读就是修改了但没提交的数据,
在文本编辑器里也有脏读的概念,就是修改了但未保存的数据
1.7 不可重复读
事务里执行两次相同的查询时,查询出来的结果不相同,
说明是不可重复读,打开两个查询分析器,
第一个写:
use northwind
set transaction isolation level read committed
begin transaction tran1
select * from region where regionId=3
waitfor delay '00:00:10'
select * from region where regionId=3
commit transaction tran1
第二个写:
use northwind
update region set regionDescription='xx' where regionId=3
依次运行第一个和第二个分析器,第一个分析器等待10秒,第二个
不用等待立即得到结果,第一个分析器运行结果为:
3 Northern
3 xx
两次读得的值不相同,
修改第一个查询分析器代码为:
use northwind
set transaction isolation level repeatable read
begin transaction tran1
select * from region where regionId=3
waitfor delay '00:00:10'
select * from region where regionId=3
commit transaction tran1
第二个修改为:
use northwind
update region set regionDescription='yy' where regionId=3
同样依次运行第一个和第二个,看到第一个在等待,
第二个也在等待,第一个分析器运行结果为:
3 xx
3 xx
看看两次的区别,第一次我设置隔离级别为read committed,
第二次我设置为repeatable read,
repeatable read 会锁住读的数据,
read committed 会锁住修改的数据,
repeatable read会锁住insert、update、delete、select操作的数据
read committed只锁insert 、update、delete, 不锁select查询的数据
1.8 幻像读
打开两个查询分析器,第一个写:
use northwind
set transaction isolation level repeatable read
begin transaction tran1
select * from region
waitfor delay '00:00:10'
select * from region
commit transaction tran1
第二个写:
use northwind
insert into region values(5,'xx')
依次运行第一个和第二个分析器,第一个分析器等待10秒,第二个
不用等待立即得到结果,第一个分析器运行结果为:
1 Eastern
2 Western
3 Northern
4 Southern
1 Eastern
2 Western
3 Northern
4 Southern
5 xx
比较两次查询的结果,第二次查询多了一行,
修改第一个分析器的代码为:
use northwind
set transaction isolation level serializable
begin transaction tran1
select * from region
waitfor delay '00:00:10'
select * from region
commit transaction tran1
修改第二个分析器代码为:
use northwind
insert into region values(6,'yy')
再依次运行第一个和第二个分析器,
可以看到两个分析器都要等待,第一个的运行结果是:
两次查询返回的行数是相同的。
理解占用读、不可重复读、幻像读要从数据库如何操作来避免他们上
来理解,如果只从概念上去理解,概念往往很抽象,比较晦涩难懂,
而且概念往往只说到其中一个方面,应该弄清楚各种级别的琐是如何
避免出现占用读、不可重复读、幻像读的。
read uncommitted不设置锁,
read commmitted会锁住update、insert、delete
repeatable read会锁住update、insert、delete、select
seriablizable会锁住update、insert、delete、select
repeatable read和seriablizable的区别在于:
repeatable read锁住时别的事务不能update、delete锁住的数据,
但别的事务能够插入,
seriablizable锁住时别的事务不能update、delete、insert 说明:
repeatable read 和 seriablizable 对 select 的锁定采用范围的方式
要锁哪些行,主要是受where语句的限制,另外还受行锁、页锁、表锁方式
的限制,对于update、insert、delete的锁定范围比较明确,
repeatable read隔离级别对select的锁定也比较明确,
而seriablizable对select的锁定,当别的事务insert时,
哪些时候不能插入呢?这个范围如何确定?
因为锁的概念往往是针对已有的数据,而insert插入的数据是原来表里
没有的,原来表里没有,那又如何锁定呢?
比如:
set transaction isolation level seriablizable
select * from region
则锁住表的所有行,比如开始有四行,则锁住四行,
那insert一行呢,这里有个范围,那就是select的范围,
它会判断insert的行是否在锁定的范围之内,比如:
use northwind
set transaction isolation level serializable
begin transaction tran1
select * from region where regionId>6
waitfor delay '00:00:10'
commit transaction tran1
如果别的事务插入记录:
insert into region values(5,'yy')
因为插入的记录regionId=5,而6<5,不满足锁定的条件,
所以该插入是允许的,如果别的事务插入记录:
insert into region values(7,'yy')
因为7>6,满足条件,所以插入被阻塞,只能等待锁释放才能插入
不过,到底锁定哪些记录,这比较难说,锁做为SQLSERVER的内部管理,
到底是怎么样的不怎么清楚,我试过有些情况不满足的条件的记录也
被阻塞,不过有点是清楚的,那就是满足条件的一定被阻塞
1.9 隔离级别
前面已经说过了,有四种隔离级别:
read uncommitted
read committed
repeatable read
serializable
read committed是默认的隔离级别,
隔离级别对单个用户有用,比如你设置了隔离级别为serializable,
那只对你自己有用,对别的用户不起作用,设置了隔离级别后,
那一直有效,直到用户退出为止,
锁的作用主要是用来保证数据一致性的,
read uncommitted不会在被读的数据上放置锁,所以它执行
的速度是最快的,也不会造成阻塞和死锁,但因为数据一致
性问题,所以往往不采用,当然可以通过别的技术比如增加
rowverision列等来保证数据一致性,但对于复杂的操作,还
是选择事务比较安全,对于事务我经历过一些教训,
比如一次我在VB里保存数据,大致如:
begin transaction
declare @id as int
insert into A(...) values(...)
select @id=max(id) from t1
insert into B(AId...) values(@id...)
commit transation
其中A表的id字段是自动编号的,我先在A表插入一条记录,
再将A表刚插入的记录的Id插入到B表,必须保证@id是前面insert
生成的那个id,
但测试时,因为客户有很多电脑同时录,所以导致一些id不一致的情况,
为什么会不一致呢?我不是已经加了事务了吗?
做一个例子,同样打开两个查询分析器,第一个写:
use northwind
set transaction isolation level serializable
begin transaction tran1
insert into region values(10,'aa')
waitfor delay '00:00:10'
select max(regionId) from region
commit transaction tran1
第二个写:
insert into region values(11,'aa')
依次执行第一个和第二个分析器,
本来我希望第一个分析器里查询出来的是10,可结果却是11
在这里,唯一的方法是指定表锁,如:
insert into region with(TabLock) values(10,'aa')
只有指定表锁,让别的事务无法操作它,才能保证数据一致,
当然,如果是自动编号,那可以用 @@identity 来获取刚生成的Id号,
比如:
insert into orders(CustomerId) values('ALFKI')
print @@identity
这个技巧在别的数据库驱动程序里可能无效,事务才是普遍支持的 补充(表来源: SQL SERVER7.0 系统管理指南)
隔离级别 阻塞风险 防止占用读 防止不可重复读 防止幻像读
---------------------------------------------------------------
read uncommitted 最低 NO NO NO
read committed 较低 Yes NO NO
repeatable read 较高 Yes Yes NO
serializable 最高 Yes Yes Yes