首页 / 更多教程 / SQL优化中的重要概念:锁定
SQL优化中的重要概念:锁定
内容导读
互联网集市收集整理的这篇技术教程文章主要介绍了SQL优化中的重要概念:锁定,小编现在分享给大家,供广大互联网技能从业者学习和参考。文章包含4709字,纯文字阅读大概需要7分钟。
内容图文
原文:SQL优化中的重要概念:锁定
上篇文章讲的是事务,这篇就引出另一个重要概念,就是锁定。
当一个用户要读取另一个用户正在修改的数据,或者一个用户正在修改另一个用户正在读取的数据,或者一个用户要修改另一个用户正在修改的数据,就会出现并发问题。锁定能防止并发问题。
资源的锁定方式称为锁定模式,SQL Server中的锁定模式:共享锁,意向锁,更新锁,排他锁,架构稳定锁,架构修改锁,大批量更新锁,键范围锁。不是所有锁模式都是兼容的,如:一个加了排他锁的资源不能再加其他锁,其他事务必须等待,直到释放排他锁。
可以锁定SQL Server中的各类对象,可以锁定的资源在粒度上差异很大,从细粒度(行、键)到粗粒度(数据库)。细粒度的锁允许用户能查询那些未被锁定的行,并发性更高,但是需要更多的锁资源(每个被锁定的行都需要一个锁资源);粗粒度的锁降低了并发性,但需要的锁资源很少。
1、在SQL Server中可锁定的资源:
- DB(数据库)
- Metadata(系统元数据)
- Object(数据库对象:视图,函数,存储过程,触发器)
- Table(表)
- Hobt(堆或B树)
- Allocation Unit(按照数据的类型(数据,行溢出、大对象)分组的相关页面)
- Extent(8个8KB的页面)
- Page(8KB数据页面)
- Rid(行标示符对应一个堆表的行)
- Key(键范围上的锁、B树中的键)
- File
- Application
2、查看锁的活动
- select resource_type, --资源类型
- resource_database_id, --资源所在的数据库id
- resource_associated_entity_id, --数据库中与资源相关联的实体的 ID。
- --该值可以是对象ID、Hobt ID 或分配单元 ID,
- --具体视资源类型而定
- object_name(resource_associated_entity_id,resource_database_id),
-
- resource_lock_partition, --已分区锁资源的锁分区ID。对于未分区锁资源值为 0
- resource_description, --资源的说明,其中只包含从其他资源列中无法获取的信息
-
- request_session_id, --请求资源的会话
- request_type, --请求类型,该值为 LOCK
- request_mode, --请求的模式,对于已授予的请求,为已授予模式,
- --对于等待请求,为正在请求的模式(锁定模式)
- request_status --请求的当前状态,
- --可能值为 GRANTED、CONVERT 或 WAIT
-
- from sys.dm_tran_locks
- WHERE request_session_id = 361
3、控制表的锁升级
每个锁都会消耗内存资源,当锁的数量增加时,那么所需要的内存就会增加,而系统内可用的内存就会减少。如果锁占用的内存比率超过一个阀值,SQL Server会将细粒度锁(行锁)升级为粗粒度锁(表锁),这个过程就是锁升级。
锁升级的优点是可以减少锁的数量,相应的减少内存的使用量,而缺点是由于锁住了更大的资源,所以会导致阻塞,降低并发性。
- --默认值,不管是不是分区表,会在表级别启用锁升级
- ALTER TABLE t
- SET (lock_escalation = TABLE)
-
- --当表升级时,如果表已经分区,会在分区级别启用锁升级
- ALTER TABLE t
- SET (lock_escalation = auto)
-
- --在表级别禁用锁升级,如果用了TabLock提示或在Serializable隔离级别下查询,还是会有表锁
- ALTER TABLE t
- SET (lock_escalation = disable)
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隔离级别允许在同一事务中总是能读取运行的已提交的数据,而且数据读取不会阻塞写,写也不会阻塞读取,也不会导致更新冲突。
不想长大啊 发布了416 篇原创文章 · 获赞 135 · 访问量 94万+ 他的留言板 关注内容总结
以上是互联网集市为您收集整理的SQL优化中的重要概念:锁定全部内容,希望文章能够帮你解决SQL优化中的重要概念:锁定所遇到的程序开发问题。 如果觉得互联网集市技术教程内容还不错,欢迎将互联网集市网站推荐给程序员好友。
内容备注
版权声明:本文内容由互联网用户自发贡献,该文观点与技术仅代表作者本人。本站仅提供信息存储空间服务,不拥有所有权,不承担相关法律责任。如发现本站有涉嫌侵权/违法违规的内容, 请发送邮件至 gblab@vip.qq.com 举报,一经查实,本站将立刻删除。
内容手机端
扫描二维码推送至手机访问。