[学习笔记]在数据库层面应对并发访问产生的问题

参考资料：
《数据库事务管理基础知识》
《sqlserver锁机制》
《Understanding Locking in SQL Server》 
《深入解析SQL Server2008-事务和并发性》

两种并发模型

悲观并发

数据库表现的很悲观，总是假设会出现冲突，假设系统中有足够的数据修改操作，因而任何给定的读取操作都有可能受到另一个用户的数据修改操作的影响。悲观并发模型通过获得正在被读取数据上的锁，使其他进程无法修改该数据而避免冲突。它也获得正在被修改的数据上的锁，使其他进程无法访问该数据。在悲观并发环境中，读取者阻塞写入者，写入者阻塞读取者和其他写入者。

乐观并发

乐观并发假设系统中有足够少的冲突数据修改操作，因而任何单个事务都不太可能修改另一个事务正在修改的数据。一个读取数据的进程可以读取到的是此进程刚刚开始读取时的数据版本，读取者不阻塞写入者，写入者也不阻塞读取者。

事务的基本概念

“事务”是一个逻辑工作单元，它包括一系列的操作。事务处理可以确保除非事务性单元内的所有操作都成功完成，否则不会永久更新面向数据的资源。通过将一组相关操作组合为一个要么全部成功要么全部失败的单元，可以简化错误恢复并使应用程序更加可靠。

 

事务包括4个基本特性，也就是我们常说的ACID，其中包括：
1.Atomic（原子性，这里的“原子”即代表事务中的各个操作不可分割）
事务中包含的操作被看作是一个逻辑单元，这个逻辑单元中的操作要么全部成功，要么全部失败。
2.Consistency（一致性）
一致性意味着，只有合法的数据才会被写入数据库，否则会回滚到最初状态。事务确保数据库的状态从一个一致状态转变为另一个一致状态。
3.Isolation（隔离性） (也翻译为“独立性”)
多个事务并发执行时，一个事务的执行不应影响其他事务的执行。 一个事务看到的数据要么是另外一个事务修改这些事务之前的状态，要么是第二个事务已经修改完成的数据，但是这个事务不能看到正在修改的数据。这种特征也称为串行性。
4.Durability（持久性）
已被提交的事务对数据库的修改应该永久保存在数据库中，即使出现系统故障也应该如此。比如在事务提交之前忽然断电，那么应保证整个事务在系统重启时回滚，如果事务已提交之后断电，则应保证事务产生的结果已经永久地存在数据库中。

并发事务处理时产生的意外情况

1.丢失更新（Lost Update）
事务1：更新一条记录。
事务2：更新事务1处理中的记录。
事务1：调用commit进行提交。
事务2：调用commit进行提交。
此时事务1所做的修改完全被事务2的修改覆盖，称为丢失更新。
当两个或多个事务选择同一行，然后基于最初选定的值更新该行时，会发生丢失更新问题。因为每个事务都不知道其它事务的存在，最后的更新将重写由其它事务所做的更新，这将导致数据丢失。

2.脏读（Dirty Read）：一个事务读取到了另外一个事务没有提交的数据，所以你可能会看到一些最后被另一个事务回滚掉的数据。
事务1：更新一条记录。
事务2：读取事务1更新的记录。
事务1：调用commit进行提交或调用rollback进行回滚。
此时事务2读取到的数据是保存在数据库内存中的数据，称为脏读。读到的数据为脏数据
详细解释：脏读就是指当一个事务正在访问数据，并且对数据进行了修改，而这种修改还没有提交到数据库中，这时，另外一个事务也访问这个数据，然后使用了这个数据。因为这个数据是还没有提交的数据，那么另外一个事务读到的这个数据是脏数据，依据脏数据所做的操作可能是不正确的。

