澳门皇冠金沙网站-澳门皇冠844网站

热门关键词: 澳门皇冠金沙网站,澳门皇冠844网站

并发控制,相同索引键值或同一行或间隙锁的冲

1.使用相同索引键值的冲突

mysql 锁机制

标签(空格分隔): mysql


InnoDB锁问题
InnoDB与MyISAM的最大不同有两点:一是支持事务(TRANSACTION);二是采用了行级锁。行级锁与表级锁本来就有许多不同之处,另外,事务的引入也带来了一些新问题。下面我们先介绍一点背景知识,然后详细讨论InnoDB的锁问题。
背景知识
1.事务(Transaction)及其ACID属性
事务是由一组SQ语句组成的逻辑处理单元,事务具有以下4个属性,通常简称为事务的ACID属性。
     原子性(Atomicity):事务是一个原子操作单元,其对数据的修改,要么全都执行,要么全都不执行。
      一致性(Consistent):在事务开始和完成时,数据都必须保持一致状态。这意味着所有相关的数据规则都必须应用于事务的修改,以保持数据的完整性;事务结束时,所有的内部数据结构(如B树索引或双向链表)也都必须是正确的。
     隔离性(Isoation):数据库系统提供一定的隔离机制,保证事务在不受外部并发操作影响的“独立”环境执行。这意味着事务处理过程中的中间状态对外部是不可见的,反之亦然。
     持久性(Durabe):事务完成之后,它对于数据的修改是永久性的,即使出现系统故障也能够保持。
银行转帐就是事务的一个典型例子。
2.并发事务处理带来的问题
相对于串行处理来说,并发事务处理能大大增加数据库资源的利用率,提高数据库系统的事务吞吐量,从而可以支持更多的用户。但并发事务处理也会带来一些问题,主要包括以下几种情况。
     更新丢失(ost Update):当两个或多个事务选择同一行,然后基于最初选定的值更新该行时,由于每个事务都不知道其他事务的存在,就会发生丢失更新问题--最后的更新覆盖了由其他事务所做的更新。例如,两个编辑人员制作了同一文档的电子副本。每个编辑人员独立地更改其副本,然后保存更改后的副本,这样就覆盖了原始文档。最后保存其更改副本的编辑人员覆盖另一个编辑人员所做的更改。如果在一个编辑人员完成并提交事务之前,另一个编辑人员不能访问同一文件,则可避免此问题。
     脏读(Dirty Reads):一个事务正在对一条记录做修改,在这个事务完成并提交前,这条记录的数据就处于不一致状态;这时,另一个事务也来读取同一条记录,如果不加控制,第二个事务读取了这些“脏”数据,并据此做进一步的处理,就会产生未提交的数据依赖关系。这种现象被形象地叫做"脏读"。
     不可重复读(Non-Repeatabe Reads):一个事务在读取某些数据后的某个时间,再次读取以前读过的数据,却发现其读出的数据已经发生了改变、或某些记录已经被删除了!这种现象就叫做“不可重复读”。
     幻读(Phantom Reads):一个事务按相同的查询条件重新读取以前检索过的数据,却发现其他事务插入了满足其查询条件的新数据,这种现象就称为“幻读”。
3.事务隔离级别
在上面讲到的并发事务处理带来的问题中,“更新丢失”通常是应该完全避免的。但防止更新丢失,并不能单靠数据库事务控制器来解决,需要应用程序对要更新的数据加必要的锁来解决,因此,防止更新丢失应该是应用的责任。
“脏读”、“不可重复读”和“幻读”,其实都是数据库读一致性问题,必须由数据库提供一定的事务隔离机制来解决。数据库实现事务隔离的方式,基本上可分为以下两种。
  一种是在读取数据前,对其加锁,阻止其他事务对数据进行修改。
  另一种是不用加任何锁,通过一定机制生成一个数据请求时间点的一致性数据快照(Snapshot),并用这个快照来提供一定级别(语句级或事务级)的一致性读取。从用户的角度来看,好象是数据库可以提供同一数据的多个版本,因此,这种技术叫做数据多版本并发控制(MutiVersion Concurrency Contro,简称MVCC或MCC),也经常称为多版本数据库。
