2、T-SQL查询语句 详解事务及锁

                                           设计者：杨文

                                           防伪码：千淘万浪虽辛苦，吹尽黄沙始到金

SQL Server 通过支持事务机制以保证数据的一致性。

案例：在日常生活中，大家可能使用过银行转账，银行转账操作往往会涉及两个或两个以上的账户。在转出账户的存款减少一定金额的同时，转入账户则会增加相应金额的存款，现在，假定张三的账户转账1000元到李四的账户。该如何实现呢？

1、首先，需要创建账户表bank，存放用户张三和李四的账户信息。我们假设表结构如图

2、账户表中要求账户余额不能少于一元，所以需要为字段currentMoney设置检查约束，设置步骤如下。

首先，打开bank表的设计窗口，在currentMoney字段上右击，在弹出的快捷菜单中选择CHECK约束即可，

如图表示：

3、然后，在打开的“CHECK 约束”对话框中，单机“添加”按钮，修改约束名称，并编辑检查约束表达式即可，

如图

4、如何解决呢？使用事务，转账过程就是一个事务，它需要两条UPDATE语句来完成，这两条语句是一个整体。

如果其中任何一条出现错误，则整个转账业务也应取消，两个账户中的余额应恢复到原来的数据，从而确保转账

前和转账后的余额总和不变，即1001元。

5、理论：

a、什么是事务？

概念

事务是一个不可分割的工作逻辑单元，包括一组命令，这组命令要么都执行，要么都不执行。

b、属性

原子性：事务是一个完整的操作。事务的各元素是不可分的。

一致性：当事务完成时，数据必须处于一致状态。

隔离性：事务是独立的，它不以任何方式依赖于或影响其他事务。

持久性：一旦事务被提交，事务的效果会被永久的保留在数据库中。

6、如何执行事务

a、执行事务的语法

开始事务：BEGIN TRANSACTION

提交事务：COMMIT TRANSACTION

回滚（撤销）事务：ROLLBACK TRANSACTION

7、事务应用实例

下面我们来应用事务来解决上述转账问题，T=SQL语句如下。

print '查看转账事务前的余额'

select * from bank 

go

begin transaction

declare @errorsum int

set @errorsum =0

update bank set customermoney =customermoney -1000

where customername ='张三'

set @errorsum =@errorsum +@@ERROR 

update bank set customermoney =customermoney +1000

where customername ='李四'

set @errorsum =@errorsum +@@ERROR 

print '查看转账事务过程中的余额'

select * from bank 

if @errorsum <>0

begin

print '交易失败,回滚事物'

rollback transaction

end

else

begin

print '交易成功,提交事务,写入键盘,永久保存'

commit transaction 

end 

go

print '查看转账事务后的余额'

select * from bank 

go 

交易失败：

交易成功：

8、

认识锁

什么是锁

用于多用户环境下保证数据库完整性和一致性。

锁的模式

共享锁（S锁）

用于读取数据。拥有共享锁的资源不能被修改。

排他锁（X锁）

用于数据修改。与其他任何锁都不兼容。

更新锁（U锁）

用于读取和修改。与S锁兼容，不影响读取；与U锁不兼容，避免死锁

如何查看锁

使用sys.dm_tran_locks动态管理视图

使用Profiler来捕捉锁信息

9、认识死锁          

死锁形成的条件：

互斥条件

请求和等待条件

不剥夺条件

环路等待条件

预防死锁：

破坏互斥条件

破坏请求和等待条件

破坏不剥夺条件