3.不可重复读（NonRepeatable Read）：在同一事务中，两次读取同一数据，得到内容不同。即一个事务再次读取之前曾经读取过的数据时，发现该数据已经被另一个已提交的事务修改。
事务1：查询一条记录。
事务2：更新事务1查询的记录。
事务2：调用commit进行提交。
事务1：再次查询上次的记录。
此时事务1对同一数据查询了两次，可得到的内容不同，称为不可重复读。

4.幻读（Phantom Read）：同一事务中，用同样的操作读取两次，得到的记录数不相同。即一个事务重新执行一个查询，返回一批符合查询条件的记录，但这些记录中包含了因为其它最近提交的事务而产生的新记录。
事务1：查询表中所有记录
事务2：插入一条或一批记录
事务2：调用commit进行提交
事务1：再次查询表中所有记录
此时事务1两次查询到的记录是不一样的，称为幻读
详细解释：幻读是指当事务不是独立执行时发生的一种现象，例如第一个事务对一个表中的数据进行了修改，这种修改涉及到表中的全部数据行。同时，第二个事务也修改这个表中的数据，这种修改是向表中插入一行新数据。那么，以后就会发生操作第一个事务的用户发现表中还有没有修改的数据行，就好象发生了幻觉一样。

事务隔离

为了避免并发事务处理时产生上述的种种意外情况，标准SQL规范定义了4中事务隔离等级。这四种隔离等级描述了四种不同程度的并发控制方式，具体实现这些并发控制需要用到“锁”，下文会详细介绍锁。

以下是四种级别的事务隔离：

1.Read Uncommitted
最低等级的事务隔离，直译就是读“未提交读”，意思就是即使另一个并发事务还没有提交,当前事务也能读到这个改变，这是很不安全的。Read Uncommitted允许脏读，但不允许更新丢失。如果一个事务已经开始写数据，则另外一个事务则不允许同时进行写操作，但允许其他事务读此行数据。
此级别的事务隔离等级对于大多数逻辑严格的应用系统而言是难以接受的，脏读取的出现将为系统的并发逻辑带来极大隐患。
2.Read Committed
直译就是读“提交读”，意思就是一个事务不会读到另一个事务已修改但未提交的数据。若A事务已update某行数据，但A事务尚未提交(或回滚)，而此时B事务欲读取改行数据，则B事务会被要求等待至A事务执行完毕(即提交或回滚完毕)。 Read Committed允许不可重复读取，但不允许脏读取。
此级别的事务隔离等级是最常用的,并且是大多数数据库的默认隔离级别，同时也适用于大多数系统。
3.Repeatable Read
直译就是“可重复读取”，这就是说同一事务先后执行相同查询语句的时候,得到的结果是一样的。若A事务对某行进行了查询，则在A事务尚未执行完毕之前，不允许其它事务对该行进行update操作。Repeatable Read禁止不可重复读取和脏读，但是有时可能出现幻读。读取数据的事务将会禁止其它事务进行写操作（但允许读事务），写数据的事务则禁止任何其他事务。
4.Serializable
翻译为“串行读”，最高等级的事务隔离级别，也提供了最严格的隔离机制。直译就是“序列化”，意思是说这个事务执行的时候不允许别的事务并发执行。Serializable提供严格的事务隔离级别，可以防止脏读，不可重复读取和幻读，它要求事务序列化执行，事务只能一个接着一个地执行，但不能并发执行。

是

总结：

隔离级别	更新丢失	脏读	不可重复读	幻象	说明
未提交读(read uncommitted)	否	是	是	是	如果其他事务更新，不管是否提交，立即执行
提交读(read committed默认)	否	否	是	是	读取提交过的数据。如果其他事务更新没提交，则等待
可重复读(repeatable read)	否	否	否	是	查询期间，不允许其他事务update
可串行读(serializable)	否	否	否	否	查询期间，不允许其他事务insert或delete

 

数据库中的锁

事务和锁是两个紧密联系的概念，事务使用锁来实现上文叙述的种种事务并发控制，以防止事务并发处理时所产生的种种问题。对于多用户系统来说，锁机制是必须的。