数据库的事务隔离越严格,并发副作用越小,但付出的代价也就越大,因为事务隔离实质上就是使事务在一定程度上 “串行化”进行,这显然与“并发”是矛盾的。同时,不同的应用对读一致性和事务隔离程度的要求也是不同的,比如许多应用对“不可重复读”和“幻读”并不敏感,可能更关心数据并发访问的能力。
为了解决“隔离”与“并发”的矛盾,ISO/ANSI SQ92定义了4个事务隔离级别,每个级别的隔离程度不同,允许出现的副作用也不同,应用可以根据自己的业务逻辑要求,通过选择不同的隔离级别来平衡 “隔离”与“并发”的矛盾。表20-5很好地概括了这4个隔离级别的特性。

  由于mysql 的行锁是针对索引加的锁,不是针对记录加的锁,所以虽然是访问不同行的记录,但如果是使用相同的索引键,是会出现锁冲突的。设计时要注意
  例如:city表city_id字段有索引,Cityname字段没有索引:

参考文档

  1. https://www.2cto.com/database/201508/429967.html
  2. http://www.cnblogs.com/aipiaoborensheng/p/5767459.html

 最后要说明的是:各具体数据库并不一定完全实现了上述4个隔离级别,例如,Oracle只提供Read committed和Serializable两个标准隔离级别,另外还提供自己定义的Read only隔离级别;SQL Server除支持上述ISO/ANSI SQL92定义的4个隔离级别外,还支持一个叫做“快照”的隔离级别,但严格来说它是一个用MVCC实现的Serializable隔离级别。MySQL 支持全部4个隔离级别,但在具体实现时,有一些特点,比如在一些隔离级别下是采用MVCC一致性读,但某些情况下又不是,这些内容在后面的章节中将会做进 一步介绍。

会话1

会话2

SET autocommit=0;

SET autocommit=0;

SELECT * FROM city WHERE city_id=14 AND Cityname='深圳' FOR UPDATE;

city_id      country_id        cityname CityCode

14     2       深圳         001

 

 

会话2与会话1访问的是不同的记录,但是因为使用了相同的索引值,所以需要等待锁

SELECT * FROM city WHERE city_id=14 AND Cityname='长沙' FOR UPDATE;

等待...

概念

  1. 共享锁(S):允许一个事务去读一行,阻止其他事务获得相同的数据集的排他锁。
  2. 排他锁(X):允许获得排他锁的事务更新数据,但是组织其他事务获得相同数据集的共享锁和排他锁。
  3. 对于insert、update、delete,InnoDB会自动给涉及的数据加排他锁(X);对于一般的Select语句,InnoDB不会加任何锁,事务可以通过以下语句给显示加共享锁或排他锁。

 

 2.使用不同索引键值但是同一行的冲突 

共享锁

select * from table_name where .....lock in share mode

Note left of 事务1: select * from table_1 where id=1 lock in share mode;
事务1-->事务2: 
Note right of 事务2: select * from table_1 where id=1 lock in share mode;
事务2-->事务1: 
Note left of 事务1: update table_1 set age=10 where id=1;
Note left of 事务1: 事务1更新时发现此行锁被其他事务享用,等待
事务1-->事务2: 
Note right of 事务2: update table_1 set age=12 where id=1;
Note right of 事务2: 事务2更新时发现此行锁被其他事务享用,也等待,导致死锁

获取InnoDB行锁争用情况    

  当表有多个索引时候,不同的事务可以使用不同的索引锁定不同的行,无论什么索引,innodb都会使用行锁来对数据加锁。
  例如city表city_id字段有主键索引,CityCode字段有普通索引:

排他锁

select * from table_name where .....for update

Note left of 事务1: select * from table_1 where id=1 for update;
事务1-->事务2: 
Note right of 事务2: select * from table_1 where id=1 for update;
Note right of 事务2: 等待...
事务2-->事务1: 
Note left of 事务1: update table_1 set age=10 where id=1;
Note left of 事务1: 更新完后释放锁
事务1-->事务2: 
Note right of 事务2: 获得锁后,得到其他事务提交的记录

可以通过检查InnoDB_row_lock状态变量来分析系统上的行锁的争夺情况:

会话1

会话2

SET autocommit=0;

SET autocommit=0;

SELECT * FROM city WHERE city_id=14  FOR UPDATE;

city_id      country_id        cityname CityCode

14     2       深圳         001

 

 

