SQL优化中的重要概念：锁定

时间：2022-04-14 06:54:08

原文: SQL优化中的重要概念：锁定

 

上篇文章讲的是事务，这篇就引出另一个重要概念，就是锁定。

当一个用户要读取另一个用户正在修改的数据，或者一个用户正在修改另一个用户正在读取的数据，或者一个用户要修改另一个用户正在修改的数据，就会出现并发问题。锁定能防止并发问题。

资源的锁定方式称为锁定模式，SQL Server中的锁定模式：共享锁，意向锁，更新锁，排他锁，架构稳定锁，架构修改锁，大批量更新锁，键范围锁。不是所有锁模式都是兼容的，如：一个加了排他锁的资源不能再加其他锁，其他事务必须等待，直到释放排他锁。

可以锁定SQL Server中的各类对象，可以锁定的资源在粒度上差异很大，从细粒度(行、键)到粗粒度(数据库)。细粒度的锁允许用户能查询那些未被锁定的行，并发性更高，但是需要更多的锁资源(每个被锁定的行都需要一个锁资源)；粗粒度的锁降低了并发性，但需要的锁资源很少。

 

1、在SQL Server中可锁定的资源：


   
  
    

  1.       DB(数据库)  
    2.  
   
  2.       Metadata(系统元数据)  
    3.  
   
  3.       Object(数据库对象:视图,函数,存储过程,触发器)  
    4.  
   
  4.       Table(表)  
    5.  
   
  5.       Hobt(堆或B树)  
    6.  
   
  6.       Allocation Unit(按照数据的类型(数据,行溢出、大对象)分组的相关页面)  
    7.  
   
  7.       Extent(8个8KB的页面)  
    8.  
   
  8.       Page(8KB数据页面)  
    9.  
   
  9.       Rid(行标示符对应一个堆表的行)  
    10.  
   
  10.       Key(键范围上的锁、B树中的键)  
    11.  
   
  11.       File  
    12.  
   
  12.       Application  
    13.

 

2、查看锁的活动


   
  
    

  1.       select resource_type, --资源类型  
    2.  
   
  2.       resource_database_id, --资源所在的数据库id  
    3.  
   
  3.       resource_associated_entity_id, --数据库中与资源相关联的实体的 ID。  
    4.  
   
  4.       --该值可以是对象ID、Hobt ID 或分配单元 ID，  
    5.  
   
  5.       --具体视资源类型而定  
    6.  
   
  6.       object_name(resource_associated_entity_id,resource_database_id),  
    7.  
   
  7.        
    8.  
   
  8.       resource_lock_partition, --已分区锁资源的锁分区ID。对于未分区锁资源值为 0   
    9.  
   
  9.       resource_description, --资源的说明，其中只包含从其他资源列中无法获取的信息  
    10.  
   
  10.        
    11.  
   
  11.       request_session_id, --请求资源的会话  
    12.  
   
  12.       request_type, --请求类型，该值为 LOCK   
    13.  
   
  13.       request_mode, --请求的模式,对于已授予的请求，为已授予模式,  
    14.  
   
  14.       --对于等待请求，为正在请求的模式(锁定模式)   
    15.  
   
  15.       request_status --请求的当前状态，  
    16.  
   
  16.       --可能值为 GRANTED、CONVERT 或 WAIT  
    17.  
   
  17.        
    18.  
   
  18.       from sys.dm_tran_locks  
    19.  
   
  19.       WHERE request_session_id = 361  
    20.

 

3、控制表的锁升级

每个锁都会消耗内存资源，当锁的数量增加时，那么所需要的内存就会增加，而系统内可用的内存就会减少。如果锁占用的内存比率超过一个阀值，SQL Server会将细粒度锁（行锁）升级为粗粒度锁（表锁），这个过程就是锁升级。

 

锁升级的优点是可以减少锁的数量，相应的减少内存的使用量，而缺点是由于锁住了更大的资源，所以会导致阻塞，降低并发性。


   
  
    

  1.       --默认值，不管是不是分区表，会在表级别启用锁升级  
    2.  
   
  2.       ALTER TABLE t  
    3.  
   
  3.       SET (lock_escalation = TABLE)  
    4.  
   
  4.        
    5.  
   
  5.       --当表升级时，如果表已经分区，会在分区级别启用锁升级  
    6.  
   
  6.       ALTER TABLE t  
    7.  
   
  7.       SET (lock_escalation = auto)  
    8.  
   
  8.        
    9.  
   
  9.       --在表级别禁用锁升级，如果用了TabLock提示或在Serializable隔离级别下查询，还是会有表锁  
    10.  
   
  10.       ALTER TABLE t  
    11.  
   
  11.       SET (lock_escalation = disable)  
    12.

4、隔离级别

影响锁定的除了上面提到的锁定模式、锁的粒度，还有就是事务的隔离级别。

所谓隔离级别其实就是事务与事务之间相互影响的程度，比如，一个事务修改了数据，那么其他事务是否能看到这些修改的数据，无论事务是否提交。对于最高的隔离级别，这个事务所做的修改，其他任何事务都看不到；而最低的隔离级别，这个事务所做的修改，可以被其他任何事务看到。

SQL Server隔离级别：

（1）read uncommitted能解决丢失更新的问题，但是会导致脏读。

（2）read committed读取的是已提交的数据，所以解决了脏读的问题，但是会有不可重复读取的问题，也就是在一个事务中有两次读取，第一次读取的和第二次读取的同一条数据，可能值是不同的，因为在事务中的select语句在读取完之后就立即释放的共享锁，而此时有另一个事务把刚才第一个事务读取的那条数据修改了，这样第一次读和第二次读到的值就会不同。

（3）repeatable read解决了不可重复读取的问题，也就是在一个事务中的前后两次读取，读取到的数据值是一样的，但是会有幻读的可能，也就是第一次读出的数据确实和第二次读取的数据一样，但是第二次读取的记录条数可能多于第一次读取的记录条数，因为在读取的时候确实是锁住了被读取的记录，但是这个表可能添加了新的记录。

（4）serializable通过锁住查询范围内的键、键与键之间的范围来解决幻读的问题，比如where id >=5 and id <=10,加入表表中只有id为7,9的两条记录，那么5-6、7-8、9-10这3个范围都会被锁住。


（5）在ALLOW_SNAPSHOT_ISOLATION下的snapshot这种隔离级别允许读取事务一致性版本的数据，但可能不是最新的版本，也就是说在一个事务中只能读到某个版本，比如，在一个事务中有两次读取，第一次读完后，数据被另一个事务修改且事务提交了，此时进行第2次读取，那么读出来的还是和第一次读取一样的数据，这就是在一个事务中如果数据被其他事务修改了，读出来的数据也一样。优点是数据读取不会阻塞写，写也不会阻塞读取。另外，如果两个事务同时修改同一行数据，会导致更新冲突错误。

（6）在READ_COMMITTED_SNAPSHOT下的read committed隔离级别允许在同一事务中总是能读取运行的已提交的数据，而且数据读取不会阻塞写，写也不会阻塞读取，也不会导致更新冲突。

SQL优化中的重要概念：锁定SQL优化中的重要概念：锁定 不想长大啊 发布了416 篇原创文章 · 获赞 135 · 访问量 94万 他的留言板 关注

相关文章