可以锁定的资源包括行、页、簇、表和数据库。

锁的类型主要包括共享锁和排它锁。

共享锁:为了实现共享读（select）。

一个对象(这个对象可以是一行数据、一张数据表等，视锁的粒度而定)被加上共享锁时，该对象不允许被update(包括加锁的事务本身和其他事务都不能update)。一个对象可以被加上多个共享锁。从锁的角度讲，当一个对象被加了共享锁时，不允许其他事务获取排他锁。

排他锁(或翻译为独占锁):

一个对象上只能加一个排他锁，当某对象被事务A加上排他锁时，其他的事务就不能对锁定的数据获取共享锁和排他锁（即排他锁与共享锁不能兼容，更多信息请查看锁兼容性）。

共享锁是若事务A锁定了某对象，则事务B对该对象只能读不能写，排它锁是若事务A锁定了某对象，则事务B对该对象既不能读也不能写。

锁定的对象越小(例如锁定行)，并发性越好，但管理开支也越大，因为要维护很多的锁。锁定比较大的对象(例如每一个事务开始时都锁定整个数据库)会大大降低并发性，但是成本开支比较低，因为只需维护比较少的锁。

 

注意：有的资料上讲解到“一个连接写的时候，另一个连接亦可以写”，实际上写的这种情况是各个连接的读写的数据不是相同的行，也就是说各个连接锁定的数据不同。

 

SqlServer中的锁

SQL Server 可以锁定行、页、扩展盘区、表、库等资源。

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

 

Sqlserver中锁的种类

共享锁（S）：用于不更改或不更新数据（只读操作），如SELECT语句时，多个进程可以在相同的数据上持有共享锁，但是进程不能在已经具有共享锁的数据上在获取独占锁(除非请求独占锁的进程就是持有共享锁的进程本身)。通常共享锁只要数据读取完毕就会被释放，但是这可以通过使用查询提示或设置不同的事务隔离级别而改变。

更新锁（U）：更新锁实际上不是一种独立的锁，而是共享锁和独占锁的混合。当SqlServer执行数据修改操作却首先要搜索表以找到需要修改的资源时，会获得更新锁。

持有更新锁不足以是您有权限改变数据，所有的修改都要求持有被修改资源的独占锁，更新锁的作用就好像是一个序列化闸门，将后序申请独占锁的序列压入队列中(许多进程都可以对一个资源持有共享锁，但只有一个进程可以对资源持有更新锁)，只要一个资源上被加了更新锁，其它进程就无法再获取该资源的更新锁或独占锁。

更新锁并不只是用于update操作，SqlServer对任何需要在进行实际修改之前先搜索数据的数据修改操作都是用更新锁，这样的操作包括受限更新和删除，也包括在带有聚集索引的表上进行的插入操作。当SqlServer只进行到插入阶段时，它采用更新锁来保护数据，而只有当它找到正确的位置并开始插入以后才将更新锁转换成独占锁。

排它锁（X）：当数据被INSERT、UPDATE、或DELETE操作修改时，SqlServer自动获得数据上的独占锁，在一个特定的数据源上，同一时刻只有一个进程可以获得独占锁。
意向锁：意向锁并不是一种独立的锁模式，它们是之前讨论过的那些模式的限定词。换言之，可以拥有意向共享锁、意向独占锁、意向更新锁。由于SqlServer可以在不同级别的粒度上获取锁，因此需要一种机制来指出一个资源上的组件已经被锁定了。比如一个进程试图获取一张表，SqlServer就需要采取一种方式来确定这张表上的一行是否已经被锁定了。
An intent lock indicates that SQL Server wants to acquire a shared (S) lock or exclusive (X) lock on some of the resources lower down in the hierarchy. For example, a shared intent lock placed at the table level means that a transaction intends on placing shared (S) locks on pages or rows within that table. Setting an intent lock at the table level prevents another transaction from subsequently acquiring an exclusive (X) lock on the table containing that page. Intent locks improve performance because SQL Server examines intent locks only at the table level to determine if a transaction can safely acquire a lock on that table. This removes the requirement to examine every row or page lock on the table to determine if a transaction can lock the entire table。 意向锁的类型为：意向共享（IS）、意向排它（IX）以及意向排它共享（SIX）

