锁+LOCK

绝地灬酷狼 2022-06-18 00:06 221阅读 0赞

--锁 总结
    锁（LOCKING）
     是最常用的并发控制机构。是防止其他事务访问指定的资源控制、实现并发控制的一种主要手段。锁是事务对某个数据库中的资源（如表和记录）存取前，先向系统提出请求，封锁该资源，事务获得锁后，即取得对数据的控制权，在事务释放它的锁之前，其他事务不能更新此数据。当事务撤消后，释放被锁定的资源。
    
     锁机制是解决阻塞的直接方法
    
    锁模式：
    
     共享 (S) 用于不更改或不更新数据的操作（只读操作）。
     更新 (U) 用于可更新的资源中。防止当多个会话在读取、锁定以及随后可能进行的资源更新时发生常见形式的死锁。
     排它 (X) 用于数据修改操作，例如 INSERT、UPDATE 或 DELETE。确保不会同时同一资源进行多重更新。
     意向锁用于建立锁的层次结构。意向锁的类型为：意向共享 (IS)、意向排它 (IX) 以及与意向排它共享 (SIX)。
     架构锁在执行依赖于表架构的操作时使用。架构锁的类型为：结构修改锁（Sch-M）和结构稳定锁（Sch-S）。
     执行表定义语言操作时，SQL Server采用Sch-M锁，编译查询时，SQL Server采用Sch-S锁。 
    
     大容量更新 (BU) 向表中大容量复制数据并指定了 TABLOCK 提示时使用。
    
    共享锁
     共享 (S)锁允许并发事务读取 (SELECT) 一个资源。资源上存在共享 (S)锁时，任何其它事务都不能修改数据。一旦已经读取数据，便立即释放资源上的共享 (S)锁，除非将事务隔离级别设置为可重复读或更高级别，或者在事务生存周期内用锁定提示保留共享 (S)锁。即：共享锁锁定的资源可以被其他用户读取，但其他用户无法修改它，在执行Select时，SQL Server会对对象加共享锁。用于保护读取操作的共享锁的保持时间取决于事务隔离级别。采用 READ COMMITTED 的默认事务隔离级别时，只在读取页的期间内控制共享锁。在扫描中，直到在扫描内的下一页上获取锁时才释放锁。
    
    
    更新锁
     更新 (U)锁可以防止通常形式的死锁。一般更新模式由一个事务组成，此事务读取记录，获取资源（页或行）的共享 (S)锁，然后修改行，此操作要求锁转换为排它 (X)锁。如果两个事务获得了资源上的共享模式锁，然后试图同时更新数据，则一个事务尝试将锁转换为排它 (X)锁。共享模式到排它锁的转换必须等待一段时间，因为一个事务的排它锁与其它事务的共享模式锁不兼容；发生锁等待。第二个事务试图获取排它 (X)锁以进行更新。由于两个事务都要转换为排它 (X)锁，并且每个事务都等待另一个事务释放共享模式锁，因此发生死锁。
    若要避免这种潜在的死锁问题，请使用更新 (U)锁。一次只有一个事务可以获得资源的更新 (U)锁。如果事务修改资源，则更新 (U)锁转换为排它 (X)锁。否则，锁转换为共享锁。即：当SQL Server准备更新数据时，它首先对数据对象作更新锁锁定，这样数据将不能被修改，但可以读取。等到SQL Server确定要进行更新数据操作时，他会自动将更新锁换为独占锁，当对象上有其他锁存在时，无法对其加更新锁。
    
    当我们用UPDLOCK来读取记录时可以对取到的记录加上更新锁，从而加上锁的记录在其它的线程中是不能更改的只能等本线程的事务结束后才能更改，我如下示例：
      简言之：如果UPDLOCK被包在事务中，那么被UPDLOCK的行只能当前进程查看、修改操作,其他进程连看都无法看到(有对该锁定的表做UPDATE)。如果不被 包在事务中，其他进程可以看但是不能修改。
    
    BEGIN TRANSACTION --开始一个事务
    SELECT CODE,QUANTITY,QTY
     FROM userTable WITH (UPDLOCK)
     WHERE code in ('a','b','c','d')
    
     UPDATE userTable SET Qty = Qty - A.Qty
     FROM userTable  AS A 
     INNER JOIN  @_Table AS B ON A.code = B.code
    
    COMMIT TRANSACTION --提交事务
    
    这样在更新时其它的线程或事务在这些语句执行完成前是不能更改 CODE 是a、b、c、d的记录的．其它的都可以修改和读，a、b、c、d 的只能读，要是修改的话只能等这些语句完成后才能操作.从而保证的数据的修改
    
    
    排它锁
     排它 (X)锁可以防止并发事务对资源进行访问。其它事务不能读取或修改排它 (X)锁锁定的数据。即：只允许进行锁定操作的程序使用，其他任何对他的操作均不会被接受。执行数据更新命令时，SQL Server会自动使用独占锁。当对象上有其他锁存在时，无法对其加独占锁。
     当对表中的某行加了排他锁后，实际上也同时悄悄对对页也加了意向排他锁、对整个数据表加了意向排他锁(IX).该表就不能被加表锁了。这样如果要给表加锁就不用逐条扫描行数据是否被加了排他锁，只要查是否有IX锁就可以了。
    
    
    意向锁
     意向共享 (IS)
     意向排它 (IX)
     意向排它共享 (SIX)
     
     意向锁表示 SQL Server 需要在层次结构中的某些底层资源上获取共享 (S)锁或排它 (X)锁。例如，放置在表级的共享意向锁表示事务打算在表中的页或行上放置共享 (S)锁。在表级设置意向锁可防止另一个事务随后在包含那一页的表上获取排它 (X)锁。意向锁可以提高性能，因为 SQL Server 仅在表级检查意向锁来确定事务是否可以安全地获取该表上的锁。而无须检查表中的每行或每页上的锁以确定事务是否可以锁定整个表。
    
    意向锁模式 
     意向共享 (IS) 通过在各资源上放置 S锁，表明事务的意向是读取层次结构中的部分（而不是全部）底层资源。
     
     意向排它 (IX) 通过在各资源上放置 X锁，表明事务的意向是修改层次结构中的部分（而不是全部）底层资源。IX 是 IS 的超集。表级的IX锁表示DELETE 语句打算在页级、行级或键级获得一个X锁。页面级上的IX锁 表示查询打算获取页面中一行上的X锁。
    
     
     意向排它共享 (SIX) 通过在各资源上放置 IX锁，表明事务的意向是读取层次结构中的全部底层资源并修改部分（而不是全部）底层资源。允许顶层资源上的并发 IS锁。例如，表的 SIX锁在表上放置一个 SIX锁（允许并发 IS锁），在当前所修改页上放置 IX锁（在已修改行上放置 X锁）。虽然每个资源在一段时间内只能有一个 SIX锁，以防止其它事务对资源进行更新，但是其它事务可以通过获取表级的 IS锁来读取层次结构中的底层资源。在给定的资源上只能放置一个SIX。其他事务可以在SIX存在的同时在较低级别的资源上放置IS锁。
     
    与游标有关的锁信息：
    
     根据为游标设置的并发选项，游标可以获取共享模式的滚动锁以保护提取。当需要滚动锁时，直到下一次提取或关闭游标（以先发生者为准）时才释放滚动锁。但是，如果指定 HOLDLOCK，则直到事务结束才释放滚动锁。在给定的资源上只能放置一个SIX。 
      
     锁的粒度（数据库对象资源上锁的级别）：
     锁粒度是被封锁目标的大小,封锁粒度小则并发性高,但开销大,封锁粒度大则并发性低但开销小。即锁生效的范围。
    
     行级别锁（RID）：数据库表上最低级别的锁。
     关键字锁（KEY）：索引中的行级锁。
     页级别锁（PAGE）： 8 千字节 (KB) 的数据页或索引页。
     区级别锁（EXT）：相邻的八个数据页或索引页构成的一组称为区。
     堆或B-树级别锁（HoBT）:表上无聚簇索引即为 堆表.该锁主要在分区的表上应用。
     表级别锁（TAB）：你知道的。
     数据库级别锁（DB）：你也知道的。
     
     NOLOCK(不加锁)
    此选项被选中时，SQL Server在读取或修改数据时不加任何锁。在这种情况下，用户有可能读取到未完成事务(Uncommited Transaction)或回滚(Roll Back)中的数据,即所谓的"脏数据"。NOLOCK语句执行时不发出共享锁，允许脏读，等于READ UNCOMMITTED事务隔离级别(set transaction isolation level read uncommitted)。
    --即:--事务隔离级别允许脏读 案例
    UPDATE test.dbo.ta SET code='UNCOMMITTED' WHERE id <10
    set transaction isolation level read uncommitted
    go
    SELECT * FROM test.dbo.ta WHERE id<10
    --等效于
    select * from test.dbo.ta with(nolock) where id<10
    --
    
    ROWLOCK (行锁)
    
    强制行上加锁
    
    SELECT * FROM TABLENAME WITH(ROWLOCK) WHERE 1=1;
    
    HOLDLOCK(保持锁) :锁定数据库的一个表：SELECT * FROM table WITH (HOLDLOCK)
    此选项被选中时，SQL Server会将此共享锁保持至整个事务结束，而不会在途中释放。HOLDLOCK持有共享锁，直到整个事务完成，应该在被锁对象不需要时立即释放，等于SERIALIZABLE事务隔离级别。其他事务可以读取表，但不能更新删除
    
    UPDLOCK(修改锁)
    此选项被选中时，SQL Server在读取数据时使用修改锁来代替共享锁，并将此锁保持至整个事务或命令结束。使用此选项能够保证多个进程能同时读取数据但只有该进程能修改数据。
    
    begin tran
     declare @id
    select @id=id from t_CreateGroupIdPool with(UPDLOCK,readpast) where 1=2
    waitfor delay '00:00:05'
     delete from t_CreateGroupIdPool where id=@id
    commit tran
    
    TABLOCK(表锁)
    此选项被选中时，SQL Server将在整个表上置共享锁直至该命令结束。这个选项保证其他进程只能读取而不能修改数据。
    
    PAGLOCK(页锁)
    此选项为默认选项，当被选中时，SQL Server使用共享页锁。
    
    TABLOCKX(排它表锁) SELECT * FROM table WITH (TABLOCKX) 
    此选项被选中时，SQL Server将在整个表上置排它锁直至该命令或事务结束。这将防止其他进程读取或修改表中的数据。其他事务不能读取表,更新和删除。在事物未提交前，连读取都是阻塞的，直到另一事物提交后才可以读取，这样就保证了数据的一致性。
    
     READPAST 让sql server跳过任何锁定行，执行事务，适用于READ UNCOMMITTED事务隔离级别只跳过RID锁，不跳过页，区域和表锁 
    select * from tablename with(updlock,readpast) where 1=?;
    
    注意: 锁定数据库的一个表的区别SELECT * FROM table WITH (HOLDLOCK) 其他事务可以读取表，但不能更新删除SELECT * FROM table WITH (TABLOCKX) 其他事务不能读取表,更新和删除 
     死锁：
     就是多个用户申请不同封锁,由于申请者均拥有一部分封锁权而又等待其他用户拥有的部分封锁而引起的无休止的等待。
     即：如果两个进程都锁定数据，并且直到其它进程释放自己的锁时，每个进程才能释放自己的锁，即发生死锁情况。
    
    避免死锁：
     1 使用事务时，尽量缩短事务的逻辑处理过程，及早提交或回滚事务；
     2 设置死锁超时参数为合理范围，如：3分钟-10分种；超过时间，自动放弃本次操作，避免进程悬挂；
     3 优化程序，检查并避免死锁现象出现；
     4 .对所有的脚本和SP都要仔细测试，在正是版本之前。
     5 所有的SP都要有错误处理（通过@error）
     6 一般不要修改SQL SERVER事务的默认级别。不推荐强行加锁
    
    锁升级
     MSSQL 自身提供自动锁升级功能。锁升级后，较低级别的锁自动释放。锁升级的阀值是MSSQL在事务期间动态确定的。锁的升级优化了加锁开销。 Transact-SQL 语句在单个表或索引的引用上获取至少 5,000 个锁时，或在表已分区的情况下，在单个表分区或索引分区的引用上获取至少 5,000 个锁时，并且 ALTER TABLE SET LOCK_ESCALATION 选项设为 AUTO时会触发锁升级。但是也可以手动设定取消自动升级. 如：ALTER TABLE tablename set (LOCK_ESCALATION=DISABLE)--即 完全禁用该表上的自动升级功能。
    
    
    隔离级别
            隔离级别影响了锁的模式。 隔离级别是用来控制锁的应用。即什么样的情况使用什么样的锁机制。可以在连接或查询级别上使用：
          set transaction isolation level 语句来设置隔离级别.    
    
    
    隔离级别的分类
        1、未提交读(READ UNCOMMITTED )，是隔离事务级别中最低的。只保证不读取物理上损坏的数据。不请求S锁,也即不存在X锁阻塞等问题.
        2、已提交读(READ COMMITTED )。该级别是默认级别。尽量不要你管启用该级别下是 行版本控制,如果启用了行版本控制 (即: alter database databasename set read_committed_snapshot on ),必须考虑TEMPDB 数据库的空闲空间。建议不要启用。已提交读易造成 不可重复读或幻读。因为它SELECT 请求的S锁不被保留到事务结束。
        3、可重复读(REPEATABLE )。它SELECT 请求的S锁会被保留到事务结束。从而会阻塞其他事务在这段时间段内对数据的修改。即：在事务结束之前 S 锁会一直被保留。
        例如： set transaction isolation level repeatable read  或 select * from tablename with(repeatableread) 这两种方式是等效的. 这种方式有个缺点就是容易死锁.所以 用 SELECT * FROM TABLENAME WITH(UPDLOCK)代替.
        4、可序列化(SERIALIZABLE)。事务完全隔离相互之间。该级别是最高级。可序列化不仅在访问的行上获取一个锁,而且还获取一个在该行和按照请求的数据级顺序的下一行上的范围锁(RangeS-S).这阻止了第一个事务所操作的数据中另一个事务增加行,避免第一个事务在其范围内的数据集中查找新建的行操作.通俗的理解是: 事务1对一定范围内的数据查询并修改,这时事务2给这个范围内增加一行或多行数据.这时就会出现逻辑不一致的情况.
        解决方法就是 SET TRANSACTION ISOLATION LEVEL SERIALIZABLE 或 在表中用锁实现的方式为: 
        select * from tablename with(holdlock)
     如果手动改动隔离级别记得最后处理完成后一定要改回默认隔离级别即： SET TRANSACTION ISOLATION LEVEL READ COMMITTED  
    
    
     --给表中的某行加锁定
     
     SET TRANSACTION ISOLATION LEVEL READ UNCOMMITTED   
    SELECT * FROM userTable ROWLOCK WHERE code='wenzhong' 
     ---锁定数据库的一个表 
    select * from userTable (tablockx) where 1=1 
    --加锁后其它人不可操作，直到加锁用户解锁，用commit或rollback解锁 
    
    
    -- A事务先更新userTable表，然后延时5秒，再更新leaderTable表；   
    begin tran   
    update userTable set code='WENZHONG' where ID=211;   
    --这将在 userTable 中生成排他行锁，直到事务完成后才会释放该锁。   
    waitfor delay '00:00:05';   
    --进入延时   
    update leaderTable set code='LEADER' where ID=110 ;   
    commit tran   
    -- B事务先更新leaderTable表，然后延时10秒，再更新userTable表；   
    begin tran   
    update leaderTable set CODE='LEADER' where ID=110;   
    --这将在 leaderTable 中生成排他行锁，直到事务完成后才会释放该锁   
    waitfor delay '00:00:10'  
    --进入延时   
    update userTable set code='WENZHONG' where  ID=211 ;   
    commit tran  
    若并发执行上述两个事务，A,B两事务都要等待对方释放排他锁，这样便形成了死锁
    
    
     
    
    SQL Server提供几个DMV,查看locks
    
    sys.dm_exec_requests
    sys.dm_tran_locks
    sys.dm_os_waiting_tasks
    sys.dm_tran_database_transactions
    查询阻塞和被阻塞的相关SQL
     SELECT * FROM sys.dm_os_waiting_tasks  或 SP_WHO2  --查询阻塞和被阻塞SPID链接信息
     SELECT * FROM SYS.DM_TRAN_LOCKS---查询阻塞和被阻塞SPID的锁信息。
     sys.dm_exec_requests 和 sys.dm_exec_sql_text 和 sys.dm_exec_query_plan  --三者关联即可查询出最后阻塞和被阻塞执行的SQL语句