mysql5.7从锁表吗,MySQL5.7 锁详解

1.锁特定比较

锁/存储引擎

MyISAM

InnoDB

特点

行锁

×

√

加锁快，开销小；不会死锁；锁颗粒度大，锁阻塞概率高，并发小。

表锁

√

√

加锁慢，开销大；可能死锁；锁颗粒度小，锁阻塞概率小，并发高。

2.MyISAM表锁机制

表共享读锁(Table Read Lock)，MyISAM读锁不会阻塞同一表的读请求，但会阻塞对同一表的写请求。

表独占写锁(Table Write Lock)，MyISAM写锁则会阻塞同一表的读写请求，所以，读、写请求是串行的。

3.InnoDB的行锁机制，参考： 官方文档

3.1 InnoDB实现了以下两种类型的行锁。

共享锁(S)：即读锁，允许一个事务去读同一行，阻止其他事务获得相同数据集的排他锁。SELECT * FROM table_name WHERE ... LOCK IN SHARE MODE

排他锁(X)：即写锁，允许获得排他锁的事务更新数据，阻止其他事务取得相同数据集的共享读锁和排他写锁。SELECT * FROM table_name WHERE ... FOR UPDATE。

另外，为了允许行锁和表锁共存，实现多粒度锁机制，InnoDB还有两种内部使用的意向锁(Intention Locks)，这两种意向锁都是表锁。

意向共享锁(IS)：事务打算给数据行加行共享锁，事务在给一个数据行加共享锁前必须先取得该表的IS锁。

意向排他锁(IX)：事务打算给数据行加行排他锁，事务在给一个数据行加排他锁前必须先取得该表的IX锁。

意向锁是InnoDB自动加的，不需用户干预。对于UPDATE、DELETE和INSERT语句，InnoDB会自动给涉及数据集加排他锁(X)；对于普通SELECT语句，InnoDB不会加任何锁；事务可以通过以下语句显示给记录集加共享锁或排他锁。

InnoDB锁的互斥与兼容关系

锁类型

排他锁(X)

共享锁(S)

意向排他锁(IX)

意向共享锁(IS)

排他锁(X)

×

×

×

×

共享锁(S)

×

√

×

√

意向排他锁(IX)

×

×

√

√

意向共享锁(IS)

×

√

√

√

3.2 InnoDB锁实现方式

InnoDB行锁是通过给索引上的索引项加锁 来实现的，这一点MySQL与Oracle不同，后者是通过在数据块中对相应数据行加锁来实现的。

InnoDB这种行锁实现特点意味着：只有通过索引条件检索数据，InnoDB才使用行级锁，否则，InnoDB将使用表锁！

笔者测试发现，使用MySQL5.6和5.7版本的服务器，即使update不用索引，MySQL使用也是行锁。

0.修改锁超时时间为1小时，默认为50秒

> show global variables like 'innodb_lock_wait_timeout';

> set global innodb_lock_wait_timeout=3600;

1.挂起一个事务

> START TRANSACTION;

> UPDATE tbl_order set Prdclass = '11111' WHERE imei = '863396023884685';

2. 对同一条记录发请求

UPDATE tbl_order set Prdclass = '22222' WHERE imei = '863396023884685';

3.show processlist

mysql> show processlist;

+----+-------------+--------------------+-----------+---------+------+--------------------------------------------------------+------------------------------------------------------------------------+

| Id | User | Host | db | Command | Time | State | Info |

+----+-------------+--------------------+-----------+---------+------+--------------------------------------------------------+------------------------------------------------------------------------+ |

| 16 | root | 10.206.16.88:61968 | latest_db | Query | 30 | updating | UPDATE tbl_order set Prdclass = '3333' WHERE imei = '863396023884685' |

+----+-------------+--------------------+-----------+---------+------+--------------------------------------------------------+------------------------------------------------------------------------+

8 rows in set (0.00 sec)

发现有第二条更新语句已经挂起了。

4. 查询innodb锁

mysql> SELECT * FROM information_schema.INNODB_LOCKS;

+---------------------+-------------+-----------+-----------+-------------------------+------------+------------+-----------+----------+-----------+

| lock_id | lock_trx_id | lock_mode | lock_type | lock_table | lock_index | lock_space | lock_page | lock_rec | lock_data |

+---------------------+-------------+-----------+-----------+-------------------------+------------+------------+-----------+----------+-----------+

| 405729757:1226:4:88 | 405729757 | X | RECORD | `latest_db`.`tbl_order` | PRIMARY | 1226 | 4 | 88 | 19 |

| 405729754:1226:4:88 | 405729754 | X | RECORD | `latest_db`.`tbl_order` | PRIMARY | 1226 | 4 | 88 | 19 |

+---------------------+-------------+-----------+-----------+-------------------------+------------+------------+-----------+----------+-----------+

2 rows in set, 1 warning (0.01 sec)

发现此时的两条记录的锁类型均为RECORD，即基于记录的。锁模式：排他锁(X)

mysql> SELECT * FROM information_schema.INNODB_LOCK_WAITS;

