数据库服务器的事务处理模式

数据库事务的自动提交模式：
在支持事务处理的数据库服务中，一般都会有数据库事务执行时候的自动提交模式，并且默认提交模式一般都是开启的，目前所知SQL

Server和MySQL 5.0以后的InnoDB表中都是这样的。如果想对事务进行测试，必须首先手动关闭事务的自动提交模式。
下面是SQL Server中的几种事务：
转自：http://software.it168.com/manual/sqlserver/ac_8_md_06_35bq.htm

自动提交事务
自动提交模式是 Microsoft® SQL Server™ 的默认事务管理模式。每个 Transact-SQL 语句在完成时，都被提交或回滚。如果一个语句成功地完成，则提交该语句；如果遇到错误，则回滚该语句。只要自动提交模式没有被显式或隐性事务替代，SQL Server 连接就以该默认模式进行操作。自动提交模式也是 ADO、OLE DB、ODBC 和 DB-Library 的默认模式。

SQL Server 连接在 BEGIN TRANSACTION 语句启动显式事务，或隐性事务模式设置为打开之前，将以自动提交模式进行操作。当提交或回滚显式事务，或者关闭隐性事务模式时，SQL Server 将返回到自动提交模式。

编译和运行时错误
在自动提交模式下，有时看起来 SQL Server 好像回滚了整个批处理，而不是仅仅一个 SQL 语句。这种情况只有在遇到的错误是编译错误而不是运行时错误时才会发生。编译错误将阻止 SQL Server 建立执行计划，这样批处理中的任何语句都不会执行。尽管看起来好像是产生错误之前的所有语句都被回滚了，但实际情况是该错误使批处理中的任何语句都没有执行。在此例中，由于编译错误，第三个批处理中的任何 INSERT 语句都没有执行。但看上去好像是前两个 INSERT 语句没有执行便进行了回滚。

USE pubs
GO
CREATE TABLE TestBatch (Cola INT PRIMARY KEY, Colb CHAR(3))
GO
INSERT INTO TestBatch VALUES (1, 'aaa')
INSERT INTO TestBatch VALUES (2, 'bbb')
INSERT INTO TestBatch VALUSE (3, 'ccc')  /* Syntax error */
GO
SELECT * FROM TestBatch   /* Returns no rows */
GO

在下面的示例中，第三个 INSERT 语句产生运行时重复键错误。由于前两个 INSERT 语句成功地执行并且提交，因此它们在运行时错误之后被保留下来。

USE pubs
GO
CREATE TABLE TestBatch (Cola INT PRIMARY KEY, Colb CHAR(3))
GO
INSERT INTO TestBatch VALUES (1, 'aaa')
INSERT INTO TestBatch VALUES (2, 'bbb')
INSERT INTO TestBatch VALUES (1, 'ccc')  /* Duplicate key error */
GO
SELECT * FROM TestBatch   /* Returns rows 1 and 2 */
GO

SQL Server 使用延迟的名称解析，其中对象名直到执行时才被解析。在下面的示例中，前两个 INSERT 语句执行并提交，当第三个 INSERT 语句由于引用了一个并不存在的表而产生运行时错误之后，前两行将仍然保留在 TestBatch 表中。

USE pubs
GO
CREATE TABLE TestBatch (Cola INT PRIMARY KEY, Colb CHAR(3))
GO
INSERT INTO TestBatch VALUES (1, 'aaa')
INSERT INTO TestBatch VALUES (2, 'bbb')
INSERT INTO TestBch VALUES (3, 'ccc')  /* Table name error */
GO
SELECT * FROM TestBatch   /* Returns rows 1 and 2 */
GO

三类事务：
转自：http://sswh.iteye.com/blog/1019104

数据库事务 
SQLServer支持三类事务：自动提交事务、隐性事务和显式事务。其中，自动提交和隐性事务由“SET IMPLICIT_TRANSACTIONS {ON|OFF}”切换。
 显式事务可以嵌套。按联机丛书的说明： 

