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来捕捉锁信息
死锁形成的条件:
互斥条件
请求和等待条件
不剥夺条件
环路等待条件
预防死锁:
破坏互斥条件
破坏请求和等待条件
破坏不剥夺条件