sqlserver 并发处理中基础--事物和锁的使用

 

一. 事务（Transaction） 
事务主要是考虑到在异常情况下数据的安全性能和正确性。例如一个转账程序，有若干个语句，分别执行不同的功能，现在从第一个账户取出款项，正好此时因为其他原因导致程序中断，这样，第二个账户没有收到款项，而第一个账户的钱也没有了，这样明显是错误的。为了解决这种类似的情况，DBMS中提出了事务的概念。事务对上面的解决方式是：把上面的提取和转入作为一个整体，形成一个操作集合，这个集合中的操作要么都不执行，要么都执行！因此事务具有“原子性”，事务作为一个整体是不可分割的。 

一般地，SQL会隐性地开始事务，当然你也可以显式地开始事务。但是事务的结束必须是显式的，有两种方法可以结束事务： 

1. Commit(提交)。如果你认为所有的操作都完成了的话，可以结束事务，可以向系统对事物进行提交，提交之后，所有的修改都会生效了，在没有提交之前，所有的修改都可以作废的。 

2. Rollback(回滚)。回滚会结束当前事务，并且放弃自事务开始以来所有的操作，回到事务开始的状态。需要注意的是，可以在事物之内设置一些保留点（Save Point），这样可以不必放弃整个事务，可以根据需要Rollback到这个保留点。 

我们知道，Word和很多的软件都有Undo功能，事务其实和Undo的功能很类似！那么它的工作原理是什么呢？原来，在DBMS中，事务开始的时候，从这个时候记录你的每一个操作的数据、类型、对象，每一个操作对应一个相应的记录，当事务成功完成的时候，清除这些记录就可以了，如果出现异常，事务失败，那么可以倒过来，把我们保存得记录都作一次逆操作即可。如一个例子：有一个事务，其中包含 

(1) 删除第一条记录 

(2) 添加一个新的记录 

(3) 修改了第5条记录 

(4) ... 

三个步骤，那么DBMS在事务开始时候，分别记录每一个操作的信息： 

(1) 删除，第一条，记录数据 

(2) 添加，记录数据，假设得到一个记录号8 

(3) 修改、修改前的数据，修改后的数据 

如果在事务执行的过程中出现错误，假设在第4句出现错误，这个时候事务需要回滚，DBMS就会执行下面的动作： 

1. 修改第5条记录，用修改前的数据代替修改后的数据 

2. 删除第8条记录 

3. 添加原来第一条记录 

这样，数据就能回到事务开始前的状态，这也是Word等软件Undo的原理。 

在SQL Server中，很多语句会自动隐性开始事务，那么如何显式开始事务呢？其命令格式如下： 

BEGIN TRAN [Tran_Name] 

前面说过可以给事务设置一个Save Point，其命令如下： 

SAVE TRAN SavePointName 

最后，事务可以提交或者回滚，其格式分别如下： 

提交：COMMIT TRAN [Tran_Name] 

回滚：ROLLBACK TRAN [Tran_Name | SavePointName] 

上面的Tran也可以写成TRANSACTION。 

例：修改0000000学生的学号为0001156。前面我们学习过一个利用触发起来防止学号不一致的题目，这次我们利用事务来进行处理。 

--本处的事务是为了保证数据（学号）的一致性 

BEGIN TRAN MyTran /* 开始一个事务 */ 

UPDATE Score /* 更新Score表 */ 

SET S_No='0001156' 

WHERE S_No='0000000' 

IF @@ERROR<>0 

BEGIN 

PRINT 'An Error occur During UPDATE the table [Score]' 

ROLLBACK TRAN 

RETURN 

END 

UPDATE IDInfo /* 更新IdInfo表 */ 

SET S_No='0001156' 

WHERE S_No='0000000' 

IF @@ERROR<>0 /* 检测是否成功更新，@@ERROR返回上一个SQL语句状态 */ 

BEGIN 

PRINT 'An Error occur During UPDATE the table [IdInfo]' 

ROLLBACK TRAN /* 回滚 */ 

RETURN /* 退出本过程 */ 

END 

ELSE 

COMMIT TRAN MyTran /* 提交事务 */ 

二. 并发控制 
封锁(Locking) 
并发控制主要是考虑到多个用户同时存取数据库中的同一数据的情况。例如：飞机票的两个售票员A和B，现在系统里面有10张机票，A和B此时查看数据库里面，都得到10张，此时A卖出去一张，写回数据库数量为9张，接着B也卖出去一张，因为他以前读取的是10张，他因此他也写回9，这样就出现了错误，实际上只有8张票了！这个问题称之为“写覆盖”。经过分析和研究，有以下几个并发控制的情况（我们不考虑两个事务都“读”的情况，那样是不需要考虑的）： 