引用

这主要是为了支持存储过程中的一些事务，这些事务可以从事务中已有的进程中调用，也可以从没有活动事务的进程中调用。
 
嵌套事务本质上只有最外层事务起作用，内部事务的begin和commit被忽略了(可以看做是这样)。 
SQLServer支持保存点，保存点用于回滚部分内部事务。 
SQLServer支持SQL92的事务隔离级别（控制由连接发出的所有SELECT语句的默认事务锁定行为），分别是： 
READ UNCOMMITTED 
READ COMMITTED（缺省） 
REPEATABLE READ 
SERIALIZABLE 

嵌套事务：“嵌套”只是一个形式，一个数据库连接上，最多同时只有一个事务存在。@@trancount变量用来决定内部事务的commit行为（究竟是该提交还是忽略）。
 Begin transaction [name]只有最外层的语句才开启事务,事务内部的begin语句被忽略了。（仅仅使@trancount+1），也只有最外层的事务名称才有效（仅用于回滚时区分保存点名称），内部事务的名称仅仅是为代码更容易阅读。
 Commit transaction [name]名称完全被忽略了。当@@trancount=1时提交事务，释放资源锁定。当事务不存在（@@trancount=0）时，将会引发一个错误。其他情况下，仅仅使@@trancount-1。
 Rollback transaction [name]名称要么不写，如果写的话，必须是最外层事务名称或者保存点名称。其他任何名称都会引发一个错误。当事务不存在（@@trancount=0）时，引发一个错误。回滚保存点时，对事务计数（@@trancount）没有影响；否则回滚事务到事务起点，并令@@trancount=0(不管回滚前是多少)。
 Save transaction <name>在事务内部设置一个保存点。名称是必须的。如果不在事务内部，将引发一个错误。 
对存储过程的调用不应该导致@@trancount发生变化。联机丛书的描述： 

引用

如果@@TRANCOUNT的值在存储过程完成时与过程执行时不同，则会生成一个266信息类错误。
 
再重述一下事务隔离级别控制由连接发出的所有SELECT语句的默认事务锁定行为的含义： 
这句话的意思是说，insert/delete/update语句的锁定和自身的事务隔离级别没有关系，都是排他锁，并且直到事务结束才释放。 
而select语句则按照事务隔离级别的不同，锁定方式不同。 
READ UNCOMMITTED下，select不加锁，所以，可以读到其他事务未提交的数据，也就是允许脏读。 
READ COMMITTED下，select语句加共享锁，如果其他事务在更新数据，同时尚未提交（排他锁还未释放），select语句就必须等待，所以，不会出现脏读。select语句执行完以后，共享锁就会释放（在事务提交前）；但是和再次读取之间，其他事务可能会更改数据，也就是说存在不可重复读。
 REPEATABLE READ下，共享锁的范围是本次读取影响的数据行或页。并且直到事务结束才释放锁，所以可以避免不可重复读。但是无法避免其他事务新增的记录也符合本次查询条件，所以会出现幻像读。
 SERIALIZABLE下，采用的是键范围锁模式，直到事务结束才释放锁。这样可以避免幻像读。 
下面摘自联机丛书： 

引用

 •共享锁：共享 (S) 锁允许并发事务读取 (SELECT) 一个资源。资源上存在共享 (S) 锁时，任何其它事务都不能修改数据。一旦已经读取数据，便立即释放资源上的共享 (S) 锁，除非将事务隔离级别设置为可重复读或更高级别，或者在事务生存周期内用锁定提示保留共享 (S) 锁。
 •排它锁：用于数据修改操作，例如 INSERT、UPDATE 或 DELETE。排它 (X) 锁可以防止并发事务对资源进行访问。其它事务不能读取或修改排它 (X) 锁锁定的数据。
 •键范围锁定：键范围锁定解决了幻像读并发问题，并支持可串行事务。键范围锁覆盖单个记录以及记录之间的范围，可以防止对事务访问的记录集进行幻像插入或删除。键范围锁仅用于代表在可串行隔离级别上操作的事务。
 •锁升级：锁升级是将众多细粒度锁转换为较少的粗粒度的锁的过程，以削减系统开销。当事务超过它的升级极限时， SQL Server自动将行锁和页锁升级为表锁。 