该记录没有被索引,所以可以获得锁

SELECT * FROM city WHERE  CityCode='002' FOR UPDATE;

city_id      country_id        cityname CityCode

15     2       长沙         002

 

由于该记录被会话1锁定,所以需要等待

SELECT * FROM city WHERE  CityCode='001' FOR UPDATE;

等待...

行锁的三种形式

  1. Record lock:锁定一条记录。
  2. Gap lock
  3. Next-key lock

Java代码 

3. 创建了索引,但使用的是表锁
  在前面章节说过,创建了索引但不走索引的情况,这种情况下innodb将使用表锁,而不是行锁,因些分析锁冲突时,还需检查sql的执行计划,以确认是否真正使用了索引。

innoDB锁问题

 

4. 间隙锁(next-key锁) 并发下要重点考虑

事务(Transaction)及其ACID属性

  • 原子性(Actomicity):事务是一个原子操作单元,其对数据的修改,要么全都执行,要么全都不执行。
  • 一致性(Consistent):在事务开始和完成时,数据都必须保持一致状态。这意味着所有相关的数据规则都必须应用于事务的修改,以操持完整性;事务结束时,所有的内部数据结构(如B树索引或双向链表)也都必须是正确的。
  • 隔离性(Isolation):数据库系统提供一定的隔离机制,保证事务在不受外部并发操作影响的“独立”环境执行。这意味着事务处理过程中的中间状态对外部是不可见的,反之亦然。
  • 持久性(Durable):事务完成之后,它对于数据的修改是永久性的,即使出现系统故障也能够保持。
  1. mysql> show status like 'innodb_row_lock%';  
  2. ------------------------------- -------   
  3. | Variable_name                 | Value |  
  4. ------------------------------- -------   
  5. | InnoDB_row_lock_current_waits | 0     |  
  6. | InnoDB_row_lock_time          | 0     |  
  7. | InnoDB_row_lock_time_avg      | 0     |  
  8. | InnoDB_row_lock_time_max      | 0     |  
  9. | InnoDB_row_lock_waits         | 0     |  
  10. ------------------------------- -------   
  11. 5 rows in set (0.01 sec)  

         当我们用范围条件而不是相等条件检索数据,并请求共享或排它锁时,innodb会给符合条件的已有数据记录的索引项加锁;对于键值在条件范围内但并不存在的记录就叫做"间隙锁"  比如city表数据分布如下:

并发事务带来的问题

  • 更新丢失(Lost Update):当两个或多个事务选择同一行,然后基于最初选定的值更新该行时,由于每个事务都不知道其他事务的存在,就会发生丢失更新问题——最后的更新覆盖了其他事务所做的更新。例如,两个编辑人员制作了同一文档的电子副本。每个编辑人员独立地更改其副本,然后保存更改后的副本,这样就覆盖了原始文档。最后保存其更改保存其更改副本的编辑人员覆盖另一个编辑人员所做的修改。如果在一个编辑人员完成并提交事务之前,另一个编辑人员不能访问同一文件,则可避免此问题
  • 澳门皇冠金沙网站,脏读(Dirty Reads):一个事务正在对一条记录做修改,在这个事务并提交前,这条记录的数据就处于不一致状态;这时,另一个事务也来读取同一条记录,如果不加控制,第二个事务读取了这些“脏”的数据,并据此做进一步的处理,就会产生未提交的数据依赖关系。这种现象被形象地叫做“脏读”。
  • 不可重复读(Non-Repeatable Reads):一个事务在读取某些数据已经发生了改变、或某些记录已经被删除了!这种现象叫做“不可重复读”。
  • 幻读(Phantom Reads):一个事务按相同的查询条件重新读取以前检索过的数据,却发现其他事务插入了满足其查询条件的新数据,这种现象就称为“幻读”。

如果发现锁争用比较严重,如InnoDB_row_lock_waits和InnoDB_澳门皇冠844网站,row_lock_time_avg的值比较高,还可以通过设置InnoDB Monitors来进一步观察发生锁冲突的表、数据行等,并分析锁争用的原因。具体方法如下:

澳门皇冠金沙网站 1

事务隔离级别

隔离级别 脏读 不可重复读 幻读
未提交读(Read uncommitted)
已提交度(Read committed) x
可重复读(Repeatable read) x x
可序列化(Serializable) x x x

