SQL Server 锁

1. update锁，并不是update语句引起的，当SQL Server 执行一个数据修改操作，但是需要首先执行一个检索来查找需要修改的资源时，事务会获取这种类型的锁。

2. 如果有多个进程都有某资源上的shared锁，那么其中一个想要转exclusive锁是不能成功的；

3. UPDATE 锁与SHARED 锁兼容，但与EXCLUSIVE 锁或其他UPDATE 锁不兼容。

     SELECT request_session_id AS session_id, DB_NAME(resource_database_id) AS [database],
          request_mode AS mode, resource_type as [type], 
          resource_associated_entity_id AS entity,
          resource_description,  request_status AS status
   FROM sys.dm_tran_locks; // 返回有关当前活动的锁管理器资源的信息。request_mode='U'的代表update锁

 

 

1. 使用Try Catch语句避免死锁：


BEGIN TRANSACTION

BEGIN TRY

  INSERT Authors VALUES 

  (@au_id, @au_lname, '', '', '', '', '', '11111', 0)

  WAITFOR DELAY '00:00:05'

  SELECT COUNT(*)  FROM Authors 

  COMMIT

END TRY

BEGIN CATCH

  SELECT ERROR_NUMBER() AS ErrorNumber

  ROLLBACK

END CATCH;

SELECT @@TRANCOUNT AS '@@Trancount'

2. 使用Try Catch语句进行重试：

DECLARE @Tries tinyint

SET @Tries = 1

WHILE @Tries <= 3

BEGIN

  BEGIN TRANSACTION

  BEGIN TRY

    INSERT Authors VALUES  (@au_id, @au_lname, '', '', '', '', '', '11111', 0)

    WAITFOR DELAY '00:00:05'

    SELECT * FROM authors WHERE au_lname LIKE 'Test%'

    COMMIT

    BREAK

  END TRY

  BEGIN CATCH

    SELECT ERROR_NUMBER() AS ErrorNumber

    ROLLBACK

    SET @Tries = @Tries + 1

    CONTINUE

  END CATCH;

END