MySQL Innodb表导致死锁日志情况分析与归纳
字体：[ ] 类型：转载 时间：
发现当备份表格的sql语句与删除该表部分数据的sql语句同时运行时，mysql会检测出死锁，并打印出日志
案例描述在定时脚本运行过程中，发现当备份表格的sql语句与删除该表部分数据的sql语句同时运行时，mysql会检测出死锁，并打印出日志。两个sql语句如下：（1）insert into backup_table select * from source_table（2）DELETE FROM source_table WHERE Id&5 AND titleWeight&32768 AND joinTime&'$daysago_1week'teamUser表的表结构如下：PRIMARY KEY (`uid`,`Id`),KEY `k_id_titleWeight_score` (`Id`,`titleWeight`,`score`),ENGINE=InnoDB两语句对source_table表的使用情况如下：
死锁日志打印出的时间点表明，语句（1）运行过程中，当语句（2）开始运行时，发生了死锁。当mysql检测出死锁时，除了查看mysql的日志，还可以通过show InnoDB STATUS \G语句在mysql客户端中查看最近一次的死锁记录。由于打印出来的语句会很乱，所以，最好先使用pager less命令，通过文件内容浏览方式查看结果，会更清晰。（以nopager结束）得到的死锁记录如下：
根据死锁记录的结果，可以看出确实是这两个语句发生了死锁，且锁冲突发生在主键索引上。那么，为什么两个sql语句会存在锁冲突呢？冲突为什么会在主键索引上呢？语句（2）得到了主键索引锁，为什么还会再次申请锁呢？锁冲突分析2.1 innodb的事务与行锁机制MySQL的事务支持不是绑定在MySQL服务器本身，而是与存储引擎相关，MyISAM不支持事务、采用的是表级锁，而InnoDB支持ACID事务、 行级锁、并发。MySQL默认的行为是在每条SQL语句执行后执行一个COMMIT语句，从而有效的将每条语句作为一个单独的事务来处理。2.2 两语句加锁情况在innodb默认的事务隔离级别下，普通的SELECT是不需要加行锁的，但LOCK IN SHARE MODE、FOR UPDATE及高串行化级别中的SELECT都要加锁。有一个例外，此案例中，语句（1）insert into teamUser_ select * from teamUser会对表teamUser_（ENGINE= MyISAM）加表锁，并对teamUser表所有行的主键索引（即聚簇索引）加共享锁。默认对其使用主键索引。而语句（2）DELETE FROM teamUser WHERE teamId=$teamId AND titleWeight&32768 AND joinTime&'$daysago_1week'为删除操作，会对选中行的主键索引加排他锁。由于此语句还使用了非聚簇索引KEY `k_teamid_titleWeight_score` (`teamId`,`titleWeight`,`score`)的前缀索引，于是，还会对相关行的此非聚簇索引加排他锁。2.3 锁冲突的产生由于共享锁与排他锁是互斥的，当一方拥有了某行记录的排他锁后，另一方就不能其拥有共享锁，同样，一方拥有了其共享锁后，另一方也无法得到其排他锁。所 以，当语句（1）、（2）同时运行时，相当于两个事务会同时申请某相同记录行的锁资源，于是会产生锁冲突。由于两个事务都会申请主键索引，锁冲突只会发生 在主键索引上。常常看到一句话：在InnoDB中，除单个SQL组成的事务外，锁是逐步获得的。那就说明，单个SQL组成的事务锁是一次获得的。而此案例中，语句（2） 已经得到了主键索引的排他锁，为什么还会申请主键索引的排他锁呢？同理，语句（1）已经获得了主键索引的共享锁，为什么还会申请主键索引的共享锁呢？死锁记录中，事务一等待锁的page no与事务二持有锁的page no相同，均为218436，这又代表什么呢？我们的猜想是，innodb存储引擎中获得行锁是逐行获得的，并不是一次获得的。下面来证明。死锁产生过程分析要想知道innodb加锁的过程，唯一的方式就是运行mysql的debug版本，从gdb的输出中找到结果。根据gdb的结果得到，单个SQL组成的事 务，从宏观上来看，锁是在这个语句上一次获得的，但从底层实现上来看，是逐个记录行查询，得到符合条件的记录即对该行记录的索引加锁。Gdb结果演示如下： 代码如下:(gdb) b lock_rec_lock  Breakpoint 1 at 0×867120: file lock/lock0lock.c, line 2070.  (gdb) c  Continuing.  [Switching to Thread
(LWP 5540)]  Breakpoint 1, lock_rec_lock (impl=0, mode=5, rec=0x2aedbe01c1 “789\200″, index=0x2aada734b8, thr=0x2aada74c18) at lock/lock0lock.c:2070 &#6 {  Current language: currently c  (gdb) c  Continuing.  Breakpoint 1, lock_rec_lock (impl=0, mode=1029, rec=0x2aedbc80ba “\200″, index=0x2aada730b8, thr=0x2aada74c18) at lock/lock0lock.c:2070 &#6 {  (gdb) c  Continuing.  Breakpoint 1, lock_rec_lock (impl=0, mode=5, rec=0x2aedbe01cf “789\200″, index=0x2aada734b8, thr=0x2aada74c18) at lock/lock0lock.c:2070 &#6 {  (gdb) c  Continuing. （说明：”789\200″为非聚簇索引，”\200″为主键索引）
Gdb结果显示，语句（1）（2）加锁的获取记录为多行，即逐行获得锁，这样就解释了语句（2）获得了主键索引锁还再次申请主键索引锁的情况。由于语句（1）使用了主键索引，而语句（2）使用了非聚簇索引，两个事务获得记录行的顺序不同，而加锁的过程是边查边加、逐行获得，于是，就会出现如下情况：
于是，两个事务分别拥有部分锁并等待被对方持有的锁，出现这种资源循环等待的情况，即死锁。此案例中被检测时候的锁冲突就发现在page no为8103的锁上。InnoDB 会自动检测一个事务的死锁并回滚一个或多个事务来防止死锁。Innodb会选择代价比较小的事务回滚，此次事务（1）解锁并回滚，语句（2）继续运行直至事务结束。innodb死锁形式归纳死锁产生的四要素：互斥条件：一个资源每次只能被一个进程使用；请求与保持条件：一个进程因请求资源而阻塞时，对已获得的资源保持不放；不剥夺条件:进程 已获得的资源，在末使用完之前，不能强行剥夺；循环等待条件:若干进程之间形成一种头尾相接的循环等待资源关系。Innodb检测死锁有两种情况，一种是满足循环等待条件，还有另一种策略：锁结构超过mysql配置中设置的最大数量或锁的遍历深度超过设置的最大深度 时，innodb也会判断为死锁（这是提高性能方面的考虑，避免事务一次占用太多的资源）。这里，我们只考虑满足死锁四要素的情况。死锁的形式是多样的，但分析到innodb加锁情况的最底层，因循环等待条件而产生的死锁只有可能是四种形式：两张表两行记录交叉申请互斥锁、同一张表则存在主键索引锁冲突、主键索引锁与非聚簇索引锁冲突、锁升级导致的锁等待队列阻塞。以下首先介绍innodb聚簇索引与非聚簇索引的数据存储形式，再以事例的方式解释这四种死锁情况。4.1聚簇索引与非聚簇索引介绍聚簇索引即主键索引，是一种对磁盘上实际数据重新组织以按指定的一个或多个列的值排序，聚簇索引的索引页面指针指向数据页面。非聚簇索引（即第二主键索 引）不重新组织表中的数据，索引顺序与数据物理排列顺序无关。索引通常是通过B-Tree数据结构来描述，那么，聚簇索引的叶节点就是数据节点，而非聚簇 索引的叶节点仍然是索引节点，通常是一个指针指向对应的数据块。而innodb在非聚簇索引叶子节点包含了主键值作为指针。（这样是为了减少在移动行或数据分页时索引的维护工作。）其结构图如下：
当使用非聚簇索引时，会根据得到的主键值遍历聚簇索引，得到相应的记录。4.2四种死锁情况在InnoDB中，使用行锁机制，于是，锁通常是逐步获得的，这就决定了在InnoDB中发生死锁是可能的。即将分享的四种死锁的锁冲突分别是：不同表的相同记录行索引锁冲突、主键索引锁冲突、主键索引锁与非聚簇索引锁冲突、锁升级造成锁队列阻塞。不同表的相同记录行锁冲突案例：两个表、两行记录，交叉获得和申请互斥锁
条件：A、 两事务分别操作两个表、相同表的同一行记录B、 申请的锁互斥C、 申请的顺序不一致
主键索引锁冲突案例：本文案例，产生冲突在主键索引锁上条件：A、 两sql语句即两事务操作同一个表、使用不同索引B、 申请的锁互斥C、 操作多行记录D、 查找到记录的顺序不一致
主键索引锁与非聚簇索引锁冲突案例：同一行记录，两事务使用不同的索引进行更新操作
此案例涉及TSK_TASK表，该表相关字段及索引如下：ID：主键；MON_TIME：监测时间；STATUS_ID：任务状态；索引：KEY_TSKTASK_MONTIME2 (STATUS_ID, MON_TIME)。
条件：A、 两事务使用不同索引B、 申请的锁互斥C、 操作同一行记录
当执行update、delete操作时，会修改表中的数据信息。由于innodb存储引擎中索引的数据存储结构，会根据修改语句使用的索引以及修改信息 的不同执行不同的加锁顺序。当使用索引进行查找并修改记录时，会首先加使用的索引锁，然后，如果修改了主键信息，会加主键索引锁和所有非聚簇索引锁，修改 了非聚簇索引列值会加该种非聚簇索引锁。此案例中，事务一使用非聚簇索引查找并修改主键值，事务二使用主键索引查找并修改主键值，加锁顺序不同，导致同时运行时产生资源循环等待。锁升级造成锁队列阻塞案例：同一行记录，事务内进行锁升级，与另一等待锁发送锁队列阻塞，导致死锁
条件：A、 两事务操作同一行记录B、 一事务对某一记录先申请共享锁，再升级为排他锁C、 另一事务在过程中申请这一记录的排他锁
避免死锁的方法InnoDB给MySQL提供了具有提交，回滚和崩溃恢复能力的事务安全（ACID兼容）存储引擎。InnoDB锁定在行级并且也在SELECT语句提供非锁定读。这些特色增加了多用户部署和性能。但其行锁的机制也带来了产生死锁的风险，这就需要在应用程序设计时避免死锁的发生。以单个SQL语句组成的隐式事务来说，建议的避免死锁的方法如下：1.如果使用insert…select语句备份表格且数据量较大，在单独的时间点操作，避免与其他sql语句争夺资源，或使用select into outfile加上load data infile代替 insert…select，这样不仅快，而且不会要求锁定2. 一个锁定记录集的事务，其操作结果集应尽量简短，以免一次占用太多资源，与其他事务处理的记录冲突。3.更新或者删除表格数据，sql语句的where条件都是主键或都是索引，避免两种情况交叉，造成死锁。对于where子句较复杂的情况，将其单独通过sql得到后，再在更新语句中使用。4. sql语句的嵌套表格不要太多，能拆分就拆分，避免占有资源同时等待资源，导致与其他事务冲突。5. 对定点运行脚本的情况，避免在同一时间点运行多个对同一表进行读写的脚本，特别注意加锁且操作数据量比较大的语句。6.应用程序中增加对死锁的判断，如果事务意外结束，重新运行该事务，减少对功能的影响。
您可能感兴趣的文章:
大家感兴趣的内容
12345678910
最近更新的内容
常用在线小工具mysql中insert...select引发的死锁 - CSDN博客
mysql中insert...select引发的死锁
mysql5.6用户手册
作为mysql的新手,被最近在项目中碰到的死锁问题吓了一跳,赶紧记下来备忘.
使用show engine innodb status命令查看到的死锁相关信息如下:
(在navicat中执行该命令时会看到status列的值为空,此时别忘了右键全选-复制)
------------------------
LATEST DETECTED DEADLOCK
------------------------
09:16:01 7f1e2c554700TOO DEEP OR LONG SEARCH IN THE LOCK TABLE WAITS-FOR GRAPH, WE WILL ROLL BACK FOLLOWING TRANSACTION
*** TRANSACTION:
TRANSACTION 649212, ACTIVE 0 sec setting auto-inc lock
mysql tables in use 2, locked 2
4 lock struct(s), heap size 1184, 2 row lock(s)
MySQL thread id 138630, OS thread handle 0x7f1e2c554700, query id .132.43.55 iapproxy Sending data
INSERT INTO tsp_agt_msg_send (
&&&&&& ...&
&&&&&&&&& 'c1',
&&&&&&&&&&'c2',&
&&&&&&&&& 'c3',&
&&&&&&&&& ...
*** WAITING FOR THIS LOCK TO BE GRANTED:
TABLE LOCK table `diap_proxy`.`tsp_agt_msg_send` trx id 649212 lock mode AUTO-INC waiting
*** WE ROLL BACK TRANSACTION (2)
------------
TRANSACTIONS
------------
Trx id counter 695842
Purge done for trx's n:o & 695818 undo n:o & 0 state: running but idle
History list length 3160
LIST OF TRANSACTIONS FOR EACH SESSION:
---TRANSACTION 695841, not started
MySQL thread id 138912, OS thread handle 0x7f1e1f5d7700, query id .168.180.54 root cleaning up
---TRANSACTION 0, not started
MySQL thread id 138901, OS thread handle 0x7f1e17a62700, query id .132.137.206 root cleaning up
---TRANSACTION 0, not started
MySQL thread id 138875, OS thread handle 0x7f1e1feba700, query id .132.137.206 root init
show engine innodb status
---TRANSACTION 695828, not started
MySQL thread id 138874, OS thread handle 0x7f1e1dd77700, query id .168.180.54 root cleaning up
---TRANSACTION 692142, not started
MySQL thread id 138843, OS thread handle 0x7f1e1ee39700, query id .168.180.54 root cleaning up
---TRANSACTION 673389, not started
首先来看下这一行
09:16:01 7f1e2c554700TOO DEEP OR LONG SEARCH IN THE LOCK TABLE WAITS-FOR GRAPH, WE WILL ROLL BACK FOLLOWING TRANSACTION
这里涉及到一个死锁判定的规则:
在innodb源代码lock/lock0lock.c文件中,定义了两个常量:
/* Restricts the length of search we will do in the waits-for
graph of transactions */
#define LOCK_MAX_N_STEPS_IN_DEADLOCK_CHECK 1000000
/* Restricts the recursion depth of the search we will do in the waits-for
graph of transactions */
#define LOCK_MAX_DEPTH_IN_DEADLOCK_CHECK 200
然后在检查是否产生死锁的函数lock_deadlock_occurs()中有如下代码:
ret = lock_deadlock_recursive(trx, trx, lock, &cost, 0);
switch (ret) {
case LOCK_EXCEED_MAX_DEPTH:
其中的lock_deadlock_recursive()函数是递归函数,它会检查自身递归深度,其中有如下代码:
= depth & LOCK_MAX_DEPTH_IN_DEADLOCK_CHECK
|| *cost & LOCK_MAX_N_STEPS_IN_DEADLOCK_CHECK;
if (too_far) {
return(LOCK_EXCEED_MAX_DEPTH);
因此innodb在检查是否产生死锁时调用lock_deadlock_occurs()检查,这个函数再会调用lock_deadlock_recursive()递归检查锁的数目(不知道这么说是否确切?)，当递归的深度depth大于了一开始介绍的常量LOCK_MAX_DEPTH_IN_DEADLOCK_CHECK,或者cost(不清楚这个代表什么)大于一开始介绍的常量LOCK_MAX_N_STEPS_IN_DEADLOCK_CHECK时,就认为发生了死锁.
如上所述这个死锁并非数据库真正发生了死锁,mysql主观的认为发生了死锁.
下面继续分析死锁日志,注意如下两行,
*** WAITING FOR THIS LOCK TO BE GRANTED:
TABLE LOCK table `diap_proxy`.`tsp_agt_msg_send` trx id 649212 lock mode AUTO-INC waiting
从5.6的用户手册中查找到AUTO-INC的相关信息:
&InnoDB uses a special lock called the table-level AUTO-INC lock for inserts into tables with AUTO_INCREMENT columns. This lock is normally held to the end of the statement (not to the end of the transaction), to ensure that auto-increment numbers are assigned
in a predictable and repeatable order for a given sequence of INSERT statements
InnoDB在为自增列产生值的时候,使用一种叫做AUTO_INC的表级锁来做控制.这种锁是作用于语句的而不是事务(即语句执行完了锁就会被释放).使用这种锁是为了确保自增列的值的可预见性和可重复性.可预见性是说当一条insert语句作用于多行时,这些行的自增列基于第一行来说是可预见的;可重复执行是指基于语句的复制在slave重放时自增列的值与master的一致.
mysql提供参数innodb_autoinc_lock_mode来控制在产生自增列时锁的行为,可取值为0,1,2默认为1
0:对于每一个insert操作,都加AUTO_INC锁来为自增列分配值.
1:对于简单的insert操作,不加AUTO_INC锁,而使用一个轻量级的mutex,分配完毕后立即释放,不需要等到语句结束.
对于批量的insert操作,加AUTO_INC锁.当简单的insert操作检测到其他的事务持有AUTO_INC锁时,也需要等待AUTO_INC锁.
2.从不加AUTO_INC锁,并发性最好,但基于语句的复制和恢复将有问题.
insert操作有INSERT, INSERT ... SELECT, REPLACE, REPLACE ... SELECT, and LOAD DATA.(凡是能产生新的一行的语句都是)
简单的insert语句:
在语句正式执行前,所作用的行数是确定的.比如insert子句,不包含子查询的replace子句.
批量插入的sql语句:
包括INSERT ... SELECT, REPLACE ... SELECT和LOAD DATA
混合型的sql语句:
INSERT INTO t1 (c1,c2) VALUES (1,'a'), (NULL,'b'), (5,'c'), (NULL,'d');即包含指定的又包含需要mysql帮忙自增的.
从以上分析可以得出,在innodb_autoinc_lock_mode=1的情况下insert...select语句会导致自增列的赋值需要加AUTO_INC锁.当在并发作insert的情况下会导致上述死锁.
总结: 在高并发的情况下对有自增列的表做插入操作应避免使用批量insert语句.
本文已收录于以下专栏：
相关文章推荐
系统中需要批量生成单据数据到数据库表，所以采用批量插入数据库的方式。由于系统中ORM操作集成使用的是Mybatis来完成的。在实际生产中发现，使用Mybatis批量插入调用的效率并不高，于是我们迫切地...
/database/062.html
http://www.itnose.net/detail/6399027.html
一.mysql的死锁： 是指两个或两个以上的进程在执行过程中,因争夺资源而造成的一种互相等待的现象,若无外力作用,它们都将无法推进下去。
表级锁不会产生死锁.所以解决死锁主要还是针对于最常用的Inno...
上周遇到一个因insert而引发的死锁问题，其成因比较令人费解。
于是想要了解一下insert加锁机制，但是发现网上介绍的文章比较少且零散，挖掘过程比较忙乱。
本以为只需要系统...
Mysql有很多坑，对Mysql多线程支持这块不是很熟的话就会莫名其妙地发生一些诡异的问题。多线程线程并发操作时最容易产生死锁问题。所以很多大数据的操作一般都采用NoSQL数据库方案来处理，或者读写分...
mysql中 insert …select …带来的问题
当使用insert...select...进行记录的插入时，如果select的表是innodb类型的，不论insert的表是什么类型...
Mysql并发时经典常见的死锁原因及解决方法
1、MySQL常用存储引擎的锁机制：
   MyISAM和MEMORY采用表级锁(table-level locking)
   BDB采用页面锁(page-level locking)或表级锁，...
Insert是T-sql中常用语句，Insert INTO table(field1,field2,...) values(value1,value2,...)这种形式的在应用程序开发中必不可少。但我...
平常习惯生产数据的时候，直接用insert into ...select这种语法，结果今天需要的数据量稍微一大就出错了，错误信息如下：
mysql& insert into t1 select nu...
他的最新文章
您举报文章：
举报原因：
原文地址：
原因补充：
(最多只允许输入30个字)MySQL insert into ... select 的锁情况 【转】 - CSDN博客
MySQL insert into ... select 的锁情况 【转】
文章来源：
&&&&& 一直以为&insert into tb select * from tbx& 这样的导入操作是会把tbx表给锁住的，在锁期间是不允许任何操作（保证一致性）。看完写的之后，发现其实我错了一半。tbx表是会被锁住，但这个锁有2种情况，现在逐一进行分析：
root@127.0.0.1 : test 02:10:40&select @@global.tx_isolation,@@session.tx_
+-----------------------+------------------------+
| @@global.tx_isolation | @@session.tx_isolation |
+-----------------------+------------------------+
| REPEATABLE-READ
| REPEATABLE-READ
+-----------------------+------------------------+
1 row in set (0.00 sec)
root@127.0.0.1 : test 02:10:50&select @@version;
+------------+
| @@version
+------------+
| 5.6.10-log |
+------------+
1 row in set (0.00 sec)
1：按照主键排序插入的情况
直接插入，不加排序字段（默认）：
session1：执行操作,表只有5W条记录
root@127.0.0.1 : test 02:10:51&insert into uu select * from
session2：查看操作锁的情况（锁的行数）
root@127.0.0.1 : (none) 02:13:30&pager grep &lock(s)&
PAGER set to 'grep &lock(s)&'root@127.0.0.1 : (none) 02:18:08&show
#被锁的行数逐步增加
274 lock struct(s), heap size 31160, 17746 row lock(s), undo log entries 17474
root@127.0.0.1 : (none) 02:18:16&show
500 lock struct(s), heap size 63928, 32572 row lock(s), undo log entries 32074
root@127.0.0.1 : (none) 02:18:17&show
676 lock struct(s), heap size 80312, 44308 row lock(s), undo log entries 43635
用主键升序插入：
& 情况和1一样。即默认的&select * from tb& 和 &select * from tb order id(PK) ASC & 是一样的情况。
用主键倒序插入：
& 情况和1一样。即默认的&select * from tb& 和 &select * from tb order id(PK) DESC& 是一样的情况，这里说的一样是锁方式一样（都是逐步，只是顺序不一样）。
从上面可知：通过主键排序或则不加排序字段的导入操作&insert into tb select * from tbx&，是会锁tbx表，但他的锁是逐步地锁定已经扫描过的记录。
2：按照非主键排序插入的情况
session1：执行操作
root@127.0.0.1 : test 02:33:00&insert into uu select * from user order by createT
session2：查看操作锁的情况（行数）
root@127.0.0.1 : (none) 02:27:29&pager grep &lock(s)&
root@127.0.0.1 : (none) 02:27:54&show
#被锁的行数一样，不变（整张表）
773 lock struct(s), heap size 80312, 50771 row lock(s), undo log entries 1843root@127.0.0.1 : (none) 02:33:19&show
773 lock struct(s), heap size 80312, 50771 row lock(s), undo log entries 17680root@127.0.0.1 : (none) 02:33:20&show
773 lock struct(s), heap size 80312, 50771 row lock(s), undo log entries 22260root@127.0.0.1 : (none) 02:33:21&show
773 lock struct(s), heap size 80312, 50771 row lock(s), undo log entries 28960
从上面可知：通过非主键排序的导入操作&insert into tb select * from tbx&，是会锁tbx表，但他的锁是一开始就会锁定整张表。
总之，&insert into tb select * from tbx& 的导入操作是会锁定原表，但是锁是有2种情况：“逐步锁”，“全锁”。
针对1的情况：逐步锁定扫描过的记录，那操作未扫描的数据会怎么样？
session1：执行操作
root@127.0.0.1 : test 02:55:27&insert into uu select * from
Query OK, 49998 rows affected (9.06 sec)
session2：测试操作锁的情况
root@127.0.0.1 : test 02:54:49&delete from user where id = 33333;update user set username='TEST' where id = 44444;insert into user(id,username,company) values(1000,'ASD','ABCASDA');
Query OK, 0 rows affected (0.00 sec) #可以删除未扫描（锁）的数据(id=33333)
Query OK, 0 rows affected (0.00 sec)
Rows matched: 1
Changed: 0
Warnings: 0 #可以更新为扫描（锁）的数据(id=44444)
Query OK, 1 row affected (8.09 sec)#插入（更新，删除）操作被锁了，因为该记录已经被扫描到(id=1000)
session3：查看操作的锁情况：
root@127.0.0.1 : (none) 02:55:33&show
LOCK WAIT 2 lock struct(s), heap size 376, 1 row lock(s)
272 lock struct(s), heap size 31160, 17574 row lock(s), undo log entries 17305
1 row in set (0.09 sec)
root@127.0.0.1 : (none) 02:55:35&show
LOCK WAIT 2 lock struct(s), heap size 376, 1 row lock(s)
448 lock struct(s), heap size 47544, 29109 row lock(s), undo log entries 28664
1 row in set (0.01 sec)
root@127.0.0.1 : (none) 02:55:37&show
LOCK WAIT 2 lock struct(s), heap size 376, 1 row lock(s)
612 lock struct(s), heap size 63928, 40034 row lock(s), undo log entries 39425
1 row in set (0.00 sec)
root@127.0.0.1 : (none) 02:55:39&show
1 row in set (0.01 sec)
从上面看出，刚好说明了1的情况：逐步的锁定已经扫描过的记录。
默认、主键升序的select ：从第一行开始扫描到最后，即第一行开始锁直到最后。
主键倒序select&&&&&&&&&&&& ：从最后一行开始扫描到最前，即最后一行开始锁直到第一行。
针对2的情况：锁定整张表，那就是表锁；不能进行任何操作，直到锁释放了？
session1：执行操作
root@127.0.0.1 : test 03:23:06&insert into uu select * from user order by
Query OK, 49994 rows affected (13.70 sec)
session2：测试操作锁的情况
root@127.0.0.1 : test 03:22:44&delete from user where id = 33337;update user set username='TESAAST' where id = 44443;insert into user(id,username,company) values(1000,'ASD','ABCASDA');
Query OK, 1 row affected (9.58 sec)
#直接被锁住了，等待session1释放了。
Query OK, 0 rows affected (0.00 sec)
Rows matched: 1
Changed: 0
Warnings: 0 #同上
Query OK, 1 row affected (0.00 sec) #同上
session3：查看操作的锁情况：
root@127.0.0.1 : (none) 03:22:45&pager grep &lock(s)&
PAGER set to 'grep &lock(s)&'
root@127.0.0.1 : (none) 03:23:20&show
773 lock struct(s), heap size 80312, 50765 row lock(s), undo log entries 4433
1 row in set (0.02 sec)
root@127.0.0.1 : (none) 03:23:28&show
LOCK WAIT 2 lock struct(s), heap size 376, 1 row lock(s)
773 lock struct(s), heap size 80312, 50765 row lock(s), undo log entries 25383
1 row in set (0.06 sec)
root@127.0.0.1 : (none) 03:23:32&show
LOCK WAIT 2 lock struct(s), heap size 376, 1 row lock(s)
773 lock struct(s), heap size 80312, 50765 row lock(s), undo log entries 42464
1 row in set (0.01 sec)
从上面看出，刚好说明了2的情况：一开始就会锁定整张表的记录，不能进行任何操作，直到锁释放了。
类似&insert into tb select * from tbx& 的操作，最好确保tbx表不被做dml操作，不然很可能出现锁等待的情况。另：通过设置隔离级别：read committed & ROW(binlog_format)可以让dml和该语句并发操作。
session1：执行操作
root@127.0.0.1 : test 04:05:08&insert into uu select * from user order by
Query OK, 49990 rows affected (14.09 sec)
session2：测试操作锁的情况
root@127.0.0.1 : test 04:04:57&delete from user where id = 33318;update user set username='TESAAeST' where id = 44423;insert into user(id,username,company) values(1000,'ASD','ABCASDA');
Query OK, 0 rows affected (0.00 sec)
Query OK, 0 rows affected (0.00 sec)
Rows matched: 1
Changed: 0
Warnings: 0
Query OK, 1 row affected (0.00 sec)
session3：查看操作的锁情况：
root@127.0.0.1 : (none) 03:22:45&pager grep &lock(s)&
PAGER set to 'grep &lock(s)&'
root@127.0.0.1 : test 04:05:23&show
1 lock struct(s), heap size 376, 0 row lock(s), undo log entries 6256
1 row in set (0.05 sec)
root@127.0.0.1 : test 04:05:28&show
1 lock struct(s), heap size 376, 0 row lock(s), undo log entries 32958
1 row in set (0.01 sec)
root@127.0.0.1 : test 04:05:35&show
1 lock struct(s), heap size 376, 0 row lock(s), undo log entries 33784
1 row in set (0.00 sec)
root@127.0.0.1 : test 04:05:36&show
1 lock struct(s), heap size 376, 0 row lock(s), undo log entries 34789
1 row in set (0.00 sec)
###########################################
附加内容：
内容来源：
本文已收录于以下专栏：
相关文章推荐
1、binlog format 启用Row Based Replication（行复制）模式：
SET GLOBAL binlog_format = 'ROW';
如果你想永久的启用这...
情景一：insert into table1 ...select * from table2：table1锁表，table2逐步锁（扫描一个锁一个）
情景二：insert into table1 ....
mysql[192.168.11.187]
processid[249] root@localhost in db[zjzc] hold
transaction time 197
1120698...
锁是计算机协调多个进程或线程并发访问某一资源的机制。在数据库中，除传统的计算资源（如CPU、RAM、I/O等）的争用以外，数据也是一种供许多用户共享的资源。如何保证数据并发访问的一致性、有效性是所有数...
mysql中 insert …select …带来的问题
当使用insert...select...进行记录的插入时，如果select的表是innodb类型的，不论insert的表是什么类型...
原文地址：/?p=771
1    背景    1
1.1    MVCC：Snapshot
可直接在mysql命令行执行：show engine innodb status\G;
查看造成死锁的sql语句，分析索引情况，然后优化sql然后
show sta...
关于Mysql innodb Insert into 加锁的机制的文章网上很少，个人对于insert 的加锁机制比较感兴趣，所以通过此wiki对研究的过程做个总结，如有不对的地方，欢迎指正。
上周遇到一个因insert而引发的死锁问题，其成因比较令人费解。
于是想要了解一下insert加锁机制，但是发现网上介绍的文章比较少且零散，挖掘过程比较忙乱。
本以为只需要系统...
如何在MySQL从多个表中组合字段然后插入到一个新表中，通过一条sql语句实现。具体情形是：有三张表a、b、c，现在需要从表b和表c中分别查几个字段的值插入到表a中对应的字段。对于这种情况，我们可以使...
他的最新文章
您举报文章：
举报原因：
原文地址：
原因补充：
(最多只允许输入30个字)