Wednesday, December 31, 2008

[MySQL] The InnoDB Transaction Model and Locking

前段时间研究了sqlserver2005的事务与锁模式,最终的结果与自己以前的认识有相当大的出入,感觉受益非浅。因为我也明白,不同的数据库对ansi-sql定义的四个事务隔离级别的支持是不一样的,所以现在是时候看看mysql是怎么支持这些事务隔离级别的,它的锁行为又是怎么样的。
* 这里说的Mysql实际上是说Mysql5.0的InnoDB存储引擎,这是一个支持事务的引擎。
Mysql很早就实现了基于行版本的快照隔离级别(sqlserver2005中增加的实现),其实我也不清除这样的说法是不是正确,但是意思大致是相同的,就是数据库服务器会管理行数据的多个版本,每个事务只能获得一个快照。
Mysql支持不同粒度的锁,即row(record) lock和table lock. row lock表示应用到特定行上的锁, table lock表示应用到整个表上的锁。
LOCK TYPE
此外锁有两种类型:共享(S)允许事务读取一行,独占(X)允许事务更新或者删除一行。还有一种意向锁(intention locks),这种锁只会应用到table上,即这是一种table lock。它的作用就是用来说明当前事务(已经获得Is或者iX)想要请求表中某行的S或者X锁(这个概念与sqlserver的意向锁是相同的)。意向锁之间都是兼容的,即使IX与IX也是兼容的,这样多个事务之间可以同时在table上获得IX锁,但是只有一个事务可以获得某一行的X锁。而S和X锁即可以应用到行也可以应用到表,比如alter table就会在标上应用X锁。 关于锁的兼容性,可以参照下表:

X IX S IS
X Conflict Conflict Conflict Conflict
IX Conflict Compatible Conflict Compatible
S Conflict Conflict Compatible Compatible
IS Conflict Compatible Compatible Compatible
此外,Mysql有个很怪的行为,如果一个事务Ta读取记录R(select ...lock in share mode),获得了S锁,然后Tb在记录R上请求X锁(应该在表上应用IX锁),这个时候Tb会被阻塞,然后Ta要更新R,这个时候Ta会尝试从S锁升级到X锁(也会在表上应用IX锁,iX与IX是兼容的),这种情况下,我的理解是Tb会继续被阻塞,直到Ta事务结束释放所有的锁。但是mysql会抛出死锁异常,我都不知道怎么解释,难道先来先得,mysql必须先给Tb分配X锁??sqlserver采用的是阻塞行为,不会认为是死锁。

CONSISTENT NON-LOCKING READ

MySQL默认的隔离级别是repeatable read,如果要修改这个默认的隔离级别,可以通过修改my.cnf,设置如下:
[mysqld]
transaction-isolcation=REPETABLE READ
在READ COMMITTED和REPEATABLE READ的隔离级别下,MySQL默认会采用Consistent Read(一致读)。Consistent Read不会在行或者表上施加任何的锁(是指select...,不是select...lock in share mode或者select ... for update, 后面两种稍后会进行说明)。这样读不会阻塞写,写不会阻塞读,可以大大提高并发处理能力,但是数据不一致的风险也更大了。
在Read Committed下,select总是获得语句执行时数据库已提交数据的快照,而repeatable
Read下,同样的select语句,总是返回第一条select执行时数据库已提交数据的快照。比如事务Ta和事务Tb同时访问记录R,Tb稍后更新R到R1并且提交。如果Ta是Read Committed的,那么在Ta在Tb提交前后分别得到R和R1,而如果是repeatable read的话,Ta总是得到R。老实说,这种逻辑我很难理解,这里我觉得sqlserver处理的方式更合理。在sqlserver中
,如果Ta是Repeatable read,那么Ta在第一次读取R的时候就会获得R的S锁,并且不到事务结束不会释放,这样,Tb在想要修改的R的时候,会被阻塞,这样就保select...lock in share mode / select...for update证了Ta的可重复读。 而MySQL的这种处理方式有点怪,有种被蒙住眼睛,被欺骗的感觉。
select...lock in share mode / select...for update
Mysql默认会采用Consistent read,这样的select不会应用任何锁到资源(行或者表)上。但是可以显示的要求mysql的资源上应用锁,这就是select ... lock in share mode和select ...for update.
select ... lock in share mode会在匹配的行上应用S锁,直到事务结束才会释放。在什么情况下会需要应用S锁呢? 这里有表Parent和Child,每个Child中的记录必须关联到Parent中的一条记录。如果不采用S锁,我们在向Child中插入数据的时候,很可能Parent中关联行会被其他事务删除,这样数据就不一致了(当然也可以用数据库外键来保证)。如果我们在想child中进行插入的时候,先在parent管理记录上获得S锁,这样其他事务就无法修改这条记录了,就可以保证child与parent的一致性。
select ... for update这个与sqlserver的行为一致,不过sqlserver会加更新锁(U),而mysql会在资源上应用X锁。如果需要读取一个记录来进行更新的话,就可以使用select..for update。
InnoDB Record-level Lock

