SQL Server（十）—— 事务和锁

一、事务的特性

1、原子性：事务必须是原子性的工作单元，要么全部执行，要么全部不执行。
2、一致性：事务完成时，必须使所有数据保持一致性。
3、隔离性：由并发事务所做的修改必须与其他并发事务所做的修改隔离。由于事务在开始时就会识别数据所处的状态，以便发生错误时可以回滚操作，所以另一个并发事务要么修改它之前的状态，要么修改它之后的状态，不能在该事务运行时修改它的状态。这也被称为可串行性。
4、持久性：在事务完成后，其操作结果对系统的影响应该是永久的，只要事务成功，就不能再次回滚。

二、创建和使用事务

BEGIN TRAN -- 开始事务
PRINT '执行语句 1'
IF @@ERROR > 0
	GOTO TranRollback

PRINT '执行语句 2'	
IF @@ERROR > 0
	GOTO TranRollback


IF @@ERROR > 0 OR @@ROWCOUNT<>1
BEGIN
	TranRollback:
	ROLLBACK TRAN -- 如果有错误则回滚事务
END
ELSE 
	COMMIT TRAN -- 如果没有错误则提交事务
-- 建议使用 TRY..CATCH 语句

三、执行事务的注意事项：

1、每个操作之后都要检查一下@@ERROR和@@ROWCOUNT的值。
　　2、当一个事务结束之后，紧跟之后的T-SQL 代码还可以继续执行，但是出错后不能回滚。
　　3、已结提交的事务会将数据正式写入数据库，此时不能再回滚事务。
　　4、当事务执行时付过发生断电等意外，则下次重启系统时，该事务会自动回滚。
　　5、事务里发送错误使事务无法执行下去时事务也会回滚。
　　6、禁止在事务中使用的语句：CREATE DATABASE、ALTER DATABASE、DROP DATABASE、LOAD DATABASE、RESTORE DATABASE、BACKUP LOG、RESTORE LOG、LOAD TRANSACTION、DUMP TRANSACTION、DISK INIT、RECONFIGURE、UPDATE STATISTICS

四、事务的执行模式

1、显示事务：明确使用“BEGIN TRAN”语句开始，以“COMMIT TRAN”或“ROLLBACK TRAN”语句结束的事务。
　　2、自动提交事务。自动提交事务是SQL Server Database Engine 的默认事务方式，每一个 T-SQL 语句都可以看成是一个自动提交的事务。会自动提交事务的语句有：ALTER TABLE、TRUNCATE TABLE、CREATE TABLE、SELECT、INSERT、UPDATE、DELETE、DROP、OPEN、FETCH、REVOKE、GRANT。
　　3、隐式事务：当执行“SET IMPLICT_TRANSACTIONS ON”语句后SQL Server进入隐式事务模式。隐式事务产生了一个连续的事务链，当事务在提交或回滚后系统自动启动新的事务，不需再次定义事务的开始。

五、事务的隔离级别

1、Read Uncommitted：不隔离数据，该事务执行时允许其他事务同时修改或删除数据。
　　2、Read Committed：不允许读取没有提交的数据，SQL Server默认的隔离级别。
　　3、Repeatable Read：在事务中锁定所读取的数据不让其他程序修改和删除，如此可以保证每次读取到的数据是一致的。其他事务可以往该表新增数据。
　　4、Snapshot：快照隔离，可以为读取数据的事务提供所需数据的一个已提交版本，因为写入数据的事务不会阻塞读取事务的事务。
　　5、Serialzable：将事务用的数据表全部锁定。该等级并发性最低。
Read Uncommitted与Read Committed属于乐观并发控制，其他属于悲观并发控制。

SET TRANSCTION ISLATION LEVEL 
	READ COMMITTED		　

六、锁

锁的作用是将数据临时锁定只提供给一个进程或程序使用，并防止其他进程或程序修改或读取。锁对象有：
　　RID：以记录为锁定单位
　　KEY：以设置为索引的字段为锁定单位
　　PAGE：以数据中的数据页或索引页作为锁定单位
　　EXTENT：以一组连续的八页作为锁定的单位
　　HOBT：以堆或B-tree 作为锁定的单位。保护索引或没有聚集索引的表中的数据页堆的锁。
　　TABLE：以整个数据表作为锁定单位
　　FILE：以数据库文件作为锁定单位
　　APPLICATION：以元数据作为锁定单位
　　ALLOCATION_UNIT：以分配单元作为锁定单位
　　DATABASE：以整个数据库作为锁定单位
SQL Server 的锁定模式：
　　共享锁：如果事务T1获得了数据项R上的共享锁，则T1对数据项R可读不可写；其他事务对数据项R的排他锁请求都不会成功，对数据R的共享锁请求可以成功。
　　更新锁(U锁)：当一个事务查询数据以进行修改时，可以对数据添加更新锁，如果事务修改了资源，则更新锁会转换为排他锁，否则会转换为共享锁；常用于更新数据中，防止多个事务在读取、锁定以及随后可能进行的数据更改时发送死锁
　　排他锁：如果事务T1获得数据项R上的排他锁，则T1对数据项R可读可写（仅在使用NOLOCK提示或未提交读隔离级别才会读取操作）；其他事务对数据项R的任何锁请求都不会成功，需等待事务T1释放数据项R上的排他锁。
　　意向锁：数据引擎使用意向锁来保护共享锁或排他锁放置在锁层级较低的资源上，意向锁可以提高性能。意向锁模式包括：意向共享、意向排他、意向排他共享、意向更新、共享意向更新、更新意向排他等模式。
　　架构锁：通常在执行依赖于表架构（DDL）的操作时使用
　　大容量更新锁(BU锁)：通常在向表进行大容量数据复制且指定 tablock 提示时使用。
　　键范围锁：使用 serializable 事务隔离级别时包含查询读取的行的范围，确保再次运行查询时其他事务无法插入复合该 serializable 事务查询的行。

七、死锁(待续)

DBCC TRACEON
DBCC TRACESTATUS
SET DEADLOCK_PRIORITY {LOW | NORMAL | HIGH} #设置死锁优先级