+-------------------+---------------------+-----------------+---------------------+

| requesting_trx_id | requested_lock_id | blocking_trx_id | blocking_lock_id |

+-------------------+---------------------+-----------------+---------------------+

| 405729757 | 405729757:1226:4:88 | 405729754 | 405729754:1226:4:88 |

+-------------------+---------------------+-----------------+---------------------+

1 row in set, 1 warning (0.00 sec)

上表展示了锁的阻塞管理，事务405729754 阻塞了事物405729757的操作。

mysql> SELECT * FROM information_schema.INNODB_TRX\G;

*************************** 1. row ***************************

trx_id: 405729757

trx_state: LOCK WAIT

trx_started: 2018-10-16 11:55:28

trx_requested_lock_id: 405729757:1226:4:88

trx_wait_started: 2018-10-16 11:55:28

trx_weight: 2

trx_mysql_thread_id: 16

trx_query: UPDATE tbl_order set Prdclass = '3333' WHERE imei = '863396023884685'

trx_operation_state: starting index read

trx_tables_in_use: 1

trx_tables_locked: 1

trx_lock_structs: 2

trx_lock_memory_bytes: 1136

trx_rows_locked: 1

trx_rows_modified: 0

trx_concurrency_tickets: 0

trx_isolation_level: REPEATABLE READ

trx_unique_checks: 1

trx_foreign_key_checks: 1

trx_last_foreign_key_error: NULL

trx_adaptive_hash_latched: 0

trx_adaptive_hash_timeout: 0

trx_is_read_only: 0

trx_autocommit_non_locking: 0

*************************** 2. row ***************************

trx_id: 405729754

trx_state: RUNNING

trx_started: 2018-10-16 11:55:06

trx_requested_lock_id: NULL

trx_wait_started: NULL

trx_weight: 86912

trx_mysql_thread_id: 13

trx_query: NULL

trx_operation_state: NULL

trx_tables_in_use: 0

trx_tables_locked: 1

trx_lock_structs: 86911

trx_lock_memory_bytes: 8429776

trx_rows_locked: 6552922

trx_rows_modified: 1

trx_concurrency_tickets: 0

trx_isolation_level: REPEATABLE READ

trx_unique_checks: 1

trx_foreign_key_checks: 1

trx_last_foreign_key_error: NULL

trx_adaptive_hash_latched: 0

trx_adaptive_hash_timeout: 0

trx_is_read_only: 0

trx_autocommit_non_locking: 0

2 rows in set (0.01 sec)

ERROR:

No query specified

>commit

对此，官方给出解释如下：

Record locks always lock index records, even if a table is defined with no indexes. For such cases, InnoDB creates a hidden clustered index and uses this index for record locking. See Section 14.8.2.1, “Clustered and Secondary Indexes”.

也就是说即使没有索引，innodb引擎也会选择基于索引的行锁，原因在于，innodb会默认创建clustered index，即聚簇索引。其创建规则如下：

如果表中有主键，则使用主键作为聚簇索引。

如果表中没有主键，则选择最前面一个唯一性索引作为聚簇索引。

如果即没有主键也没有唯一性索引，InnoDB会用rowId创建一列隐藏的列，名称叫做“GEN_CLUST_INDEX”，作为聚簇索引使用。

聚簇索引之所以查询快？

聚簇索引并不是一种单独的索引类型，而是一种数据存储方式。具体的细节依赖于其实现方式，但InnoDB的聚簇索引实际上在同一个结构中保存了B-Tree索引和数据行。

当表有聚簇索引时，他的数据行实际上存放在索引的叶子页(leaf page)中。术语 “聚簇”表示数据行和相邻的键值紧凑地存储在一起(这并非总成立)。

mysql中每个表都有一个聚簇索引(clustered index )，除此之外的表上的每个非聚簇索引都是二级索引，又叫辅助索引(secondary indexes)。

3.3 InnoDB间隙(Gap)锁、Next-key锁(行锁和间隙锁叠加)

使用范围更新时，如: between 10 and 20，则10~20范围内的数据都被上锁，如果此时插入15的数据，则会阻塞。

在一个事务未提交前，其他并发事务不能插入满足其锁定条件的任何记录，也就是不允许出现幻读，这已经超过了ISO/ANSI SQL92“可重复读”隔离级别的要求，实际上是要求事务要串行化。这也是许多情况下，InnoDB要用到间隙锁的原因，比如在用范围条件更新记录时，无论在Read Commited或是Repeatable Read隔离级别下，InnoDB都要使用间隙锁，但这并不是隔离级别要求的

3.3 InnoDB表锁(意向锁)

笔者尝试官方文档中的案例，发现有出入，Intention Locks，结果还是行锁Record，这点费解。

3.4 Auto-Inc表锁

Auto-Inc是一种表锁，用在控制并发事务的场景中自增列的表锁。

