查询未提交事务个数

 

查询未提交事务个数

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