【SQL】- 基础知识梳理（八） - 事务与锁

--错误捕捉 begin try --语句正确 insert into table1 (id,name,value,sex) values (4,‘michael2‘,‘chaoshuai2‘,1); --加入保存点 -- save tran pigOneIn --sex为int型 出错 insert into table1 (id,name,value,sex) values (5,‘michael3‘,‘chaoshuai3‘,‘天气下雨了‘); insert into table1 (id,name,value,sex) values (6,‘michael4‘,‘chaoshuai4‘,1); end try begin catch select Error_number() as ErrorNumber, --错误代码 Error_severity() as ErrorSeverity, --错误严重级别，级别小于10 try catch 捕获不到 Error_state() as ErrorState , --错误状态码 Error_Procedure() as ErrorProcedure , --出现错误的存储过程或触发器的名称。 Error_line() as ErrorLine, --发生错误的行号 Error_message() as ErrorMessage --错误的具体信息 if(@@trancount>0) --全局变量@@trancount，事务开启此值+1，他用来判断是有开启事务 rollback tran tran_Addtable1 ---由于出错，这里回滚事务到原点，第一条语句也没有插入成功。 end catch if(@@TRANCOUNT>0) commit tran tran_Addtable1 --提交事务

执行结果

分析：由于插入table1时发生错误，根据事务的原子性，要么全做，要全不错，所以一条数据都没有插入

事务的并发控制

在多用户都用事务同时访问同一个数据资源的情况下，就会造成以下几种数据错误
1.更新丢失：多个用户同时对一个数据资源进行更新，必定会产生被覆盖的数据，造成数据读写异常。
2.不可重复读：如果一个用户在一个事务中多次读取一条数据，而另外一个用户则同时更新啦这条数据，造成第一个用户多次读取数据不一致。
3.脏读：第一个事务读取第二个事务正在更新的数据表，如果第二个事务还没有更新完成，那么第一个事务读取的数据将是一半为更新过的，一半还没更新过的数据，这样的数据毫无意义。
4.幻读：第一个事务读取一个结果集后，第二个事务，对这个结果集经行增删操作，然而第一个事务中再次对这个结果集进行查询时，数据发现丢失或新增。

设置事务隔离级别

read uncommitted：这个隔离级别最低啦，可以读取到一个事务正在处理的数据，但事务还未提交，这种级别的读取叫做脏读。
read committed：这个级别是默认选项，不能脏读，不能读取事务正在处理没有提交的数据，但能修改。
repeatable read：不能读取事务正在处理的数据，也不能修改事务处理数据前的数据。
snapshot：指定事务在开始的时候，就获得了已经提交数据的快照，因此当前事务只能看到事务开始之前对数据所做的修改。
serializable：最高事务隔离级别，只能看到事务处理之前的数据。

 

锁的概念

Microsoft SQL Server 数据库引擎使用不同的锁模式锁定资源，这些锁模式确定了并发事务访问资源的方式。

锁的分类

• 共享锁：允许并发事务在封闭式并发控制下读取（SELECT）资源。资源上存在共享锁（S 锁）时，任何其他事务都不能修改数据。 读取操作一完成，就立即释放资源上的共享锁（S 锁）；
• 排他锁：可以防止并发事务对资源进行访问。使用排他锁时，任何其他事务都无法修改数据；数据修改语句（如 INSERT、UPDATE 和 DELETE）合并了修改和读取操作，通常请求共享锁和排他锁
• 更新锁：防止常见的死锁。此事务读取数据 [获取资源（页或行）的共享锁（S 锁）]，然后修改数据 [此操作要求锁转换为排他锁（X 锁）]。果两个事务获得了资源上的共享模式锁，然后试图同时更新数据，则一个事务尝试将锁转换为排他锁（X 锁）。 共享模式到排他锁的转换必须等待一段时间，因为一个事务的排他锁与其他事务的共享模式锁不兼容；发生锁等待。 第二个事务试图获取排他锁（X 锁）以进行更新。 由于两个事务都要转换为排他锁（X 锁），并且每个事务都等待另一个事务释放共享模式锁，因此发生死锁。

　　   更新锁（U 锁）使得一次只有一个事务可以获得资源的更新锁（U 锁）。 如果事务修改资源，则更新锁（U 锁）转换为排他锁（X 锁）

• 意向锁：数据库引擎使用意向锁来保护共享锁（S 锁）或排他锁（X 锁）放置在锁层次结构的底层资源上。在较低级别锁前可获取它们，因此会通知意向将锁放置在较低级别上。

　　  例如，在该表的页或行上请求共享锁（S 锁）之前，在表级请求共享意向锁。 在表级设置意向锁可防止另一个事务随后在包含那一页的表上获取排他锁（X 锁）。 意向锁可以提高性能，因为数据库引擎仅在表检　          查意向锁来确定事务是否可以安全地获取该表上的锁。 而不需要检查表中的每行或每页上的锁以确定事务是否可以锁定整个表。

• 意向锁包括意向共享 (IS)、意向排他 (IX) 以及意向排他共享 (SIX)。
• 架构锁：数据库引擎在表数据定义语言 (DDL) 操作（例如添加列或删除表）的过程中使用架构修改 (Sch-M) 锁。 保持该锁期间，Sch-M 锁将阻止对表进行并发访问。
• 大容量更新锁：  大容量更新锁（BU 锁）允许多个线程将数据并发地大容量加载到同一表，同时防止其他不进行大容量加载数据的进程访问该表。

锁模式兼容性

如何将死锁降低到最低

按同一顺序访问对象。
避免事务中的用户交互。
保持事务简短并处于一个批处理中。
使用较低的隔离级别。
使用基于行版本控制的隔离级别。
将 READ_COMMITTED_SNAPSHOT 数据库选项设置为 ON，使得已提交读事务使用行版本控制。
使用快照隔离。
使用绑定连接。

 

【SQL】- 基础知识梳理（八） - 事务与锁

标签：触发器   常用   分割   insert   标记   add   共享模式   用户交互   全局变量