InnoDB has several types of record-level locks:

  • Record lock: 用来锁住一条索引记录。

  • Gap lock: This is a lock on a gap between index records, or a lock on the gap before the first or after the last index record.

  • Next-key lock: This is a combination of a record lock on the index record and a gap lock on the gap before the index record.

Record lock总是会锁住索引记录(index record),即使该表没有建立任何索引(这种情况下,mysql会自动创建一个clusterindex,然后record lock就会锁这些索引),实际上锁行就是锁索引。
默认情况下,事务隔离级别是repeatable read并且Innodb_locks_unsafe_for_binlog没有被激活,这个时候InnoDB会采用next-key lcok来搜索和扫描索引,从而避免幻影读取(为什么在repeatable read中避免幻影读取?)。 Next-key lock 组合了record lock和gap lock,除了会锁住索引记录以外,还会锁住索引之间的间隔。InnoDB会对访问到的每条记录加锁,尽管有些行根本不满足where子句的约定条件,这些记录会被加上Next-key lock。这就说明,我们在进行查询的时候,最好对索引行来查询,减少查询的访问范围,防止无辜的记录被锁上。如果针对一个没有索引的列来进行查询,那么会进行全表扫描,这就会导致整个表的记录都被锁上。
举例说明一下:
假设有表t,包含两列id int not null, name varchar(30) not null,其中id为primary key(即为一个cluster index),其中包含6条记录。
mysql> select * from t;
+----+------+
| i | name |
+----+------+
| 0 | t-0 |
| 2 | t-2 |
| 3 | t-3 |
| 10 | t-10 |
| 14 | t-14 |
| 15 | t-15 |
+----+------+
6 rows in set (0.00 sec)
首先在默认的事务隔离级别repeatable read下测试,假设两个事务Ta和Tb:
Ta>start transaction;
Ta>select * from t where name='t-2' for update;
Tb>start transaction;
Tb>insert into t(i,name) value(25,'t-25');
这个时候Tb会被阻塞,因为name不是索引字段,Ta会进行全表扫描,每条记录都被应用next-key lock(但是实际上这些锁又是锁在cluster indx record上的,这个概念和sqlserver应该是相同的,有cluster index的表就是cluster表,索引和记录是在一起的,相对于索引和记录分开的情况,cluster table会减少磁盘io,提高访问性能),所以Tb被阻塞。
接下来,我们使用索引进行查询。
Ta>start transaction;
Ta>select * from t where i=3 for update;
Tb>start transaction;
Tb>insert into t(i,name) value(25,'t-25');
这个时候Tb不会被阻塞,这里应该也并不表示Ta只会在i=3的索引记录上加锁,因为cluster index本质上是一个B树,所以应该是访问到的节点都被加锁(??)。

我们可以通过以下两种方式来取消gap lock(取消了gap lock,自然也取消了next-key lock,但是在readcommitted下仍然有next-key lock):
  • 如果事务的隔离级别 是read committed
  • 激活Innodb-locks-unsafe-for-binlog.

Locks Set by Different SQL Statements in InnoDB

实际上不论select还是update,delete都会把扫描的记录加锁,而不是仅仅将匹配where clause子句的记录加锁,可以通过下面的实验来验证。
Ta>set transaction isolation level read committed;
Tb>set transaction isolation level read committed;
Ta>start transaction;
Ta>update t set name='t-2-2' where name='t-2';
Tb>start transaction;
Tb>select * from t where name='t-1' for update;
这个时候Tb会被阻塞,因为Ta进行了全表扫描,所以的记录都被加上了X锁。为什么采取这种策略??SQLServer这里表现的更好,它只会锁住符合查询条件的记录。

