Sql Server 事务锁死

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) 事务隔离级别 http://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