锁机制讲解：
转自：http://www.iteye.com/topic/186542

对锁机制的研究要具备两个条件： 
1．数据量大 
2．多个用户同时并发 
如果缺少这两个条件，数据库不容易产生死锁问题。研究起来可能会事倍功半。如果这两个条件都有，但你还是按数据库缺省设置来处理数据，则会带来很多的问题，比如： 
1）丢失更新 
A,B两个用户读同一数据并进行修改,其中一个用户的修改结果破坏了另一个修改的结果 
2）脏读 
A用户修改了数据时,B用户也在读该数据,但A用户因为某些原因取消了对数据的修改,数据恢复原值,此时B得到的数据就与数据库内的数据产生了不一致 
3）不可重复读 
B用户读出该数据并修改,同时，A用户也在读取数据，此时A用户再读取数据时发现前后两次的值不一致 
SQL SERVER 作为多用户数据库系统，以事务为单位，使用锁来实现并发控制。SQLSERVER使用“锁”确保事务完整性和数据一致性。 

一、锁的概念 
锁（LOCKING）是最常用的并发控制机构。是防止其他事务访问指定的资源控制、实现并发控制的一种主要手段。锁是事务对某个数据库中的资源（如表和记录）存取前，先向系统提出请求，封锁该资源，事务获得锁后，即取得对数据的控制权，在事务释放它的锁之前，其他事务不能更新此数据。当事务撤消后，释放被锁定的资源。
 当一个用户锁住数据库中的某个对象时，其他用户就不能再访问该对象 

二、锁的粒度 
SQL Server 2000 具有多粒度锁定，允许一个事务锁定不同类型的的资源。为了使锁定的成本减至最少，SQL Server 自动将资源锁定在适合任务的级别。锁定在较小的粒度（例如行）可以增加并发但需要较大的开销，因为如果锁定了许多行，则需要控制更多的锁。锁定在较大的粒度（例如表）就并发而言是相当昂贵的，因为锁定整个表限制了其它事务对表中任意部分进行访问，但要求的开销较低，因为需要维护的锁较少。SQL Server 可以锁定行、页、扩展盘区、表、库等资源。
 •资源 级别 描述 
•RID 行锁 表中的单个行 
•Key 行级锁 索引中的行 
•Page 页级锁 一个数据页或者索引页 
•Extent 页级锁 一组数据页或者索引页 
•Table 表级锁 整个表 
•Database 数据库级锁 整个数据库 

选择多大的粒度，根据对数据的操作而定。如果是更新表中所有的行，则用表级锁;如果是更新表中的某一行，则用行级锁。 
行级锁是一种最优锁，因为行级锁不可能出现数据既被占用又没有使用的浪费现象。但是，如果用户事务中频繁对某个表中的多条记录操作，将导致对该表的许多记录行都加上了行级锁，数据库系统中锁的数目会急剧增加，这样就加重了系统负荷，影响系统性能。因此，在SQL Server中，还支持锁升级(lock escalation)。
 所谓锁升级是指调整锁的粒度，将多个低粒度的锁替换成少数的更高粒度的锁，以此来降低系统负荷。在SQL Server中当一个事务中的锁较多，达到锁升级门限时，系统自动将行级锁和页面锁升级为表级锁。
 特别值得注意的是，在SQL Server中，锁的升级门限以及锁升级是由系统自动来确定的，不需要用户设置。 

三、锁的模式 
锁模式以及描述表 

