SqlServer常见的三种事务操作及事务锁死讲解
sql server 常见的三种事务
1.自动提交事务
是sql server 默认的一种事务，每一个sql语句都会被看作一个事务来处理。
2.显示事务
t-sql标明由begin transaction 来开始事务，由commmit transaction来提交事务，rollback transaction 来回滚事务
3.隐试事务
使用set implicit_transactions on 将将隐式事务模式打开，不用begin transaction开启事务，当一个事务结束，这个模式会自动启用下一个事务，只用commit transaction 提交事务、rollback transaction 回滚事务即可。
1) 显试事务的常见操作
1.begin transaction
2.commit transaction
3.rollback transaction
4.save transaction
--测试回滚到保存事务节点的地方
begin tran
begin try
insert into t_his_purchasedetail values (n'1804180000001', n'030100172', '50', n'030116112000001', '2018-04-18 09:29:53.983',
n'71e0cdcf-9ed7-7adf-25f2-18bd9bedeb83', n'0301001039', '1', n'1804180000001', n'00011', '0');
save tran yigjn
insert into t_his_purchasedetail values (n'1804180000002', n'030100172', '50', n'030116112000001', '2018-04-18 09:29:53.983',
n'71e0cdcf-9ed7-7adf-25f2-18bd9bedeb83', n'0301001039', '1', n'1804180000001', n'00011', '3');
insert into t_his_purchasedetail values (n'1804180000003', n'030100172', '50', n'030116112000001', '2018-04-18 09:29:53.983',
n'71e0cdcf-9ed7-7adf-25f2-18bd9bedeb83', n'0301001039', '1', n'1804180000001', n'00011', '0');
end try
begin catch
select error_number(),
error_severity(),
error_state(),
error_procedure(),
error_line(),
error_message()
print(@@trancount);
if (@@trancount > 0)
begin
print('yigjn catch')
rollback tran
end
end catch
if (@@trancount > 0)
begin
print('yigjn end')
rollback tran yigjn
select * from t_his_purchasedetail
end
2) set xact_abort
设置xact_abort on/off 指定是否回滚当前事务 on时如果当前sql出错则回滚整个事务，off时如果sql出错则回滚当前sql语句,其他语句照常写入
~~~~~~~~~需要注意 xact_abort 只对运行时异常有效，如果编译错误则不会启用
-- 类型转换错误 会使xact_abort 失效， 溢出整数列可以进行正确回滚
--测试 xact_abort
truncate table t_his_purchasedetail
set xact_abort off
begin tran
insert into t_his_purchasedetail values (n'1804180000001', n'030100172', '50', n'030116112000001', '2018-04-18 09:29:53.983',
n'71e0cdcf-9ed7-7adf-25f2-18bd9bedeb83', n'0301001039', '1', n'1804180000001', n'00011', '0');
save tran yigjn
insert into t_his_purchasedetail values (n'1804180000002', n'030100172', '50', n'030116112000001', '2018-04-18 09:29:53.983',
n'71e0cdcf-9ed7-7adf-25f2-18bd9bedeb83', n'0301001039', '1', n'1804180000001', n'00011', 'asd');
insert into t_his_purchasedetail values (n'1804180000003', n'030100172', '50', n'030116112000001', '2018-04-18 09:29:53.983',
n'71e0cdcf-9ed7-7adf-25f2-18bd9bedeb83', n'0301001039', '1', n'1804180000001', n'00011', '0');
commit tran
select * from t_his_purchasedetail
3) 死锁
-- 分别将两个代码块放入两个查询界面执行
-- 测试事务进程死锁
begin tran
update t_his_purchasedetail set num = '1' --where orderno = '1804180000001'
waitfor delay '00:00:10'
update t_dept set upflag = '1'-- where fid = '0301014'
commit tran
begin tran
update t_dept set upflag = '1' --where fid = '0301'
waitfor delay '00:00:10'
update t_his_purchasedetail set num = '1'-- where orderno = '1804180000003'
commit tran
~~~~~~~~~~~~~~~~~~测试出来锁的粒度为页级锁
4) 锁的分类
从数据库角度来分分为三种： 1. 排他锁(x) 2.共享锁(s) 3.更新锁(u)
5) 死锁的处理
-- 查询所占用
select l.request_session_id,
db_name(l.resource_database_id),object_name(p.object_id),
l.resource_description,l.request_type,
l.request_status,request_mode
from sys.dm_tran_locks as l
left join sys.partitions as p
on l.resource_associated_entity_id=p.hobt_id
sql server 自动侦测何时死锁发生，sql server 中有一个独立进程叫做 lock_monitor,大约五秒钟检查一次是否存在死锁
6) 事务隔离级别 https://blog.itpub.net/13651903/viewspace-1082730/
修改事务隔离级别
set transaction isolation level
repeatable read
go
1.read uncommitted
造成的问题 ： 脏读 可以读到其他事务未提交的操作 , 幻读， 不可重复度
2.read committed
解决的问题：脏读 ，操作的时候加上了x锁其他事务不可读该数据 问题：幻读 ， 不可重读(另一个事务仍可以修改该事务读取的数据)
3.repeatable(重复) read
解决问题 ： 不可重复度 (另一个事务无法修改删除该事务读取的数据，只能等该事物结束之后)
3.serializable
解决问题：幻读 (另一个事务无法insert一个事务正在查询的表)
7) 锁粒度
1.database
每个数据库的查询连接都会给数据库加一个s锁
2.d:\program files\sql server2008\mssql10_50.mssqlserver\mssql\backup
--查看锁占用情况
select resource_type,resource_description,request_mode,request_status,request_type,request_lifetime , request_session_id, db_name(resource_database_id)
,resource_associated_entity_id,p.*
--, object_name(resource_associated_entity_id)
from sys.dm_tran_locks l left join sys.partitions p on l.resource_associated_entity_id = p.hobt_id
where resource_database_id=db_id() and request_session_id=@@spid