InnoDB sets specific types of locks as follows:

  • SELECT ... FROM is a consistent read, reading a snapshot of the database and setting no locks unless the transaction isolation level is set to SERIALIZABLE. For SERIALIZABLE level, the search sets shared next-key locks on the index records it encounters.

  • SELECT ... FROM ... LOCK IN SHARE MODE sets shared next-key locks on all index records the search encounters.

  • SELECT ... FROM ... FOR UPDATE sets exclusive next-key locks on all index records the search encounters.

  • UPDATE ... WHERE ... sets an exclusive next-key lock on every record the search encounters.

  • DELETE FROM ... WHERE ... sets an exclusive next-key lock on every record the search encounters.

  • INSERT INTO ... VALUES (...) sets an exclusive lock on the inserted row. This lock is not a next-key lock and does not prevent other sessions from inserting into the gap before the inserted row. If a duplicate-key error occurs, a shared lock on the duplicate index record is set.

更多信息请参考Mysql5.0 Mannual:13.2.9 The InnoDB Transaction Model and Locking

Friday, December 26, 2008

JGroups - A toolkit of reliable multicast communication.

以前一直经常会看到一些框架依赖jgroups包,当时没有特别在意,以为又是jcp出来的某个规范的一个实现,因为精力有限,所以也没有用心去了解它到底是怎么一个东西。 这次因为用ibatis,然后研究ibatis的cache,而ibtis支持使用oscache。oscache是一个分布式的缓存系统,就是说,它是支持集群环境的,而对集群环境的支持就是通过jgroups来实现的。
终于说到jgroups,其实很多项目都用来jgroups来实现集群功能,包括jboss,jetty,jonas,tomcat等,当然还有oscache。
在开始深入研究jgroups之前,首先需要搞清除IP地址的意思和什么叫多播。

A类IP地址,B类IP地址,C类IP地址,E类IP地址

IP地址包括网络位和地址位,一般将ip地址与子网掩码进行&操作,就可以得到哪些是网络位。
1. A类IP地址 (1.0.0.0到126.255.255.255)
一个A类IP地址由1字节的网络地址和3字节主机地址组成,网络地址的最高位必须是“0”, 地址范围从1.0.0.0 到126.0.0.0。可用的A类网络有126个,每个网络能容纳1亿多个主机。
2. B类IP地址 (128.0.0.0到191.255.255.255)
一个B类IP地址由2个字节的网络地址和2个字节的主机地址组成,网络地址的最高位必须是“10”,地址范围从128.0.0.0到191.255.255.255。可用的B类网络有16382个,每个网络能容纳6万多个主机 。
3. C类IP地址 (192.0.0.0到223.255.255.255)
一个C类IP地址由3字节的网络地址和1字节的主机地址组成,网络地址的最高位必须是“110”。范围从192.0.0.0到223.255.255.255。C类网络可达209万余个,每个网络能容纳254个主机。
4. D类地址用于多点广播(Multicast)(224.0.0.0到239.255.255.255)
D类IP地址第一个字节以“lll0”开始,它是一个专门保留的地址。它并不指向特定的网络,目前这一类地址被用在多点广播(Multicast)中。多点广播地址用来一次寻址一组计算机,它标识共享同一协议的一组计算机。
5. E类IP地址 (240.0.0.0到247.255.255.255)
以“llll0”开始,为将来使用保留。
全零(“0.0.0.0”)地址对应于当前主机。全“1”的IP地址(“255.255.255.255”)是当前子网的广播地址。

在IP地址3种主要类型里,各保留了3个区域作为私有地址,其地址范围如下:
A类地址:10.0.0.0~10.255.255.255
B类地址:172.16.0.0~172.31.255.255
C类地址:192.168.0.0~192.168.255.255

广播(Broadcast),多播(Multicast)

广播就是向网络内的所有主机发送信息。广播地址一般是一个网段内的最大主机号,比如ip为192.168.0.120, 子网掩码为255.255.255.0,这是一个c类地址,前三个字节为网络地址,这个网段内的广播地址就是192.168.0.255.
多播(Multicast)是在一个网络上 一单个发送者和多个接收者之间的通信。多播技术在很多接收者同时想收听或看相同的源通过递送一单个信息流给很多接收者时被用于减少网络通信(因为广播会通知一个网络内所有的主机,而有些主机可能是对广播信息不感兴趣的,而多播就保证只有感兴趣的主机才会接受到信息)。对一个团体视 频会议,一个(n-1)次低的带宽被需要。“多播”被典型地用于指使用一个多播地址的IP多播。IPv6支持单播,多播,和任意播,但是在IPv6中广播 作为一个术语已经消失,但是被认为是一种形式的多播。但是那里有其它协议执行多播概念例如异步传输模式(ATM),它已经嵌入到“点-到-多点”或“多点-到-多点”连接机制中。
多播地址就是一个D类地址,从224.0.0.0到239.255.255.255,其中任意一个地址都可以作为多播地址。