T1 
T2 
T1 
T2 
T1 
T2 

① 
Read A=10 

Read A=50 

Read B=100 

A+B=150 

Read A=100 

A=A*2 

Write A(200) 


② 

Read A=10 

Read B=100 

B=B*2 

Write B 

Read A=200 

③ 
A=A-1 

Write A(9) 

Read A=50 

Read B=200 

A+B=250 

验算（不正确） 

Rollback Tran 

A=100 


④ 

A=A-1 

Write A(9) 

写覆盖（修改丢失） 
不能重复读 
读“脏数据” 


在数据库中有一个特定的名词“脏数据”，用来描述那些被某些事务变更但是还没有提交的数据。 

那么如何解决用户同时访问数据的问题呢？总不能因为并发而限制用户的操作吧！并发控制的解决方案是“锁（LOCKING）”和事务。事务是并发控制的基本单位。事务不等于程序，一个程序可以包含多个事务。下面我们来详细讨论封锁机制。 

从数据库的角度来看，锁有两种类型：排它锁(Exclusive locks，简称X锁)和共享锁（Share locks，简称S锁）。X锁只允许加锁的事务进行操作，其他事务禁止加锁和任何操作（读、写），其他事务必须等待解锁才能继续运行！S锁可以允许多个事务同时对数据加锁，如果事务T对数据R加了S锁，那么其它的事务就不能再对R加X锁，但是可以加S锁，这样可以保证其他事务不能修改R。另外，还有一个加锁的范围需要考虑，我们可以进行行加锁，也可以进行表加锁，甚至可以进行数据库加锁，加锁的范围越大，那么实现就越简单，开销就越小，数据的并发程序就越低！反之，如果加锁范围越小，那么实现就越复杂，开销就越大，数据的并发程序就越高。一般地考虑到加锁的成本和性能，处理少量数据的事务应该尽可能减少加锁的作用范围，提高数据的并发程度，应该采用行锁，防止则应该采用表锁等等。另外，当一个事务操作完毕的时候，应尽可能快的解锁。在SQL Server中，还有一种更新锁(U锁)，这种锁和S锁是兼容的，如果一个事务要更新数据可以采取U锁，那么在事务初期可能是读取数据，此时是S锁，到后面，数据进行了修改，这个时候S锁自动升级为X锁。在SQL Server中，是自动强制锁定的，但是我们应该学习以便能够有效的设计应用程序。一般的情况下面，我们这样考虑：读取采用S锁，写入采取X锁。 

如果从程序员的角度来看，可以把锁分成两种：乐观锁(optimistic Lock)和悲观锁（Pessimistic Lock）。乐观锁就是在处理数据的时候，完全由数据库系统来自动实行加锁的管理，从前面我们知道，SQL Server采取的是乐观锁：对于Update,Insert,Delete自动采用X锁，对于Select，自动采用S锁；悲观锁则需要程序员自己来控制加锁、解锁的动作。 

下面让我们来看看，如何利用锁来解决前面的冲突： 


T1 
T2 
T1 
T2 
T1 
T2 

① 
XLock A 

Read A=10 

SLock A 

SLock B 

Read A=50 

Read B=100 

A+B=150 

XLock A 

Read A=100 

A=A*2 

Write A(200) 


② 

Request 

XLock A 

Waiting... 

Request 

XLock B 

Waiting... 

Request 

SLock A 

Waiting... 

③ 
A=A-1 

Write A(9) 

Commit 

XUnlock A 
waiting... 
Read A=50 

Read B=100 

A+B=150 

验算（OK） 

Commit 

SUnLock A 

SUnLock B 
Waiting... 
Rollback Tran 

A=100 

XUnlock A 
Waiting... 

④ 

XLock A 

Read A=9 

A=A-1 

Write A(8) 

Commit 

XUnlock A 
XLock B 

Read B=100 

B=B*2 

Write B=200 

Commit 

XUnLock B 

SLock A 

Read A=100 

Commit 

SUnLock A 


写覆盖（修改丢失） 
不能重复读 
读“脏数据” 


使用锁的时候，请注意一定要遵守下面两个事项：(1) 先锁后操作；(2) 事务结束之后必须解锁。最后总结一下如何利用锁来解决上面的三个问题（三级封锁协议）： 

