SQL Server和MySQL锁定提示比较

本文涉及的产品
云数据库 RDS SQL Server,独享型 2核4GB
云数据库 RDS MySQL Serverless,0.5-2RCU 50GB
简介:

SQL Server篇


SQL Server中的提示

SQL Server提示(Hints)是指定的强制选项或策略,由 SQL Server 查询处理器针对 SELECT、INSERT、UPDATE 或 DELETE 语句执行。 提示将覆盖查询优化器可能为查询选择的任何执行计划。

它分为:

  • 联接提示(Join Hints)

  • 查询提示(Query Hints)

  • 表提示(Table Hints)


什么是提示(Hints)以及提示的分类,可详见:
http://www.cnblogs.com/downmoon/archive/2012/04/13/2445694.html


SQL Server中的表提示

通过指定锁定方法、一个或多个索引、查询处理操作(如表扫描或索引查找)或其他选项,表提示在数据操作语言 (DML) 语句执行期间覆盖查询优化器的默认行为。 表提示在 DML 语句的 FROM 子句中指定,仅影响在该子句中引用的表或视图。

表提示有哪些,可详见:
https://msdn.microsoft.com/zh-cn/library/ms187373.aspx


SQL Server支持的表锁定提示

HOLDLOCK 持有共享锁,直到整个事务完成,应该在被锁对象不需要时立即释放,等于SERIALIZABLE事务隔离级别

NOLOCK 语句执行时不发出共享锁,允许脏读 ,等于 READ UNCOMMITTED事务隔离级别
PAGLOCK 在使用一个表锁的地方用多个页锁
READPAST 让sql server跳过任何锁定行,执行事务,适用于READ UNCOMMITTED事务隔离级别只跳过RID锁,不跳过页,区域和表锁
ROWLOCK 强制使用行锁
TABLOCKX 强制使用独占表级锁,这个锁在事务期间阻止任何其他事务使用这个表
UPLOCK 强制在读表时使用更新而不用共享锁

两个最常用的锁定提示是WITH (NOLOCK)和WITH (UPDLOCK,HOLDLOCK)。

WITH (NOLOCK) 告诉服务器不需要申请任何锁:

1
SELECT  col1,col2  FROM  tbl1  WITH  (NOLOCK);


这是非常有效率的,因为避免了锁的额外开销。副作用是你会读到另一个事务的未提交数据。

WITH (UPDLOCK,HOLDLOCK) 用于避免锁升级和死锁。假设我从一个表读取数据到一个临时表,做一些操作,然后再次更新源表。在没有任何锁定提示时,这个初始读取会申请共享读锁,之后升级用于写。与此同时,其他事务申请共享锁,这个升级至少导致阻塞,有可能是死锁。为了避免它,在事务中尽早申请并持有写锁。例如:

1
2
3
4
CREATE  TABLE  # temp  ...
INSERT  INTO  # temp ... 
SELECT  Col1, Col2, Col3
FROM  tbl1  WITH  (UPDLOCK,HOLDLOCK)


事务隔离级别与锁保持的时间

锁保持的时间长度为保护所请求级别上的资源所需的时间长度。

用于保护读取操作的共享锁的保持时间取决于事务隔离级别。采用 READ COMMITTED 的默认事务隔离级别时,只在读取页的期间内控制共享锁。在扫描中,直到在扫描内的下一页上获取锁时才释放锁。如果指定 HOLDLOCK 提示或者将事务隔离级别设置为 REPEATABLE READ 或 SERIALIZABLE,则直到事务结束才释放锁。

SQL Server的默认事务隔离级别是READ COMMITTED,但是我们需要SERIALIZABLE。可以使用以下提示来让SQL Server忽略使用READ COMMITTED隔离级别而使用SERIALIZABLE代替,直到事务完成:

1
2
SELECT  Col1,Col2,Col3
FROM  tbl1  WITH  (UPDLOCK, HOLDLOCK)

UPDLOCK : 在事务中不阻塞SELECT和INSERT。它阻塞任何UPDATE或DELETE该事务查询的行。
HOLDLOCK:  等价于SERALIZABLE,并且允许SELECT,但是阻塞UPDATE和DELETE该事务查询的行,以及阻塞任何INSERT该事务查询的范围



