查询未提交事务个数

查询未提交事务个数

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 northwindselect * 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) 可以看到对应的表为 Products1.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 Southern1 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、selectrepeatable 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 serializableread 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