l 1级封锁协议—---对事务T要修改的数据R加X锁，直到事务结束，防止“写覆盖”并且保证T是可以恢复的。 

l 2级封锁协议----1级封锁协议加上对T要读取的数据R加S锁，防止读“脏数据” 

l 3级封锁协议----1级封锁协议加上对T要读取的数据R加S锁，直到事务结束，可以解决重复读的问题。 

SQL Server是自动实现锁的，但是有的时候需要手动调整锁的级别，那么如何做呢？在SQL Server和Delphi中，都是采用隔离级别(Isolation Level)来实现的。在SQL Server 中有以下四种隔离级： 

1. READ COMMITTED 
和S锁类似，在此隔离级下SELECT 命令不会返回尚未提交的数据，也不能返回脏数据，它是SQL Server 默认的隔离级； 

2. READ UNCOMMITTED 
与READ COMMITTED 隔离级相反，它允许读取已经被其它用户修改但尚未提交确定的数据，限制级别最小； 

3. REPEATABLE READ 
在此隔离级下用SELECT 命令读取的数据在整个命令执行过程中不会被更改，其他事务不能执行Update和Delete，但是可以Insert。此选项会影响系统的效能，非必要情况最好不用此隔离级； 

4. SERIALIZABLE 
这是最大的限制，和X锁类似，不允许其他事务进行任何写访问。如非必要，不要使用这个选项。 

隔离级需要使用SET 命令来设定，其语法如下： 

SET TRANSACTION ISOLATION LEVEL 
{READ COMMITTED | READ UNCOMMITTED | REPEATABLE READ | SERIALIZABLE} 

在事务的开始使用这个命令即可，该隔离级别一直对该SQL Server连接（不是本事务）有效，直到下一次使用本命令设置了新的隔离级别为止。 

活锁和死锁 
下面我们来讨论关于锁的特殊情况： 

假设T1要更新R1和R2，首先它Lock了R1，而另外一个事务T2也要更新R2和R1，他首先锁定了R2，这个时候，T1要锁定R2必须等待，而T2也要锁定R1，这个时候也必须等待，这样T1和T2互相等待对方解锁，造成了死循环，这个就称之为“死锁”。 

再来看另外一个情况：T1锁定R，T2请求封锁R，这个时候必须等待，T3也请求封锁R，T1解锁之后，批准了T3的请求，于是T2必须等待，然后T3请求封锁R，T3解锁之后，批准了T4的请求，于是T2继续等待，...这样可能导致T2无限等待，这个就称之为“活锁”，活锁的解决比较简单，采取先来先服务策略即可。 

死锁一般可以采取如下的策略： 

1. 一次性对事务锁有请求的数据进行加锁，否则事务不能运行，缺点：降低了并发度； 

2. 预先规定一个封锁顺序，所有事务按照一定的顺序进行加锁； 

3. 不采取任何措施进行预防，而是检测是否有死锁和拆除死锁的方法。 

关于死锁在操作系统的课程中有详细的讨论。我们应该尽可能降低死锁的可能性：事务尽可能简短；避免在事务中和用户交互；尽量使用低级别的隔离级别等等。 

SQL Server中，对于死锁采取检测和拆除的方式：如果系统检测到有死锁，会选择一个事务作为牺牲者，该事务会自动终止并回滚，并且系统返回一个1025的错误给应用程序，任意一个程序有可能被系统作为牺牲品，因此，任意一个程序都应该处理1025错误，否则有可能你的应用程序不能正常运行。Oracle采取同样的策略。一般地，SQL Server会选择撤销花费代价最少的事务作为牺牲品。如果在SQL Server中要指定死锁时本事务的级别，可以使用如下的命令： 

SET DEADLOCK_PRIORITY {LOW | NORMAL} 

Low 表示如果发生死锁，那么当前事务作为首选的牺牲品，Normal表死按照正常的处理方式进行处理。前面讨论过，如果请求锁定，不能满足请求的时候，事务会进行等待，等待是不能无限期的，我们可以设定一个等待的时间，等待超过指定的时间之后，我们就认为可能发生了死锁，事物就自动回滚，锁定超时可以采用下面的命令来设置： 

SET LOCK_TIMEOUT {–1 | 0 | n} 

-1表示无限期等待，默认；0表示根本不等待；n则表示等待n毫秒，如果等待n毫秒之后还不能锁定成功，则返回锁定错误。 

另外@@LOCK_TIMEOUT表示返回当前的锁超时设置。例如我们可以用SELECT @@Lock_Timeout来查看。