SQL Server事务隔离级别,详见:
http://ultrasql.blog.51cto.com/9591438/1839116


SQL Server行版本控制的隔离级别,详见:
http://www.cnblogs.com/xwdreamer/archive/2012/07/30/2615357.html



MySQL篇


InnoDB锁定


行级别锁定:

Shared (S) locks: 它允许一个事务读取一行,如果一个会话的事务获得了这个锁,没有其他会话的事务可以在同一行上应用X锁。
Exclusive (X) locks: 它允许一个事务更新或删除一行。如果一个会话的事务获得了这个锁,没有其他会话的事务可以在同一行上应用S或X锁。

官方定义:
如果事务T1在r行上持有S锁,那么事务T2的请求在r行上锁的处理如下:

  • 事务T2请求S锁将会被立即授予。结果,T1和T2都在r行上持有S锁。

  • 事务T2请求X锁不能被立即授予。


如果事务T1在r行上持有一个X锁,那么事务T2的请求在r行上任何类型的锁都不能被立即授予。事务T2只有等待事务T1释放r行上的锁。

表级别锁定:

意向锁(Intention Locks):
InnoDB支持多粒度锁定,允许行锁和表锁共存。意向锁背后的想法是,对于一个事务,表明它会在该表的一行它后面会申请哪种类型的锁(S或X)。在InnoDB里使用了两种类型的意向锁:

  • Intention shared (IS): 允许请求以S或IS模式锁定行集。

  • Intention exclusive (IX): 允许请求以X、S、IX或IS模式显示锁定行集。


例如,后面会讲到SELECT ... LOCK IN SHARE MODE会设置一个IS锁,SELECT ... FOR UPDATE会设置一个IX锁。

更多InnoDB锁定细节,详见:
http://dev.mysql.com/doc/refman/5.7/en/innodb-locking.html


InnoDB锁定读(Locking Reads)


引用自官方文档:

 If you query data and then insert or update related data within the same transaction, the regular SELECT statement does not give enough protection. Other transactions can update or delete the same rows you just queried. InnoDB supports two types of locking reads that offer extra safety:

    .SELECT ... LOCK IN SHARE MODE sets a shared mode lock on any rows that are read. Other sessions can read the rows, but cannot modify them until your transaction commits. If any of these rows were changed by another transaction that has not yet committed, your query waits until that transaction ends and then uses the latest values.

    .For index records the search encounters, SELECT ... FOR UPDATE locks the rows and any associated index entries, the same as if you issued an UPDATE statement for those rows. Other transactions are blocked from updating those rows, from doing SELECT ... LOCK IN SHARE MODE, or from reading the data in certain transaction isolation levels. Consistent reads ignore any locks set on the records that exist in the read view. (Old versions of a record cannot be locked; they are reconstructed by applying undo logs on an in-memory copy of the record.) 

These clauses are primarily useful when dealing with tree-structured or graph-structured data, either in a single table or split across multiple tables. You traverse edges or tree branches from one place to another, while reserving the right to come back and change any of these “pointer” values.

All locks set by LOCK IN SHARE MODE and FOR UPDATE queries are released when the transaction is committed or rolled back.