锁模式 描述 •共享（S） 用于不更改或不更新数据（只读操作），如SELECT语句 
•更新（U） 用于可更新的资源中。防止当多个会话在读取、锁定以及随后可能进行的资源更新时发生常见形式的死锁。 
•排它（X） 用于数据修改操作，例如 INSERT、UPDATE或DELETE。确保不会同时对同一资源进行多重更新 
•意向 当 Microsoft SQL Server 数据库引擎获取低级别的锁时，它还将在包含更低级别对象的对象上放置意向锁.例如： 
当锁定行或索引键范围时，数据库引擎将在包含行或键的页上放置意向锁。当锁定页时，数据库引擎将在包含页的更高级别的对象上放置意向锁。 
意向锁的类型为：意向共享（IS）、意向排它（IX）以及意向排它共享（SIX） •架构 在执行依赖于表架构的操作时使用。架构锁的类型为：架构修改（Sch-M）和架构稳定（Sch-S）
 •大容量更新（BU） 向表中大容量复制数据并指定了TABLOCK提示时使用 



四 SQL Server 中锁的设置 
1 处理死锁和设置死锁优先级 
死锁就是多个用户申请不同封锁,由于申请者均拥有一部分封锁权而又等待其他用户拥有的部分封锁而引起的无休止的等待 
可以使用SET DEADLOCK_PRIORITY控制在发生死锁情况时会话的反应方式。 
Syntax: 
SET DEADLOCK_PRIORITY { LOW | NORMAL} 
其中LOW说明该进程会话的优先级较低，在出现死锁时，可以首先中断该进程的事务。 
2 处理超时和设置锁超时持续时间。 
@@LOCK_TIMEOUT 返回当前会话的当前锁超时设置，单位为毫秒 
SET LOCK_TIMEOUT 设置允许应用程序设置语句等待阻塞资源的最长时间。当语句等待的时间大于 LOCK_TIMEOUT 设置时，系统将自动取消阻塞的语句，并给应用程序返回"已超过了锁请求超时时段"的 1222 号错误信息
 示例 
1）将锁超时期限设置为 1,800 毫秒。 
SET LOCK_TIMEOUT 1800 
2) 配置索引的锁定粒度 
可以使用 sp_indexoption 系统存储过程来设置用于索引的锁定粒度 
3）设置事务隔离级别 
SET   TRANSACTION   ISOLATION   LEVEL 

五 查看锁的信息 
1 执行 EXEC SP_LOCK 报告有关锁的信息 
2 查询分析器中按Ctrl+2可以看到锁的信息 

六、奇怪的sql语句 



Java代码  
1.begin tran  
2.update titles set title_idid＝title_id  where 1＝2  
3.if (selectavg(price)fromtitles)>$15  
4.begin  
5.update titles set price＝price*1.10  
6.where price<(select avg(price)from titles)  
7.end  
8.commit tran  
 

update titles set title_idid＝title_id  where 1＝2，这个条件是永远也不会成立的，如此写的含义是什么呢？ 
这里的where子句看起来很奇怪，尽管计算出的结果总是false。当优化器处理此查询时，因为它找不到任何有效的SARG，它的查询规划就会强制使用一个独占锁定来进行表扫描。此事务执行时，where子句立即得到一个false值，于是不会执行实际上的扫描，但此进程仍得到了一个独占的表锁定。
 因为此进程现在已有一个独占的表锁，所以可以保证没有其他事务会修改任何数据行，能进行重复读，且避免了由于holdlock所引起的潜在性死锁。 
但是，在使用表锁定来尽可能地减少死锁的同时，也增加了对表锁定的争用。因此，在实现这种方法之前，你需要权衡一下：避免死锁是否比允许并发地对表进行访问更重要。 
所以，在这个事务中，没有其他进程修改表中任何行的price。 