Java代码 

 

mysql行锁的特性

  1. innodb 的行锁是在有索引的情况下,没有索引的表是锁定全表的.
    实例:
    id是主键
    | id| name|
    | -| - |
    | 1 | 1 |
    | 2 | 2 |
    | 3 | 3 |
    事务1update第一条id=1的数据,事务不提交;事务2接着update第二条id=2的数据的时候等待,原因是id没有加上索引,导致事务1锁的是表锁而不是行锁。

  2. 如果是使用相同的索引键,会出现锁冲突。
    示例:tab_with_index表中id字段有索引,name字段没有索引。
    事务1:

select * from tab_with_index where id = 1 and name = '1' for update;

事务2:

select * from tab_with_index where id = 1 and name = '4' for update;

虽然事务2访问的是和事务1不同的记录,但是因为使用了相同的索引,所以需要等待锁。

  1. 当表有多个索引的时候,不同的事务可以使用不同的索引锁定不同的行,另外,不论是使用主键索引、唯一索引或普通索引,InnoDB都会使用行锁来对数据加锁。
    示例:表tab_with_index的id字段有主键索引,name字段有普通索引。
    事务1:
select * from tab_with_index where id = 1 for update;

事务2:

select * from tab_with_index where name = '2' for update;

事务2使用name的索引访问记录,因为记录没有被索引,所以也可以获得锁。

 

   如果查询使用如下sql
  select * from city where city_id>100 for update;

间隙锁(Next-Key锁)

当 我们用范围条件而不是相等条件检索数据,并请求共享或排他锁时,InnoDB会给符合条件 的已有数据记录的索引项加锁;对于键值在条件范围内但并不存在的记录,叫做“间隙(GAP)”,InnoDB也会对这个“间隙”加锁,这种锁机制就是所谓 的间隙锁(Next-Key锁)。
示例:

Select * from  emp where empid > 100 for update;

是一个范围条件的检索,InnoDB不仅会对符合条件的empid值为101的记录加锁,也会对empid大于101(这些记录并不存在)的“间隙”加锁。
InnoDB 使用间隙锁的目的,一方面是为了防止幻读,以满足相关隔离级别的要求,对于上面的例子,要是不使用间隙锁,如果其他事务插入了empid大于100的任何 记录,那么本事务如果再次执行上述语句,就会发生幻读.
还要特别说明的是,InnoDB除了通过范围条件加锁时使用间隙锁外,如果使用相等条件请求给一个不存在的记录加锁,InnoDB也会使用间隙锁!

  1. mysql> CREATE TABLE innodb_monitor(a INT) ENGINE=INNODB;  
  2. Query OK, 0 rows affected (0.14 sec)   

  这就是一个范围条件的检索, innodb不但会对符合条件的101的记录加锁,也会对city_id大于101(虽然记录并不存在)的"间隙"加锁。使用间隙锁的目的是为了防止幻读,以满足相关的隔离级别。关于幻读查看"sql 开发进阶篇系列 6 锁问题(事务与隔离级别介绍)"
很明显,在使用范围条件的检索记录时, 会阻塞符合条件范围内键值的并发插入,往往造成严重的锁等待。在实现业务中尽量使用相等条件来检索数据。还需注意如查使用相等条件检索的数据不存在时,也会加间隙锁。
  为了防止幻读,mysql隔离级别必须是REPEATABLE-READ和Serializable。REPEATABLE-READ也是默认的隔离级别。

一致性非锁定读

一致性非锁定读是指InnoDB存储引擎通过多版本并发控制技术来读取当前数据库的数据。如果当前读取的行正在执行delete或者update操作,这时读取操作不会等行锁的释放,而是去读取行的快照数据。

澳门皇冠金沙网站 2

非锁定一致性读.png

快照数据是指改行之前版本的数据,该实现是通过undo段来实现的,而undo段用来在事务中保存回滚数据,因此使用快照没有增加额外的开销。
这是InnoDB存储引擎的默认读取方式。

 然后就可以用下面的语句来进行查看:

会话1

会话2

SELECT @@tx_isolation

@@tx_isolation

REPEATABLE-READ

SELECT @@tx_isolation

@@tx_isolation

REPEATABLE-READ

SET autocommit=0;

SET autocommit=0;