在MySQL 5.1.22之前，innodb使用一个表锁解决自增字段的一致性问题(内部是用一个计数器维护，每次自增时要加表锁)，如果一行一行的插入数据则没有什么问题，但是如果大量的并发插入就废了，表锁会引起SQL堵塞，不但影响效率，而且可能会瞬间达到max_connections而崩溃。

在 5.1.22之后，innodb使用新的方式解决自增字段一致性问题，对于可以预判行数的insert语句，innodb使用一个轻量级的互斥量。如：某一insert语句1执行前，表的AUTO_INCREMENT=1，语句1的插入行数已知为3，innodb在语句1的实际插入操作执行前就预分配给该语句三个自增值，当有一个新的insert语句2要执行时，读取的AUTO_INCREMENT=4，这样虽然语句1可能还没有执行完，语句2就可直接执行无需等待语句2。这种方式对于可预判插入行数的插入语句有效，如：insert和replace。

对于无法提前获知插入行数的语句，如：insert...select...、replace...select...和load data则innodb还是使用表锁。

4. 死锁

MyISAM表锁不会发生死锁，因为一次能获取全部的表锁，具有排他性，要么全部满足，要么等待，因此不会死锁。InnoDB的行锁复杂程度高于MyISAM，除单个SQL组成的事务外，锁是逐步获取的，这就可能导致死锁。

笔者测试发现，发生死锁后，InnoDB一般能够检测出来，并使一个事务释放锁并回退，另一个事务获得锁，继续完成事务。但在涉及外部锁，或涉及表锁的情况下，InnoDB并不能完全自动检测到死锁，这需要通过设置锁等待超时参数 innodb_lock_wait_timeout来解决。需要说明的是，这个参数并不是只用来解决死锁问题，在并发访问比较高的情况下，如果大量事务因无法立即获得所需的锁而挂起，会占用大量计算机资源，造成严重性能问题，甚至拖跨数据库。我们通过设置合适的锁等待超时阈值，可以避免这种情况发生。

如果出现死锁，可以用SHOW ENGINE INNODB STATUS命令来确定最后一个死锁产生的原因。返回结果中包括死锁相关事务的详细信息，如引发死锁的SQL语句，事务已经获得的锁，正在等待什么锁，以及被回滚的事务等。据此可以分析死锁产生的原因和改进措施。

笔者，尝试多种方法产生表锁，均未成功，比如表product，有model，name属性，分别创建索引，还有code属性，也创建索引，操作如下：

session 1> start transaction;

session 1> update product set price=1 where name='test' and model='N8';

session 2> start transaction;

session 2> update product set price=2 where code='Mj12321';

实际上都是更新同一行数据，发现session1获得行锁，session2阻塞，进入等待队列。

后续该表条件，选中一个范围获取Gap，又更新范围内的数据，同样获得行锁。又使用like避开索引，还是行锁。扩大update范围，如更新上百万行数据，均是使用行锁，未涉及表锁。

最后使用alter改表语句删除一列，发现问题所在，原来是不管表锁还是行锁，锁查询的lock_type都为RECORD

> SELECT * FROM information_schema.INNODB_LOCKS;

根据笔者的观察，lock_type有两个取值，TABLE和RECORD，而实际用到表锁是也是RECORD，这点确实奇怪，下面换一个查询方法，便豁然开朗：

> show OPEN TABLES where `Database` = 'latest_db' ;

该查询记录表锁的打开情况。

总结行锁便表锁的情况

1、表字段进行变更。

2、进行整表查询。(没使用索引)

3、like语句查询的时候。(没使用索引)

锁、事务相关命令：

SELECT * FROM information_schema.INNODB_LOCKS;

SELECT * FROM information_schema.INNODB_LOCK_WAITS;

SELECT * FROM information_schema.INNODB_TRX;

SHOW PROCESSLIST;

SHOW ENGINE INNODB STATUS;

show OPEN TABLES where `Database` = 'latest_db' ;

show status like 'Table%';

5.聚簇索引概念

myisam的主索引和次索引都指向物理行，下面来进行讲解

innodb的主键下存储该行的数据，此索引指向对主键的引用

myisam的索引存储图如下，可以看出，无论是id还是cat_id，下面都存储有执行物理地址的值。通过主键索引或者次索引来查询数据的时候，都是先查找到物理位置，然后再到物理位置上去寻找数据。

20151004223450594.jpg

innodb的索引存储图如下，我们会发现，主键索引下面直接存储有数据，而次索引下，存储的是主键的id。通过主键查找数据的时候，就会很快查找到数据，但是通过次索引查找数据的时候，需要先查找到对应的主键id，然后才能查找到对应的数据。

20151004223815368.jpg

nnodb的主索引文件上 直接存放该行数据,称为聚簇索引,次索引指向对主键的引用

myisam中, 主索引和次索引,都指向物理行(磁盘位置).

注意: innodb来说,

1: 主键索引 既存储索引值,又在叶子中存储行的数据

2: 如果没有主键, 则会Unique key做主键

3: 如果没有unique,则系统生成一个内部的rowid做主键.

4: 像innodb中,主键的索引结构中,既存储了主键值,又存储了行数据,这种结构称为”聚簇索引”