Note:
Locking of rows for update using SELECT FOR UPDATE only applies when autocommit is disabled (either by beginning transaction with START TRANSACTION or by setting autocommit to 0. If autocommit is enabled, the rows matching the specification are not locked.


以上描述主要想让你知道两件事:
1.
select .. from .. where .. FOR UPDATE
select .. from .. where .. LOCK IN SHARE MODE
这两个锁定读都用于SELECT查询。

2.
LOCK IN SHARE MODE 以IX模式锁定整个表,并且以S模式SELECT行集。在读取的行上设置一个共享模式的锁。这个共享锁允许其它session读取数据但不允许修改它。 行读取的是最新的数据,如果他被其它事务使用中而没有提交,读取锁将被阻塞直到那个事务结束。 
FOR UPDATE 以IS模式锁定整个表,并且以X模式SELECT行集。在读取行上设置一个排他锁。阻止其他会话读取或者写入行数据。

让我们通过以下示例来深入理解:

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
37
38
39
40
41
mysql1 >  select  from  orders  where  id = 21548  FOR  UPDATE
#There  is  no  transaction  in  this so it immedietly releases the lock.
 
mysql2 >  update  orders  set  order_value = 1000  where  id = 21548;
#Works because there  is  no  lock applied  in  connection1.
 
mysql1 > start  transaction ;
mysql1 >  select  from  custom_orders  where  id = 21548  FOR  UPDATE ;
#acquired IX lock  on  table  and  X lock  on  row  with  id 21548
 
mysql2 >  select  from  orders  where  id = 21548; 
#works
mysql2 >  update  orders  set  order_value = 1000  where  id = 21549; 
#works
mysql2 >  select  from  orders  where  id = 21548  FOR  UPDATE
#waiting  for  lock which  is  acquired  by  connection1  on  row  with  id 21548 
 
mysql1 > start  transaction ;
mysql1 >  select  from  orders  where  id = 21548  FOR  UPDATE ;
#acquired IX lock  on  table  and  X lock  on  row  with  id 21548
 
mysql2 > start  transaction
mysql2 >  select  from  orders  where  id = 21548  FOR  UPDATE ; #waiting
#waiting  for  lock which  is  acquired  by  connection1  on  row  with  id 21548 
 
mysql1 > start  transaction ;
mysql1 >  select  from  orders  where  id = 21548 LOCK  IN  SHARE MODE;
#acquired  IS  lock  on  table  and  S lock  on  row  with  id 21548 so can 't update this row by different connection
 
mysql2 > update orders set order_value = 1000 where id = 21549;    
#works because row with id 21549 is not locked.
mysql2 > update orders set order_value = 1000 where id = 21548;
#waiting for lock which is acquired by connection1 on row with id 21548 
 
mysql1 > start transaction;
mysql1 > select * from orders where id = 21548 LOCK IN SHARE MODE;
#acquired IS lock on table and S lock on row with id 21548 so can' update  this row  by  different  connection
 
mysql2 >  select  from  custom_orders  where  id = 21548  FOR  UPDATE ;
#waiting  for  lock which  is  acquired  by  connection1  on  row  with  id 21548 
#This  is  asking  for  X lock but can't be given  as  connection1 has S lock  on  the same row.

MySQL必须保持它更新的每一行上的锁直到事务提交,因此如果事务失败或者取消它将一次回滚所有。

1
2
3
4
5
6
mysql1 >  start  transaction ;
mysql1 >  update  orders  set  order_value = 1000  where  id = 21548;
# As  this  is  happening  in  transaction  , mysql automatically applies lock  on  the concerned row.
 
mysql2 >  select  from  orders  where  id = 21548 LOCK  IN  SHARE MODE;
#waiting  for  the lock acquired  by  connection1  on  the same row.


MVCC:快照读(Snapshot Read)vs 当前读(Current Read)

MySQL InnoDB存储引擎,实现的是基于多版本的并发控制协议——MVCC (Multi-Version Concurrency Control) (注:与MVCC相对的,是基于锁的并发控制,Lock-Based Concurrency Control)。MVCC最大的好处,相信也是耳熟能详:读不加锁,读写不冲突。在读多写少的OLTP应用中,读写不冲突是非常重要的,极大的增加了系统的并发性能,这也是为什么现阶段,几乎所有的RDBMS,都支持了MVCC。

在MVCC并发控制中,读操作可以分成两类:快照读 (snapshot read)当前读 (current read)。快照读,读取的是记录的可见版本 (有可能是历史版本),不用加锁。当前读,读取的是记录的最新版本,并且,当前读返回的记录,都会加上锁,保证其他事务不会再并发修改这条记录。

在一个支持MVCC并发控制的系统中,哪些读操作是快照读?哪些操作又是当前读呢?以MySQL InnoDB为例:

快照读:简单的select操作,属于快照读,不加锁。(当然,也有例外,下面会分析)

  • select * from table where ?;


当前读:特殊的读操作,插入/更新/删除操作,属于当前读,需要加锁。

  • select * from table where ? lock in share mode;

  • select * from table where ? for update;

  • insert into table values (…);

  • update table set ? where ?;

  • delete from table where ?;


所有以上的语句,都属于当前读,读取记录的最新版本。并且,读取之后,还需要保证其他并发事务不能修改当前记录,对读取记录加锁。其中,除了第一条语句,对读取记录加S锁 (共享锁)外,其他的操作,都加的是X锁 (排它锁)。

更多MVCC快照读和当前读,详见:
http://hedengcheng.com/?p=771













本文转自UltraSQL51CTO博客,原文链接:http://blog.51cto.com/ultrasql/1839484 ,如需转载请自行联系原作者



相关实践学习
基于CentOS快速搭建LAMP环境
本教程介绍如何搭建LAMP环境,其中LAMP分别代表Linux、Apache、MySQL和PHP。
全面了解阿里云能为你做什么
阿里云在全球各地部署高效节能的绿色数据中心,利用清洁计算为万物互联的新世界提供源源不断的能源动力,目前开服的区域包括中国(华北、华东、华南、香港)、新加坡、美国(美东、美西)、欧洲、中东、澳大利亚、日本。目前阿里云的产品涵盖弹性计算、数据库、存储与CDN、分析与搜索、云通信、网络、管理与监控、应用服务、互联网中间件、移动服务、视频服务等。通过本课程,来了解阿里云能够为你的业务带来哪些帮助     相关的阿里云产品:云服务器ECS 云服务器 ECS(Elastic Compute Service)是一种弹性可伸缩的计算服务,助您降低 IT 成本,提升运维效率,使您更专注于核心业务创新。产品详情: https://www.aliyun.com/product/ecs
相关文章
|
18小时前
|
SQL 存储
SQL Server基本函数
SQL Server基本函数
|
3天前
|
SQL 存储 关系型数据库
MySQL索引原理以及SQL优化
MySQL索引原理以及SQL优化
28 0
|
14天前
|
SQL 缓存 关系型数据库
MySQL技能完整学习列表6、查询优化——3、查询缓存——4、SQL优化技巧
MySQL技能完整学习列表6、查询优化——3、查询缓存——4、SQL优化技巧
25 0
|
14天前
|
SQL 安全 关系型数据库
MySQL技能完整学习列表3、SQL语言基础——3、SQL运算符和函数
MySQL技能完整学习列表3、SQL语言基础——3、SQL运算符和函数
22 0
|
14天前
|
SQL 关系型数据库 MySQL
MySQL技能完整学习列表3、SQL语言基础——1、SQL(Structured Query Language)简介——2、基本SQL语句:SELECT、INSERT、UPDATE、DELETE
MySQL技能完整学习列表3、SQL语言基础——1、SQL(Structured Query Language)简介——2、基本SQL语句:SELECT、INSERT、UPDATE、DELETE
31 0
|
7月前
|
存储 SQL 数据库
SQL Server——为什么要使用存储过程?不使用是什么样的?
提高数据库执行速度,可能第一次见到这句话的小伙伴们感觉到非常的匪夷所思叭!怎么就提高了它的执行速度捏,从哪方面可以表现出来呢?既然这里要说到的是为什么要使用存储过程,也就是说它的优点是什么。那我们肯定就要对使用和不使用存储过程两方面来进行对比才能看出它的优点对吧。
|
10月前
|
存储 SQL Go
SQL Server 存储过程
SQL Server 存储过程
110 0
|
SQL 存储 数据可视化
【Sql Server】存储过程通过定时执行添加记录作业
通过上篇了解了什么是存储过程,创建存储过程的方法,以及调用存储过程的方法 本次将通过数据库中的作业功能,进行定时执行存储过程,这样就可以完成我们刚开始假设的场景
417 0
|
存储 SQL 程序员
【Sql Server】存储过程通过作业定时执行按天统计记录
通过前两篇文章的学习,我们已经对创建表、存储过程、作业等功能点有所了解 本次将结合前面所学习的知识点,创建统计表以及结合作业定时按天以及实时统计域名各个长度的记录值
276 0
【Sql Server】存储过程通过作业定时执行按天统计记录
|
SQL 存储 安全
【Sql Server】存储过程的创建和调用,随机添加域名记录
假设有这样一个场景 创建一个储存过程A,它执行添加一条随机产生3到8位长度的域名记录,通过定时器T1每隔1秒执行一次存储过程A 创建另一个存储过程B,它执行统计域名的长度3到8的记录数,通过定时器T2每隔1秒执行一次存储过程B
138 0
【Sql Server】存储过程的创建和调用,随机添加域名记录