七 如何避免死锁 
1 使用事务时，尽量缩短事务的逻辑处理过程，及早提交或回滚事务； 
2 设置死锁超时参数为合理范围，如：3分钟-10分种；超过时间，自动放弃本次操作，避免进程悬挂； 
3 所有的SP都要有错误处理（通过@error） 
4 一般不要修改SQL SERVER事务的默认级别。不推荐强行加锁 
5 优化程序，检查并避免死锁现象出现； 
1）合理安排表访问顺序 
2）在事务中尽量避免用户干预，尽量使一个事务处理的任务少些。 
3）采用脏读技术。脏读由于不对被访问的表加锁，而避免了锁冲突。在客户机/服务器应用环境中，有些事务往往不允许读脏数据，但在特定的条件下，我们可以用脏读。 
4）数据访问时域离散法。数据访问时域离散法是指在客户机/服务器结构中，采取各种控制手段控制对数据库或数据库中的对象访问时间段。主要通过以下方式实现: 合理安排后台事务的执行时间，采用工作流对后台事务进行统一管理。工作流在管理任务时，一方面限制同一类任务的线程数（往往限制为1个），防止资源过多占用; 另一方面合理安排不同任务执行时序、时间，尽量避免多个后台任务同时执行，另外，避免在前台交易高峰时间运行后台任务
 5）数据存储空间离散法。数据存储空间离散法是指采取各种手段，将逻辑上在一个表中的数据分散到若干离散的空间上去，以便改善对表的访问性能。主要通过以下方法实现: 第一，将大表按行或列分解为若干小表; 第二，按不同的用户群分解。
 6）使用尽可能低的隔离性级别。隔离性级别是指为保证数据库数据的完整性和一致性而使多用户事务隔离的程度，SQL92定义了4种隔离性级别：未提交读、提交读、可重复读和可串行。如果选择过高的隔离性级别，如可串行，虽然系统可以因实现更好隔离性而更大程度上保证数据的完整性和一致性，但各事务间冲突而死锁的机会大大增加，大大影响了系统性能。
 7）使用Bound Connections。Bound connections 允许两个或多个事务连接共享事务和锁，而且任何一个事务连接要申请锁如同另外一个事务要申请锁一样，因此可以允许这些事务共享数据而不会有加锁的冲突。
 8）考虑使用乐观锁定或使事务首先获得一个独占锁定。  

八如何对行、 表、数据库加锁 
1 如何锁一个表的某一行 



Java代码  
1.SET TRANSACTION ISOLATION LEVEL READ UNCOMMITTED  
2.SELECT * FROM table1 ROWLOCK WHERE A = 'a1'  
 
2 锁定数据库的一个表 
select col1 from 表 (tablockx) where 1=1 ; 
加锁后其它人不可操作，直到加锁用户解锁，用commit或rollback解锁 
3.实例 
建表 



Java代码  
1.create table table1(A varchar(50)  not  null, B varchar(50) ,C varchar(50));  
2.create table table2(D varchar(50),E varchar(50))  
3.insert table1 (A,B,C) values(‘a1’,’b1’,’c1’);  
4.insert table1 (A,B,C) values(‘a2’,’b2’,’c2’);  
5.insert table1 (A,B,C) values(‘a3’,’b3’,’c3’);  
6.insert table2 (D,E) values(‘d1’,’e1’);  
7.insert table2 (D,E) values(‘d2’,’e2’);  
 
1）排它锁 



Java代码  
1.-- A事务先更新table1表，在更新时，对其他事务进行排他  
2.begin tran  
3.update table1 set A='aa' where B='b2'；  
4.waitfor delay '00:00:30'； --等待30秒  
5.commit tran  
6.-- A事务先更新table2表  
7.begin tran  
8.select * from table1 where B='b2';  
9.commit tran  
若同时执行上述两个事务，则select查询必须等待update执行完毕才能执行即要等待30秒 
2）共享锁 