Friday, December 19, 2008

SQLServer2005 基于行版本的隔离

Part 1:行版本控制隔离

行版本控制的隔离是SQL Server 2005一个新的隔离框架。使用行版本控制的隔离可以在大量并发的情况下,显著减少所得产生,并且与NoLock相比,它又可以显著降低肮脏读,幻影,丢失更新等现象的发生(READ_COMMITTED_SNAPSHOT)

当在基于行版本控制的隔离下运行的事务读取数据时,读取操作不会获取正被读取的数据上的共享锁(S 锁),因此不会阻塞正在修改数据的事务。另外,锁定资源的开销随着所获取的锁的数量的减少降至最低。使用行版本控制的已提交读隔离和快照隔离可以提供副本数据的语句级或事务级读取一致性。


行版本控制隔离:优势

使用行版本控制的隔离级别具有以下优点:
·读取操作检索一致的数据库快照。
·SELECT 语句在读取操作过程中不锁定数据(读取器不阻塞编写器,编写器也不阻塞读取器)。
·SELECT 语句可以在其他事务更新行时访问最后提交的行值,而不阻塞应用程序。
·死锁的数量减少。
·事务所需的锁的数量减少,这减少了管理锁所需的系统开销。
·锁升级的次数减少。

行版本控制隔离:原理

SQL Server 2005的行版本控制原理上很简单,就是在库表中每一行的记录上都悄悄的增加了一个类时间戳列(行版本列)。
当使用行版本控制的隔离时,SQL Server 2005 Database Engine 向使用行版本控制操作数据的每个事务分配一个事务序列号 (XSN)。事务在执行 BEGIN TRANSACTION 语句时启动。但是,事务序列号在执行 BEGIN TRANSACTION 语句后的第一次读/写操作时开始增加。事务序列号在每次分配时都增加 1。
当事务执行时,SQL Server根据行版本列,来提供的行的相应版本。

而SQLServer将维护所有在数据库中执行的数据修改的逻辑副本(版本)。特定的事务每次修改行时,数据库引擎 实例都存储以前提交的 tempdb 中行的图像版本。每个版本都标记有进行此更改的事务的事务序列号。已修改行的版本使用链接列表链接在一起。最新的行值始终存储在当前的数据库中并链接至版本存储区 tempdb 中存储的版本。(修改大型对象 (LOB) 时,只有已更改的片段才会复制到 tempdb 中的版本存储区, 对于短期运行的事务,已修改行的版本将可能保存在缓冲池中,而不会写入 tempdb 数据库的磁盘文件中。如果只是临时需要副本行,它将只是简单地从缓冲池中删除而不会引发 I/O 开销。)

MSDN上一再强调要关注提升tempdb的地位,不然因为tempdb的性能导致整个数据库性能下降,可就是无妄之灾了。

行版本控制隔离:种类

行版本控制分为两种已提交读快照隔离级别(READ_COMMITTED_SNAPSHOT)和快照隔离级别(ALLOW_SNAPSHOT_ISOLATION),他们属于行版本控制的隔离的范畴,但是实际上他们却有方方面面的不同。
下面是MSDN对他们的比较。


读取数据时的行为
SNAPSHOT隔离就像真实的快照,它会无视涉及行的变化。
在SNAPSHOT隔离下运行的事务将读取数据,然后由另一事务修改此数据。SNAPSHOT事务不阻塞由其他事务执行的更新操作,它忽略数据的修改继续从版本化的行读取数据。但是,当快照事务尝试修改已由其他事务修改的数据时,SNAPSHOT事务将生成错误并终止.

READ_COMMITTED_SNAPSHOT类似与以前的READ_COMMITTED隔离级别,
也就是说与快照事务相同的是,即使其他事务修改了数据,已提交读事务也将读取版本化的行。然而,与快照事务不同的是,已提交读将执行下列操作:

·在其他事务提交数据更改后,读取修改的数据。
·能够更新由其他事务修改的数据,而快照事务不能。

修改数据时的行为
在使用行版本控制的已提交读事务中,使用阻塞性扫描(其中读取数据值时将在数据行上采用更新锁(U 锁)完成选择要更新的行。这与不使用行版本控制的已提交读事务相同。如果数据行不符合更新标准,在该行上将释放更新锁并且将锁定下一行并对其进行扫描。
在快照隔离下运行的事务对数据修改采用乐观方法:直到数据被修改时才获取数据上的锁。不需要获取锁就可以选择要更新的行。当数据行符合更新标准时,快照事务将验证未被并发事务(在快照事务开始后提交)修改的数据行。如果数据行已在快照事务以外修改,则将出现更新冲突,同时快照事务也将终止。更新冲突由数据库引擎 处理,无法禁用更新冲突检测。

Reference: http://edu.itbulo.com/200612/110943.htm
Part 2: sqlserver的锁
这里我们说的都是没有激活快照隔离级别的情况下,得到的结果。
实际上在sqlserver2005支持行版本隔离以前,我是无法理解sqlserver对read committed隔离级别的支持的,我的理解就是一个事务如果正在修改某行数据(暂时不论是聚集表还是heap)的时候,另外一个事务如果要读取该行记录,应该是可以读取的,只不过读到的是已经持久到数据库中的数据(我在mysql上测试就得到了这样的结论)。
但是实际情况不是这样的,实际情况是另外那个读取的事务会被阻塞,直到更新的事务完成。为 资源上什么会这样呢? 因为第一个更新的事务会在该行记录(如果是聚集表,那么就是在key上,后面会详细说明聚集表和heap)上施加一个X锁,第二个事务读的时候会需要获得一个S锁,因为S锁和X锁是不兼容的,所以第二个事务必须等到第一个事务结束,所以会被阻塞。
在sqlserver中,一个事务在执行到某个sql语句的时候会要求获得相应的锁,然后会在事务结束(提交或者回滚)之后才释放这些锁。但是有个例外,如果事务的隔离级别是read committed或者read uncommitted,那么事务获得S锁会在读取了数据之后马上就释放。这里假设两个事务,它们都是先读取一条记录,然后更新这条记录, 在read committed下,这两个事务不会死锁,但是如果是repeatable read的话,就可能死锁。因为,在repeatable read的隔离级别下,事务读取记录后不会释放S锁,而是在事务结束后才会释放S锁,这样的话,假如第一个事务先读取了这条记录,然后第二个事务也读取了这条记录, 这个时候第一个事务要更新,更新的话需要获得X锁,而X锁和S锁是不兼容的,就表示第一个事务要等待第二个事务释放S锁,而同时第二个事务也要更新这条记录,它又等待第一个事务释放S锁,这样两个事务就死锁了。
为了解决这种死锁问题,sqlserver引入了更新锁U锁,U锁与S锁是兼容的,但是与U锁和X锁是不兼容的。U锁是用来处理读取数据进行更新这种情况,即先读取出数据,然后进行更新。还是说先前那个例子,两个事务都是先读取,但是在读取的时候必须显示说明申请UPDlock(select * from TABLE with updlock where ...),这样第一条记录在读取记录后就获得了该记录的U锁,第二个事务如果也要获得U锁,那么会被阻塞,但是能够获得S锁,然后第一个事务获得U锁后,就可以执行更新,这就避免了死锁。

sqlserver会采取如下两种方式之一来组织数据(http://msdn.microsoft.com/zh-cn/library/ms180978(SQL.90).aspx):
  • 聚集表是有聚集索引的表。
    数据行基于聚集索引键按顺序存储。聚集索引按 B 树索引结构实现,B 树索引结构支持基于聚集索引键值对行进行快速检索。索引中每个级别的页(包括叶级别的数据页)链接在一个双向链接的列表中。但是,通过使用键值来执行从一个级别到另一级别的导航。有关详细信息,请参阅聚集索引结构
  • 堆(heap)是没有聚集索引的表。
    数据行不按任何特殊的顺序存储,数据页也没有任何特殊的顺序。数据页不在链接列表内链接。有关详细信息,请参阅堆结构
索引分为聚集(cluster)索引和非聚集索引,它们具有相同的B树结构,但是有以下两点显著差别:
  • 非聚集索引的基础表的数据行不按非聚集键的顺序排序和存储。
  • 非聚集索引的叶层是由索引页而不是由数据页组成。
聚集索引结构
sqlserver中,索引页是按B树结构组织的,B树的每一个节点就是一个索引页。顶端节点称为根节点,根节点和叶节点之间的任何索引级别称为中间 级。
在聚集索引中,叶节点包含基础表的数据页。根节点和中间级别节点包含存有索引行的索引页。每个索引行包含一个键值和一个指针,该指针指向B树上某一中 间级叶或叶级索引上的某个数据行。每级索引的页均被链接在双向的链接列表中。


非聚集索引结构
聚集索引结构中,B树已经包含数据页(基础表包含在B树中?),而非索引结构中,索引与数据是分开的,非聚集索引中每个索引行都包含非聚集索引键值和行定位符。行定位符指向聚集索引或者堆中包含该键值的数据行。


什么是B树?B树就是常说的二叉搜索树,有以下三个特点:
  1. 所有非叶子节点至多拥有两个子节点
  2. 每个节点存储一个关键字
  3. 非叶子节点的左指针指向小于其关键字的子树,右指针指向大于其关键字的子树。

sqlserver在细粒度的资源上获得锁之后,一般会在更大粒度的资源上施加意向锁。比如如果在RID上获得了一个X锁,那么在该RID所属的KEy,和页上会施加IX锁,这样其他的事务访问这个页的时候,就知道该页下面有一个组件(key,rid)已经被其他事务施加了X锁。

什么是页(page)?
SQL Server 中数据存储的基本单位是页(Page)。数据库中的数据文件(.mdf 或 .ndf)分配的磁盘空间可以从逻辑上划分成页(从 0 到 n 连续编号)。磁盘 I/O 操作在页级执行。也就是说,SQL Server 每次读取或写入数据的最少数据单位是数据页。

  注意:日志文件不是用这种方式存储的,而是一系列日志记录。

数 据库被分成逻辑页面(每个页面8KB),并且在每个文件中,所有页面都被连续地从0到x编号,其中x是由文件的大小决定的。我们可以通过指定一个数据库 ID、一个文件ID、一个页码来引用任何一个数据页。当我们使用ALTER DATABASE命令来扩大一个文件时,新的空间会被加到文件的末尾。也就是说,我们所扩大文件的新空间第一个数据页的页码是x+1。当我们使用DBCC SHRINKDATABASE或DBCC SHRINKFILE命令来收缩一个数据库时,将会从数据库中页码最高的页面(文件末尾)开始移除页面,并向页码较低的页面移动。这保证了一个文件中的页码总是连续的。

每页的开头是 96 字节的标头,用于存储有关页的系统信息。此信息包括页码、页类型、页的可用空间以及拥有该页的对象的分配单元 ID。

  在数据页上,数据行紧接着标头按顺序放置。页的末尾是行偏移表,对于页中的每一行,每个行偏移表都包含一个条目。每个条目记录对应行的第一个字节与页首的距离。行偏移表中的条目的顺序与页中行的顺序相反。



Part 3: 实验
-- 激活sqlserver2005的快照隔离级别
--ALTER DATABASE NorthWind SET READ_COMMITTED_SNAPSHOT ON;
--ALTER DATABASE NorthWind SET ALLOW_SNAPSHOT_ISOLATION ON;

事务1:
set transaction isolation level read committed
--set transaction isolation level REPEATABLE READ
--set transaction isolation level SNAPSHOT

BEGIN TRANSACTION
select * from account where id=1;
--insert into account(id,name,age) values(6, 'liliming-6',30);
update account set age=age+1 where id=1;
WAITFOR DELAY '00:00:10'

-- 显示该事务获得的锁,只能在sqlserver2005或以后版本,并且创建的数据库兼容级别是sql90
SELECT L.request_session_id AS SPID,
DB_NAME(L.resource_database_id) AS DatabaseName,
O.Name AS LockedObjectName,
P.object_id AS LockedObjectId,
L.resource_type AS LockedResource,
L.request_mode AS LockType,
ST.text AS SqlStatementText,
ES.login_name AS LoginName,
ES.host_name AS HostName,
TST.is_user_transaction as IsUserTransaction,
AT.name as TransactionName,
CN.auth_scheme as AuthenticationMethod
FROM sys.dm_tran_locks L
JOIN sys.partitions P ON P.hobt_id = L.resource_associated_entity_id
JOIN sys.objects O ON O.object_id = P.object_id
JOIN sys.dm_exec_sessions ES ON ES.session_id = L.request_session_id
JOIN sys.dm_tran_session_transactions TST ON ES.session_id = TST.session_id
JOIN sys.dm_tran_active_transactions AT ON TST.transaction_id = AT.transaction_id
JOIN sys.dm_exec_connections CN ON CN.session_id = ES.session_id
CROSS APPLY sys.dm_exec_sql_text(CN.most_recent_sql_handle) AS ST
WHERE resource_database_id = db_id()
ORDER BY L.request_session_id

--WAITFOR DELAY '00:00:10'
SELECT * FROM account;
COMMIT


事务2:
set transaction isolation level read committed
--set transaction isolation level REPEATABLE READ
--set transaction isolation level SNAPSHOT

BEGIN TRANSACTION
select * from account;
--insert into account(id,name,age) values(6, 'liliming-6',30);
--update account set age=age+1 where id=1;
--WAITFOR DELAY '00:00:5'

-- 显示该事务获得的锁,只能在sqlserver2005或以后版本,并且创建的数据库兼容级别是sql90
SELECT L.request_session_id AS SPID,
DB_NAME(L.resource_database_id) AS DatabaseName,
O.Name AS LockedObjectName,
P.object_id AS LockedObjectId,
L.resource_type AS LockedResource,
L.request_mode AS LockType,
ST.text AS SqlStatementText,
ES.login_name AS LoginName,
ES.host_name AS HostName,
TST.is_user_transaction as IsUserTransaction,
AT.name as TransactionName,
CN.auth_scheme as AuthenticationMethod
FROM sys.dm_tran_locks L
JOIN sys.partitions P ON P.hobt_id = L.resource_associated_entity_id
JOIN sys.objects O ON O.object_id = P.object_id
JOIN sys.dm_exec_sessions ES ON ES.session_id = L.request_session_id
JOIN sys.dm_tran_session_transactions TST ON ES.session_id = TST.session_id
JOIN sys.dm_tran_active_transactions AT ON TST.transaction_id = AT.transaction_id
JOIN sys.dm_exec_connections CN ON CN.session_id = ES.session_id
CROSS APPLY sys.dm_exec_sql_text(CN.most_recent_sql_handle) AS ST
WHERE resource_database_id = db_id()
ORDER BY L.request_session_id

COMMIT


如果是聚集表,那么锁的最小粒度是key,因为数据页就是保存在索引结构中,如果是heap,那么锁的最小粒度为rid,即数据行,因为索引结构和数据页是分开的。

执行顺序:
先启动事务1,等待几秒钟,保证事务1已经执行了更新并且开始等待,这个时候执行事务2

--未激活快照隔离级别
结果1:数据库没有激活快照隔离级别
事务2会被阻塞,直到事务1结束。 因为事务1在更新id=1的记录,而事务2查询的结果集包含id=1的记录,就是说事务2需要获得所有满足条件的记录的S锁,但是事务1在id=1的记录上(聚集表则是key,heap为rid)已经获得了X锁,X锁和S锁不兼容,所以事务2会等待直到事务1结束。

结果2:数据库没有激活快照隔离级别
变体:事务1的update更改为insert
事务2会被阻塞,直到事务1结束。 因为事务1在insert后获得该记录的X锁,虽然还没有提交,但是事务2仍然会尝试获得该记录的S锁,所以会被阻塞(行记录只有一个版本,一切都由锁来控制)。

结果3:数据库没有激活快照隔离级别
变体:事务1和事务2都执行insert,并且插入的行id是一样的
事务2会被阻塞sqlserver应该会判断动作类型,由于是insert,所以第二个事务会等待第一个事务提交,然后才会跑主键冲突。如果并不阻塞,而直接就抛出这个异常,那么很可能,稍后事务1会回滚,这样的话抛出冲突就毫无道理了。

--激活快照隔离级别
结果4:
事务2不会被阻塞,会获得先前已经提交的数据。

结果5:
变体:事务1和事务2都执行insert,并且插入的行id是一样的
事务2会被阻塞,最后抛出主键冲突。

参考:
  • http://ramonli.blogspot.com/2007/01/understanding-transaction-isolation.html