事务和锁

事务管理

事务是用户定义的一个数据库操作序列，这些操作要么做要么全不做，是一个不可分割的工作单位。

主要分为自动提交事务、隐式事务、显式事务和分布式事务。

类型	含义
自动提交事务	每条单独语句都是一个事务
隐式事务	前一个事务完成时新事务隐式启动，每个事务仍以COMMIT或ROLLBACK语句显示结束
显式事务	每个事务均以BEGIN TRNSACTION语句显示开始，以COMMIT或ROLLBACK语句显示结束
分布式事务	跨越多个服务器的事务

事务的属性事务的属性：原子性、一致性、隔离性、持久性，简称ACID属性。

事务管理的常用语句

• BEGIN TRANSACTION:建立一个事务。
• COMMIT TRANSACTION:提交事务。
• ROLLBACK TRANSACTION:事务失败时执行回滚操作。
• SAVE TRANSACTION:保存事务。

事务的隔离级别

事务具有隔离性，在同一时间可以有很多事务正在处理数据，但是每个数据在同一时刻只能有一个事务进行操作。如果锁定可能会造成死锁。
为提高数据并发使用效率，可以为事务在读取数据时设置隔离状态，有5种级别。

• READ UNCOMMITTED 级别：该级别不隔离数据，即使事务正在使用数据，其他事务也能同时修改或删除该数据。
• READ COMMITTED 级别：指定语句不能读取已由其他事务修改，但尚未提交的数据。可以避免脏读。
• REPEATABLE READ 级别：指定语句不能读取已由其他事务修改但尚未提交的行，并且其他事务都不能在当前事务完成之前修改当前事务读取的数据。
• SNAPSHOT 级别：指定事务中任何语句读取的数据都将是在事务开始时便存在的数据事务上抑制的版本。
• SERIALIZABLE 级别：将事务所要用到的时间全部锁定，不容许其他事务操作，该级别并发性最低，要读取同一数据的事务就必须排队等待。

事务应用案例

• 【例1】限定stu_info表中最多只能插入10条学生记录，如果表中插入人数大于10人，插入失败

USE newdb
GO
BEGIN TRANSACTION 
INSERT INTO stu_info VALUES('路飞',80,'男',18);
INSERT INTO stu_info VALUES('张露',85,'女',18);
INSERT INTO stu_info VALUES('魏波',70,'男',19);
INSERT INTO stu_info VALUES('李婷',74,'女',18);
DECLARE @studentCount INT
SELECT @studentCount = (SELECT COUNT(*) FROM stu_info)
IF @studentCount>10
    BEGIN
    ROLLBACK TRANSACTION 
    PRINT '插入人数太多，失败'
    END
ELSE
    BEGIN
    COMMIT TRANSACTION
    PRINT '插入成功'
    END

锁

锁的作用

数据库中数据的并发操作经常发生，而并发操作会产生一些问题：脏读、幻读、非重复性读取、丢失更新。

1. 脏读：当一个事务读取的记录是另一个事务的一部分是，如果第一个事务正常完成，就灭问题，但如果此时另一个事务未完成，就产生了脏读。
2. 幻读：当某一数据行执行INSERT或DELETE操作，而该数据行恰好属于某个事务正在读取的范围时，就会发生幻读现象。
3. 非重复性读取：如果一个事务不止一次地读取相同的记录，但在两次读取中间有另一个事务工号修改了数据，则两次读取的数据将出现差异，此时就发生了非重复性读取。
4. 丢失更新：一个事务更新了数据库之后，另一个事务再次对数据库更新，此时系统只能保留最后一个数据的修改。

可锁定的资源与锁类型

可锁定的资源：数据库，表，区段页，页，键，行。

锁类型：更新锁，排它锁，共享锁，键范围锁，架构锁。

死锁

在两个或多个任务中，如果每个任务锁定了其他任务试图锁定的资源，会造成这些任务永久阻塞，从而出现死锁。

锁的应用案例

• 锁定行
  【例2】锁定stu_info表中s_id=2的学生记录

SET TRANSACTION ISOLATION LEVEL READ UNCOMMITTED
SELECT * FROM stu_info ROWLOCK WHERE s_id = 2;

• 锁定数据表
  【例3】锁定stu_info表中记录

SELECT s_age FROM stu_info TABLELOCKX WHERE s_age=26;

• 排他锁
  【例4】创建名称为transaction1和transaction2的事务，在transaction1事务上面添加排他锁，事务1执行10秒钟之后才能执行transaction2事务

SELECT s_age FROM stu_info TABLELOCKX WHERE s_age=26;

• 共享锁
  【例5】创建名称为transaction1和transaction2的事务，在transaction1事务上面添加共享锁，允许两个事务同时执行查询操作，如果第二个事务要执行更新操作，必须等待10秒钟

BEGIN TRAN transaction1
SELECT  s_score,s_sex,s_age FROM stu_info WITH(HOLDLOCK) WHERE s_name='张三';
WAITFOR DELAY '00:00:10';
COMMIT TRAN
BEGIN TRAN transaction2
SELECT * FROM stu_info  WHERE s_name='张三';
--UPDATE stu_info SET s_score=90  WHERE s_name='张三' ;
COMMIT TRAN

问题

1.事务和锁在应用上的区别是什么？

• 事务将段SOL语句作为一个单元来处理，这些操作要么全部成功，要么全部失败。
• 事务包含4个特性:原子性、致性、 隔离性和持久性。
• 事务的执行方式分为自动提交事务、显示事务、隐式事务和分布式事务。
• 事务以“BEGIN TRAN"语句开始，并以“COMMIT TRAN"或ROLLBACKTRAN”语句结束。
• 锁是另一个和事务紧密联系的概念，对于多用户系统，使用锁来保护指定的资源。在事务中使用锁，防止其他用户修改另外-一个事务中还没有完成的事务中的数据。
• SQL Server中有多种类型的锁，允许事务锁定不同的资源。

2.事务和锁有什么关系？

事务包含一系列的操作，通过事务机制管理多个事务，保证一致性，事务中使用锁保护指定资源，防止其他用户修改另外一个还没有完成的事务中的数据。