SQL Server事务和锁

一、事务

       事务就是将一些列事情看成一个整体，这些事情要么一起完成，要么就不完成。在SQL Server中的每一条单独的SELECT、UPDATE、INSERT和DELETE语句都是一个隐式的事务。可以设置一系列语句当成事务的SQL 语句如下：

• BEGIN TRAN:设置事务的开始点；当事务回滚时，也是事务回滚点。
• COMMIT TRAN:使事务成为数据库永久的、不可逆转的一部分。
• ROLLBACK TRAN:回滚，也就是说撤销事务前面的操作。
• SAVE TRAN:创建一个特定标记符，只允许作部分回滚。

1.BEGIN TRAN

      表示事务的开始；也作为事务回滚点。其语法结构如下：

2.COMMIT TRAN

     设置该命令，会认为事务的持久的。该事务不会受系统故障影响。撤销事务的唯一方法是发出一个新的事务。其语法结构如下：

3.ROLLBACK TRAN

     回滚事务，事务已经执行的部分会被忘记。也可以回滚到保存点，保存点有SAVE TRAN创建。其语法结构如下：

4.SAVE TRAN

     创建书签，可以回滚到书签位置。但当调用了ROLLBACK后，所有的保存点就会消失，再想用保存点，只有重建。其语法结构如下：

 二、日志

 

        对于数据库的大部分操作，都不是直接将修改后的数据写入数据库中，而是先将数据保存到事务日志中。当发出检查点时，会将所有脏页写入到磁盘。脏页是指一些读入到缓存后已经被修改，但是所进行的修改还没有写入磁盘的日志或数据页。

        发出检查点的几个原因：

        1）向已满的缓存中读入数据

        2）通过一条手动的语句------使用CHECKPOINT命令

        3）在生产关闭服务器时，但使用EITH NOWAIT除外。

        4）当修改数据库的任何选项时

        5）当使用Simple Recovery选项并且日志超过70%填满的时候

        6）当从最后一个检查点开始的日志中的数据总量超过服务器再recovery interval选项指定的时间里可恢复的数据总量时。

1.恢复

        恢复总是在SQL Server启动时发生。SQL Server获取数据库文件，并且应用来自最后一个检查点以后日志中任何提交的改变。

事务日志文件LDF被划分为多个逻辑上的虚拟日志文件VLF。

日志序列号LSN。

事务日志记录的操作包括：每个事务的开始和结束；每个数据的修改；每次分配或释放的区和页；创建或删除表或索引。

一篇很好介绍事务日志的文章

        SQL Server提供了一个缓冲区，缓冲区中包括日志内容和数据内容。SQL Server使用预写式日志WAL技术来确保事务的原子性和持久性。WAL技术的核心思想是：在将数据写入数据库之前，先写入到日志。

        SQL Server对于数据的修改,会分为以下几个步骤顺序执行:

• 1.在SQL Server的缓冲区的日志中写入”Begin Tran”记录
• 2.在SQL Server的缓冲区的日志页写入要修改的信息
• 3.在SQL Server的缓冲区将要修改的数据写入数据页
• 4.在SQL Server的缓冲区的日志中写入”Commit”记录
• 5.将缓冲区的日志写入日志文件
• 6.发送确认信息(ACK)到客户端(SMSS,ODBC等）

       只有当缓冲区遇到COMMIT时，才将日志页写入当磁盘的日志文件中。而将修改的数据页（即脏页）写入磁盘数据库文件的两个过程：1）.CheckPoint；2）.Lazy Writer。CheckPoint或Lazy Writer通过将日志文件末尾的LSN号和缓冲区中数据文件的LSN进行对比，只有缓冲区内LSN号小于日志文件末尾的LSN号的数据才会被写入到磁盘中的数据库。因此确保了WAL。

 

三、并发和锁

锁是一种为了防止某对象执行某个动作的一个进程与已在该对象上执行的其他进程相冲突的机制。锁可以解决下面4个问题：

       1）脏读：一个事务读取的是另一个事务未提交的一部分。

       2）非重复性读取：如果一个事务两次读取记录，而在着期间另一个事务改变了记录。

       3）幻读：是指事务A读取与搜索条件相匹配的行。事务B以插入和删除的方式来修改事务A的结果集，然后提交。造成事务A的结果集发生变化。

       4）丢失更新：丢失更新是指两个事务同时获得了记录，而事务A对记录进行了更新并提交给数据库，而这时事务B也要更新记录，这时就造成了数据丢失。

1.锁的层次

锁的层次越高它的粒度就越粗。在SQL Server中，由粗到细的6个锁层次：数据库，表，区段，页，键，行。

2.锁定模式

       1）共享锁：共享锁用于只读取数据的时候，即不修改数据。

       2）排它锁：不能和任何锁兼容。

       3）更新锁：是共享锁和排它锁的兼容。

       4）意向锁：用于处理对象层次问题。其可分为：意向共享锁、意向排它锁和共享意向排它锁。

       5）模式锁：分为模式修改锁和模式稳定性锁定。

       6）批量更新锁：允许并行加载数据。

兼容性如下表：

 3.隔离级别

        默认情况下，一旦建立了任何与数据修改相关的锁，这个锁就存在于整个事务期间。这只是默认情况下，因此，SQL Server可以有下面5个隔离级别进行设置：READ COMMITTED、READ UNCOMMITTED、REPEATABLE READ、SERIALIZABLE、SNAPHOST。对隔离级别的修改只会影响当当前连接，不会影响到其他用户。设置语法如下：

1）READ COMMITTED

 

2）READ UNCOMMITTED

 

3）REPEATABLE READ

 

4）SERIALIZABLE

 

5）ANAPHOST