再来温故一些基础概念。
锁机制：
数据库区别与文件系统的一个关键特性之一。用于管理对共享资源的并发访问。
各种关系型数据库虽可能类似，但锁实现方式上又有各自的特点。
MySQL InnoDB 存储引擎,锁设计粒度：行锁。
MySQL MyISAM存储引擎,锁设计粒度：表锁。
其他数据库如SQL server 锁设计粒度：页锁。热点数据页并发访问。后通过乐观/悲观行锁控制并发访问（但会造成锁升级）
lock对象是事务，用来锁定的是数据库中的对象：表，页，行。并且lock对象仅在事务commit或rollback后进行释放。（不同隔离级别释放的时间可能不同）
InnoDB支持多粒度锁定。允许事务在行级和表级上的锁同时存在。为了支持在不同粒度上加锁操作 >>> 意向锁(Intention Lock)应运而生。
兼容矩阵：
模拟一个简单加锁：
查看数据库锁请求情况（一般通过如下方式）
1.show full processlist;
2.show engine innodb status\G
root@localhost:(none) 5.7.26-log 11:51:27> show engine innodb status\G （省略与本文无关输出信息）
*************************** 1. row ***************************
Type: InnoDB
Name:
Status:
=====================================
2019-12-09 11:52:42 0x7f6d5d1c8700 INNODB MONITOR OUTPUT
=====================================
Per second averages calculated from the last 14 seconds
.......
------------
TRANSACTIONS
------------
Trx id counter 1593
Purge done for trx's n:o < 1590 undo n:o < 0 state: running but idle
History list length 8
LIST OF TRANSACTIONS FOR EACH SESSION:
---TRANSACTION 421582651166320, not started
0 lock struct(s), heap size 1136, 0 row lock(s)
---TRANSACTION 421582651165408, not started
0 lock struct(s), heap size 1136, 0 row lock(s)
---TRANSACTION 1592, ACTIVE 5 sec starting index read
mysql tables in use 1, locked 1
LOCK WAIT 2 lock struct(s), heap size 1136, 1 row lock(s)
MySQL thread id 1128, OS thread handle 140107690583808, query id 2591 localhost root updating
update userinfo set age=32 where id=2
------- TRX HAS BEEN WAITING 5 SEC FOR THIS LOCK TO BE GRANTED:
RECORD LOCKS space id 37 page no 3 n bits 80 index PRIMARY of table `jiale`.`userinfo` trx id 1592 lock_mode X locks rec but not gap waiting
Record lock, heap no 3 PHYSICAL RECORD: n_fields 7; compact format; info bits 0
0: len 4; hex 80000002; asc ;;
1: len 6; hex 00000000062d; asc -;;
2: len 7; hex d400000187011c; asc ;;
3: len 8; hex 800000000000000b; asc ;;
4: len 7; hex 6368656e6c6569; asc chenlei;;
5: len 1; hex a8; asc ;;
6: len 1; hex 80; asc ;;
------------------
---TRANSACTION 1591, ACTIVE 36 sec
2 lock struct(s), heap size 1136, 9 row lock(s)
MySQL thread id 1127, OS thread handle 140106502825728, query id 2589 localhost root
--------
.......
tips: innodb_status_output_locks=on ,show engine innodb status更多信息
3.查看innodb_trx,innodb_locks,innodb_lock_waits 可知锁情况。
root@localhost:information_schema 5.7.26-log 11:54:25> select * from INNODB_TRX\G
*************************** 1. row ***************************
trx_id: 1592
trx_state: LOCK WAIT
trx_started: 2019-12-09 11:52:37
trx_requested_lock_id: 1592:37:3:3
trx_wait_started: 2019-12-09 11:52:37
trx_weight: 2
trx_mysql_thread_id: 1128
trx_query: update userinfo set age=32 where id=2
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: 1591
trx_state: RUNNING
trx_started: 2019-12-09 11:52:06
trx_requested_lock_id: NULL
trx_wait_started: NULL
trx_weight: 2
trx_mysql_thread_id: 1127
trx_query: NULL
trx_operation_state: NULL
trx_tables_in_use: 0
trx_tables_locked: 1
trx_lock_structs: 2
trx_lock_memory_bytes: 1136
trx_rows_locked: 9
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 rows in set (0.00 sec)
root@localhost:information_schema 5.7.26-log 11:54:29> select * from INNODB_LOCKS;
+-------------+-------------+-----------+-----------+--------------------+------------+------------+-----------+----------+-----------+
| lock_id | lock_trx_id | lock_mode | lock_type | lock_table | lock_index | lock_space | lock_page | lock_rec | lock_data |
+-------------+-------------+-----------+-----------+--------------------+------------+------------+-----------+----------+-----------+
| 1592:37:3:3 | 1592 | X | RECORD | `jiale`.`userinfo` | PRIMARY | 37 | 3 | 3 | 2 |
| 1591:37:3:3 | 1591 | X | RECORD | `jiale`.`userinfo` | PRIMARY | 37 | 3 | 3 | 2 |
+-------------+-------------+-----------+-----------+--------------------+------------+------------+-----------+----------+-----------+
2 rows in set, 1 warning (0.00 sec)
Warning (Code 1681): 'INFORMATION_SCHEMA.INNODB_LOCKS' is deprecated and will be removed in a future release.
root@localhost:information_schema 5.7.26-log 11:54:43> select * from INNODB_LOCK_WAITS;
+-------------------+-------------------+-----------------+------------------+
| requesting_trx_id | requested_lock_id | blocking_trx_id | blocking_lock_id |
+-------------------+-------------------+-----------------+------------------+
| 1592 | 1592:37:3:3 | 1591 | 1591:37:3:3 |
+-------------------+-------------------+-----------------+------------------+
1 row in set, 1 warning (0.00 sec)
很多时候我们知道被阻塞事务SQL语句，却不知到底是谁阻塞（后续）事务运行。查看processlist很可能Command为 sleep。此时
4.可通过events_statements_current查看持有锁SQL。（MySQL5.7+）
root@localhost:performance_schema 5.7.26-log 12:00:20> select * from performance_schema.events_statements_current where THREAD_ID=1127\G
*************************** 1. row ***************************
THREAD_ID: 1127
EVENT_ID: 9
END_EVENT_ID: 9
EVENT_NAME: statement/sql/select
SOURCE:
TIMER_START: 2080305515241809000
TIMER_END: 2080305515906717000
TIMER_WAIT: 664908000
LOCK_TIME: 293000000
SQL_TEXT: select * from userinfo where id>1 for update
DIGEST: bcac85243232e159f51f6e5604c95b20
DIGEST_TEXT: SELECT * FROM `userinfo` WHERE `id` > ? FOR UPDATE
CURRENT_SCHEMA: jiale
OBJECT_TYPE: NULL
OBJECT_SCHEMA: NULL
OBJECT_NAME: NULL
OBJECT_INSTANCE_BEGIN: NULL
MYSQL_ERRNO: 0
RETURNED_SQLSTATE: NULL
MESSAGE_TEXT: NULL
ERRORS: 0
WARNINGS: 0
ROWS_AFFECTED: 0
ROWS_SENT: 8
ROWS_EXAMINED: 8
CREATED_TMP_DISK_TABLES: 0
CREATED_TMP_TABLES: 0
SELECT_FULL_JOIN: 0
SELECT_FULL_RANGE_JOIN: 0
SELECT_RANGE: 1
SELECT_RANGE_CHECK: 0
SELECT_SCAN: 0
SORT_MERGE_PASSES: 0
SORT_RANGE: 0
SORT_ROWS: 0
SORT_SCAN: 0
NO_INDEX_USED: 0
NO_GOOD_INDEX_USED: 0
NESTING_EVENT_ID: NULL
NESTING_EVENT_TYPE: NULL
NESTING_EVENT_LEVEL: 0
1 row in set (0.00 sec)
现在我们了解两个概念：一致性非锁定读/一致性锁定读
一致性非锁定读（理解示意图含义）
在事务隔离级别READ COMMITTED（RC），REPEATABLE READ（RR）下，InnoDB存储引擎使用非锁定一致性读。但是对于快照数据定义却不相同。
RC：非一致性读总是读取被锁定行的最新（一份快照）数据。
RR：非一致性读总是读取事务开始时行版本数据。
我们来看一下如下场景查询结果会是怎样？并给出自己的解释（知其所以然）
2. 一致性锁定读（locking read）
select ... for update;
select ... lock in share mode;
2.1 一致性锁定读加锁方式：
select ... for update; 对读取行记录➕ X锁，其他事务不能对已锁定行加任何锁。
select ... lock in share mode; 对读取行记录➕S锁，其他事务可以对已锁定行加S锁，但是如果加X锁会被阻塞。
2.2 再回头说明下MySQL InnoDB 行锁算法：大致可分为三种算法。
Record Lock:单个行记录上的锁
Gap Lock:间隙锁，锁定的一个范围，但不包括记录本身
Next-key Lock:Gap Lock+Record Lock ，锁定一个范围，并且锁定记录本身（RR模式下防止幻读）
重点：查询条件含有唯一属性时，InnoDB（优化锁）next-key lock 降级为record lock
模拟加锁，思考事务运行状态：
如何查看加锁过程：
tips:MySQL-8.0新增performance_schema.data_locks表。
通过查看此表可清晰知道MySQL的加锁过程。再结合兼容矩阵可理解阻塞原因。
留个疑问：在RR和RC隔离级别下加锁过程有何区别。建议模拟动手实验
本文分享自微信公众号 - topdba，如有侵权，请联系 service001@enmotech.com 删除。