-- 当前会话对不存在的记录加 for update;

SELECT * FROM city WHERE city_id=102 FOR UPDATE;

 

 

如果这里插入的值>=102就会出现阻塞

INSERT INTO city VALUES(200,2,'江门','005')

错误代码: 1205

Lock wait timeout exceeded; try restarting transaction

 

ROLLBACK;

 

 

INSERT INTO city VALUES(200,2,'江门','005')

共 1 行受到影响

注意

  1. 不同的事务隔离级别下读取的方式不同,并不是每个事务隔离级别下都是采用非锁定的一致性读。四种隔离级别中,READ COMMITTED和REPEATABLE READ这两种隔离级别使用非锁定的一致性读。
  2. 不同的事务即使都使用非锁定的一致性读,但是对于快照数据的定义也各不相同。READ COMMITTED级别下非锁定读总是读取锁定行的最新一份快照数据;而REPEATABLE READ级别下非锁定读总是读取事务开始时的数据版本。

Java代码 

例子

事务A
select * from table where id='1';
.
select * from table where id='1';
.
select * from table where id='1';

上述例子中事务B update以后事务A第一次select的时候RC级别和RR级别获取的结果都是id=1的那一条数据;第二次select的时候,由于事务B已经提交,RC级别select的结果就是id=3,而RR级别读取的是事务开始时的数据,id=1。

 

一致性锁定读

默认配置下事务的隔离级别为REPEATABLE READ,select操作为非一致性锁定读,但某些情况下需要对数据库读取操作进行加锁保证数据的一致性。select 有两种一致的锁定读:

  • select ... for update
  • select ... lock in share mode
  1. mysql> Show innodb statusG;  
  2. *************************** 1. row ***************************  
  3.   Type: InnoDB  
  4.   Name:  
  5. Status:  

自增长与锁

InnoDB存储引擎内部对每个含有自增长列的表有一个自增长计数器,当进行insert操作时,首先获取计数器的最大值,加1后进行insert操作。这个操作会加一个特殊的表锁,AUTO-INC LOCK。这个锁并不是在事务提交后才释放,而是在insert语句执行完后释放。

 监视器可以通过发出下列语句来停止查看:

缺点

虽然是insert后就释放锁,不是事务提交后才释放,但是必须等前一个insert的完成才能进行下一次insert,性能较差。

Java代码 

改进

TODO...

 

外键与锁

在对外键值进行update和insert操作时首先需要查询父表的记录,即select父表,这个select操作不是使用一致性非锁定读,因为会发生数据不一致的问题,为此需要使用一致性锁定读,这时使用的select ... lock in share mode方式。当父表对应记录加X锁后,子表的操作将会阻塞。

  1. mysql> DROP TABLE innodb_monitor;  
  2. Query OK, 0 rows affected (0.05 sec)  

例子

TODO...

 设置监视器后,在SHOW INNODB STATUS的显示内容中,会有详细的当前锁等待的信息,包括表名、锁类型、锁定记录的情况等,便于进行进一步的分析和问题的确定。打开监视器以后,默认情况下每15秒会向日志中记录监控的内容,如果长时间打开会导致.err文件变得非常的巨大,所以用户在确认问题原因之后,要记得删除监控表以关闭监视器,或者通过使用“--console”选项来启动服务器以关闭写日志文件。
InnoDB的行锁模式及加锁方法
InnoDB实现了以下两种类型的行锁。
  共享锁(S):允许一个事务去读一行,阻止其他事务获得相同数据集的排他锁。
  排他锁(X):允许获得排他锁的事务更新数据,阻止其他事务取得相同数据集的共享读锁和排他写锁。
另外,为了允许行锁和表锁共存,实现多粒度锁机制,InnoDB还有两种内部使用的意向锁(Intention Locks),这两种意向锁都是表锁。
  意向共享锁(IS):事务打算给数据行加行共享锁,事务在给一个数据行加共享锁前必须先取得该表的IS锁。
  意向排他锁(IX):事务打算给数据行加行排他锁,事务在给一个数据行加排他锁前必须先取得该表的IX锁。
上述锁模式的兼容情况具体如表20-6所示。

本文由澳门皇冠金沙网站发布于数据库研究,转载请注明出处:并发控制,相同索引键值或同一行或间隙锁的冲