事务隔离级别与锁的关系

程序员所做的事主要是设定事务的隔离级别，至于在什么时候加什么锁，加什么样的锁，所加的锁在什么时候释放，则由DBMS根据设定的事务隔离级别结合具体的SQL语句自动进行。

 

一般情况下，读语句(select)发出共享锁，写语句(update,insert,delete)发出排他锁。

 

Read Committed 隔离级别下读操作不获得任何锁，直接读之，因而不会影响其它正在读取或写入数据的进程。Read Committed隔离级别只是忽略锁，并不提供数据一致性

 

Sqlserver默认的隔离级别是 Read Committed, SqlServer的Read Committed级别的事务隔离有两个变种：乐观的和悲观的Read Committed，具体取决于数据库设置READ_COMMITTED_SNAPSHOP，由于数据库选项的默认值是关闭的，所以该隔离级别的默认值使用悲观并发控制。Read Committed级别的事务隔离确保该事务本身从来不会读取另一个事务已经更改但是还未提交的数据。

 

Repeatable Read确保在一个事务内多次读取数据库所得到结果是一样的(即本事务执行期间别的事务不可以对本事务已读过的数据进行update，但别的事务仍然可以插入新的数据，所以Repeatable Read级别的事务隔离仍可能出现幻读问题)。为了保证Repeatable Read级别的事务隔离，事务中所有的共享锁必须一直持有到事务结束。

 

由上述可以看出，共享锁的锁定时间与事务的隔离级别有关，如果隔离级别为Read Committed，只在执行读取(select)的期间保持锁定，即在查询出数据以后就释放了锁；如果隔离级别为更高的Repeatable read或Serializable，则直到事务结束才释放锁。而独占锁总是要到事务结束时才释放，只有这样才能保证事务在必要时可以执行回滚操作。另说明，如果select语句中指定了HoldLock提示，则其共享锁也要等到事务结束才释放锁。

 

SqlServer中设定事务的隔离级别

一：DBCC Useroptions -> isolation level 这一项的 Value 既是当前的设置值。

这种方式设置的作用域是当前链接，也就是，你查看的是当前链接的级别，但是sql server 同时 150+ 个链接是很正常的，所以这个设置方法不够强大。

二：亦可在ADO.NET中设置

System.Data.SqlClient.SqlConnection con = new SqlConnection();
con.BeginTransaction(IsolationLevel.ReadUncommitted);

三：在sql语句中设置

打开查询分析器并打开两个连接，分别输入如下两个事务：

--事务Ⅰ

SET TRANSACTION ISOLATION LEVEL READ Committed

begin tran

update A set A2 = 20 where A1 = 11

waitfor delay '00:00:10'

rollback tran

--事务Ⅱ

SET TRANSACTION ISOLATION LEVEL READ Committed

select * from A where A1 = 11

如果先运行事务Ⅰ，然后紧接着运行事务Ⅱ，则事务Ⅱ要等待10秒钟（一个连接在修改数据块时别的连接也不能查询这个数据块，直到解锁。反之亦然：读的时候不能写和修改）。

如果把事务Ⅱ改为如下

SET TRANSACTION ISOLATION LEVEL READ UNCommitted

select * from A where A1 = 11

那么事务Ⅱ不需等待，立即执行（可以看出READ UNCommitted事务select不对数据发出共享锁）

选择事务隔离级别不影响为保护数据修改而获取的锁。事务总是在其修改的任何数据上获取排他锁并在事务完成之前持有该锁

最后，来一个示例练练手吧：《数据库并发一致性案例分析》