Java代码  
1.-- A事务先查询table1表，在查询时，加共享锁，防止其他事务对该表进行修改操作  
2.begin tran  
3.select * from table1 holdlock where B='b2' ;  
4. -holdlock人为加锁  
5.waitfor delay '00:00:30';--等待30秒  
6.commit tran  
7.-- A事务先查询table1表，后更改table1表  
8.begin tran  
9.select A,C from table1 where B='b2';  
10.update table1 set A='aa' where B='b2';  
11.commit tran  
若并发执行上述两个事务，则B事务中的select查询可以执行,而update必须等待第一个事务释放共享锁转为排它锁后才能执行即要等待30秒 
3）死锁 



Java代码  
1.-- A事务先更新table1表，然后延时30秒，再更新table2表；  
2.begin tran  
3.update table1 set A='aa' where B='b2';  
4.--这将在 Table1 中生成排他行锁，直到事务完成后才会释放该锁。  
5.waitfor delay '00:00:30';  
6.--进入延时  
7.update table2 set D='d5' where E='e1' ;  
8.commit tran  
9.-- B事务先更新table2表，然后延时10秒，再更新table1表；  
10.begin tran  
11.update table2 set D='d5' where E='e1';  
12.--这将在 Table2 中生成排他行锁，直到事务完成后才会释放该锁  
13.waitfor delay '00:00:10'  
14.--进入延时  
15.update table1 set A='aa' where B='b2' ;  
16.commit tran  
若并发执行上述两个事务，A,B两事务都要等待对方释放排他锁，这样便形成了死锁。 

九、sqlserver提供的表级锁 
sqlserver所指定的表级锁定提示有如下几种 
1． HOLDLOCK: 在该表上保持共享锁，直到整个事务结束，而不是在语句执行完立即释放所添加的锁。  
2． NOLOCK：不添加共享锁和排它锁，当这个选项生效后，可能读到未提交读的数据或“脏数据”，这个选项仅仅应用于SELECT语句。   
3． PAGLOCK：指定添加页锁（否则通常可能添加表锁） 
4． READCOMMITTED用与运行在提交读隔离级别的事务相同的锁语义执行扫描。默认情况下，SQL Server 2000 在此隔离级别上操作。 
5． READPAST: 跳过已经加锁的数据行，这个选项将使事务读取数据时跳过那些已经被其他事务锁定的数据行，而不是阻塞直到其他事务释放锁，READPAST仅仅应用于READ COMMITTED隔离性级别下事务操作中的SELECT语句操作
 6． READUNCOMMITTED：等同于NOLOCK。    
7． REPEATABLEREAD：设置事务为可重复读隔离性级别。  
8． ROWLOCK：使用行级锁，而不使用粒度更粗的页级锁和表级锁。 
9． SERIALIZABLE：用与运行在可串行读隔离级别的事务相同的锁语义执行扫描。等同于 HOLDLOCK。 
  10． TABLOCK：指定使用表级锁，而不是使用行级或页面级的锁，SQL Server在该语句执行完后释放这个锁，而如果同时指定了HOLDLOCK，该锁一直保持到这个事务结束。
 11． TABLOCKX：指定在表上使用排它锁，这个锁可以阻止其他事务读或更新这个表的数据，直到这个语句或整个事务结束。 
12． UPDLOCK ：指定在读表中数据时设置更新锁（update lock）而不是设置共享锁，该锁一直保持到这个语句或整个事务结束，使用UPDLOCK的作用是允许用户先读取数据（而且不阻塞其他用户读数据），并且保证在后来再更新数据时，这一段时间内这些数据没有被其他用户修改
 SELECT * FROM table WITH (HOLDLOCK) 其他事务可以读取表，但不能更新删除 
SELECT * FROM table WITH (TABLOCKX) 其他事务不能读取表,更新和删除 

十、应用程序锁 

应用程序锁就是客户端代码生成的锁，而不是sql server本身生成的锁处理应用程序锁的两个系统存储过程 
sp_getapplock： 锁定应用程序资源 
sp_releaseapplock： 为应用程序资源解锁