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