| performance_schema | hosts | 0 | 0 |

| performance_schema | setup_objects | 0 | 0 |

| performance_schema | events_stages_current | 0 | 0 |

| mysql | time_zone | 0 | 0 |

| mysql | tables_priv | 0 | 0 |

| performance_schema | table_io_waits_summary_by_table | 0 | 0 |

| mysql | time_zone_leap_second | 0 | 0 |

| db01 | book | 0 | 0 |

| performance_schema | session_account_connect_attrs | 0 | 0 |

| db01 | mylock | 0 | 0 |

| mysql | func | 0 | 0 |

| performance_schema | events_statements_summary_global_by_event_name | 0 | 0 |

| performance_schema | events_statements_history | 0 | 0 |

| performance_schema | accounts | 0 | 0 |

| mysql | time_zone_transition | 0 | 0 |

| db01 | dept | 0 | 0 |

| performance_schema | events_stages_summary_by_host_by_event_name | 0 | 0 |

| performance_schema | events_stages_summary_by_thread_by_event_name | 0 | 0 |

| mysql | proc | 0 | 0 |

| performance_schema | setup_instruments | 0 | 0 |

| performance_schema | host_cache | 0 | 0 |

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

84 rows in set (0.00 sec)

• 添加锁

lock table 表名 1 read(write), 表名 2 read(write), ...;

• 释放表锁

unlock tables;

[](()2.1.1、读锁示例

读锁示例

• 在 session 1 会话中，给 mylock 表加个读锁

mysql> lock table mylock read;

Query OK, 0 rows affected (0.00 sec)

• 在 session1 会话中能不能读取 mylock 表：可以读

################# session1 中的操作 #################

mysql> select * from mylock;

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

| id | name |

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

| 1 | a |

| 2 | b |

| 3 | c |

| 4 | d |

| 5 | e |

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

5 rows in set (0.00 sec)

• 在 session1 会话中能不能读取 book 表：并不行。。。

################# session1 中的操作 #################

mysql> select * from book;

ERROR 1100 (HY000): Table 'book' was not locked with LOCK TABLES

• 在 session2 会话中能不能读取 mylock 表：可以读

################# session2 中的操作 #################

mysql> select * from mylock;

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

| id | name |

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

| 1 | a |

| 2 | b |

| 3 | c |

| 4 | d |

| 5 | e |

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

5 rows in set (0.00 sec)

• 在 session1 会话中能不能修改 mylock 表：并不行。。。

################# session1 中的操作 #################

mysql> update mylock set name='a2' where id=1;

ERROR 1099 (HY000): Table 'mylock' was locked with a READ lock and can't be updated

• 在 session2 会话中能不能修改 mylock 表：阻塞，一旦 mylock 表锁释放，则会执行修改操作

################# session2 中的操作 #################

mysql> update mylock set name='a2' where id=1;

在这里阻塞着呢~~~

结论

1. 当前 session 和其他 session 均可以读取加了加读锁的表
   

2. 当前 session 不能读取其他没有加读锁的表，并且不能修改加了读锁的表
   

3. 其他 session 想要修改加了读锁的表，必须等待其读锁释放
   

[](()2.1.2、写锁示例

写锁示例

• 在 session 1 会话中，给 mylock 表加个写锁

mysql> lock table mylock write;

Query OK, 0 rows affected (0.00 sec)

• 在 session1 会话中能不能读取 mylock 表：阔以

################# session1 中的操作 #################

mysql> select * from mylock;

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

| id | name |

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

| 1 | a2 |

| 2 | b |

| 3 | c |

| 4 | d |

| 5 | e |

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

5 rows in set (0.00 sec)

• 在 session1 会话中能不能读取 book 表：不阔以

################# session1 中的操作 #################

mysql> select * from book;

ERROR 1100 (HY000): Table 'book' was not locked with LOCK TABLES

• 在 session1 会话中能不能修改 mylock 表：当然可以啦，加写锁就是为了修改呀

################# session1 中的操作 #################

mysql> update mylock set name='a2' where id=1;

Query OK, 0 rows affected (0.00 sec)

Rows matched: 1 Changed: 0 Warnings: 0

• 在 session2 会话中能不能读取 mylock 表：

################# session2 中的操作 #################

mysql> select * from mylock;

在这里阻塞着呢~~~

结论

1. 当前 session 可以读取和修改加了写锁的表

2. 当前 session 不能读取其他表

3. 其他 session 想要读取加了写锁的表，必须等待其读锁释放

案例结论

1. MyISAM 在执行查询语句（SELECT）前，会自动给涉及的所有表加读锁，在执行增删改操作前，会自动给涉及的表加写锁。

2. MySQL 的表级锁有两种模式：

• 表共享读锁（Table Read Lock）

• 表独占写锁（Table Write Lock）

结论：结合上表，所以对 MyISAM 表进行操作，会有以下情况：

1. 对 MyISAM 表的读操作（加读锁），不会阻塞其他进程对同一表的读请求，但会阻塞对同一表的写请求。只有当读锁释放后，才会执行其它进程的写操作。

2. 对 MyISAM 表的写操作（加写锁），会阻塞其他进程对同一表的读和写操作，只有当写锁释放后，才会执行其它进程的读写操作

3. 简而言之，就是读锁会阻塞写，但是不会堵塞读。而写锁则会把读和写都堵塞。

[](()2.2、表锁分析

• 查看哪些表被锁了，0 表示未锁，1 表示被锁

show open tables;

1

【如何分析表锁定】可以通过检查 table_locks_waited 和 table_locks_immediate 状态变量来分析系统上的表锁定，通过 show status like 'table%'; 命令查看

1. Table_locks_immediate：产生表级锁定的次数，表示可以立即获取锁的查询次数，每立即获取锁值加 1；

2. Table_locks_waited：出现表级锁定争用而发生等待的次数（不能立即获取锁的次数，每等待一次锁值加 1），此值高则说明存在着较严重的表级锁争用情况；【此值越高，存在锁竞争越大】

mysql> show status like 'table%';

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

| Variable_name | Value |

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

| Table_locks_immediate | 500440 |

| Table_locks_waited | 1 |

| Table_open_cache_hits | 500070 |

| Table_open_cache_misses | 5 |

| Table_open_cache_overflows | 0 |

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

5 rows in set (0.00 sec)

• 此外，Myisam 的读写锁调度是写优先，这也是myisam不适合做写为主表的引擎。因为写锁后，其他线程不能做任何操作，大量的更新会使查询很难得到锁，从而造成永远阻塞
  

.

[](()3、行锁

行锁的特点

1. 偏向 InnoDB 存储引擎，开销大，加锁慢；会出现死锁；锁定粒度最小，发生锁冲突的概率最低，并发度也最高。

2. InnoDB 与 MyISAM 的最大不同有两点：一是支持事务（TRANSACTION）；二是采用了行级锁

[](()3.1、事务复习

行锁支持事务，复习下老知识

事务（Transation）及其 ACID 属性

事务是由一组 SQL 语句组成的逻辑处理单元，事务具有以下 4 个属性，通常简称为事务的 ACID 属性。

1. 原子性（Atomicity）：事务是一个原子操作单元，其对数据的修改，要么全都执行，要么全都不执行。

2. 一致性（Consistent）：在事务开始和完成时，数据都必须保持一致状态。这意味着所有相关的数据规则都必须应用于事务的修改，以保持数据的完整性；事务结束时，所有的内部数据结构（如 B 树索引或双向链表）也都必须是正确的。

3. 隔离性（Isolation）：数据库系统提供一定的隔离机制，保证事务在不受外部并发操作影响的“独立”环境执行。这意味着事务处理过程中的中间状态对外部是不可见的，反之亦然。

4. 持久性（Durability）：事务院成之后，它对于数据的修改是永久性的，即使出现系统故障也能够保持。

并发事务处理带来的问题

1. 更新丢失

（Lost Update）：

• 当两个或多个事务选择同一行，然后基于最初选定的值更新该行时，由于每个事务都不知道其他事务的存在，就会发生丢失更新问题一一最后的更新覆盖了由其他事务所做的更新。

• 例如，两个程序员修改同一java文件。每程序员独立地更改其副本，然后保存更改后的副本，这样就覆盖了原始文档。最后保存其更改副本的编辑人员覆盖前一个程序员所做的更改。

• 如果在一个程序员完成并提交事务之前，另一个程序员不能访问同一文件，则可避免此问题。

2. 脏读

（Dirty Reads）：

• 一个事务正在对一条记录做修改，在这个事务完成并提交前，这条记录的数据就处于不一致状态；这时，另一个事务也来读取同一条记录，如果不加控制，第二个事务读取了这些“脏”数据，并据此做进一步的处理，就会产生未提交的数据依赖关系。这种现象被形象地叫做”脏读”。

• 一句话：事务 A 读取到了事务 B 已修改但尚未提交的的数据，还在这个数据基础上做了操作。此时，如果 B 事务回滚，A 读取的数据无效，不符合一致性要求。

3. 不可重复读

（Non-Repeatable Reads）：

• 一个事务在读取某些数据后的某个时间，再次读取以前读过的数据，却发现其读出的数据已经发生了改变、或某些记录已经被删除了！这种现象就叫做“不可重复读”。

• 一句话：事务 A 读取到了事务 B 已经提交的修改数据，不符合隔离性

4. 幻读

（Phantom Reads）

• 一个事务按相同的查询条件重新读取以前检索过的数据，却发现其他事务插入了满足其查询条件的新数据，这种现象就称为“幻读一句话：事务 A 读取到了事务 B 体提交的新增数据，不符合隔离性。

• 多说一句：幻读和脏读有点类似，脏读是事务 B 里面修改了数据，幻读是事务 B 里面新增了数据。

事物的隔离级别

1. 脏读”、“不可重复读”和“幻读”，其实都是数据库读一致性问题，必须由数据库提供一定的事务隔离机制来解决。

2. 数据库的事务隔离越严格，并发副作用越小，但付出的代价也就越大，因为事务隔离实质上就是使事务在一定程度上“串行化”进行，这显然与“并发”是矛盾的。

3. 同时，不同的应用对读一致性和事务隔离程度的要求也是不同的，比如许多应用对“不可重复读”和“幻读”并不敏感，可能更关心数据并发访问的能力。

4. 查看当前数据库的事务隔离级别：show variables like 'tx_isolation'; mysql 默认是可重复读

[](()3.2、行锁案例

行锁案例分析

创建表

• 建表 SQL 使用ENGINE=INNODB引擎，mysql 默认

CREATE TABLE test_innodb_lock (a INT(11),b VARCHAR(16))ENGINE=INNODB;

INSERT INTO test_innodb_lock VALUES(1,'b2');

INSERT INTO test_innodb_lock VALUES(3,'3');

INSERT INTO test_innodb_lock VALUES(4, '4000');

INSERT INTO test_innodb_lock VALUES(5,'5000');

INSERT INTO test_innodb_lock VALUES(6, '6000');

INSERT INTO test_innodb_lock VALUES(7,'7000');

INSERT INTO test_innodb_lock VALUES(8, '8000');

INSERT INTO test_innodb_lock VALUES(9,'9000');

INSERT INTO test_innodb_lock VALUES(1,'b1');

CREATE INDEX test_innodb_a_ind ON test_innodb_lock(a);

CREATE INDEX test_innodb_lock_b_ind ON test_innodb_lock(b);

• test_innodb_lock 表中的测试数据

mysql> select * from test_innodb_lock;

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

| a | b |

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

| 1 | b2 |

| 3 | 3 |

| 4 | 4000 |

| 5 | 5000 |

| 6 | 6000 |

| 7 | 7000 |

| 8 | 8000 |

| 9 | 9000 |

| 1 | b1 |

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

9 rows in set (0.00 sec)

• test_innodb_lock 表中的索引

mysql> SHOW INDEX FROM test_innodb_lock;

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

| Table | Non_unique | Key_name | Seq_in_index | Column_name | Collation | Cardinality | Sub_part | Packed | Null | Index_type | Comment | Index_comment |

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

| test_innodb_lock | 1 | test_innodb_a_ind | 1 | a | A | 9 | NULL | NULL | YES | BTREE | | |

| test_innodb_lock | 1 | test_innodb_lock_b_ind | 1 | b | A | 9 | NULL | NULL | YES | BTREE | | |

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

2 rows in set (0.00 sec)

操作同一行数据

• session1 开启事务，修改 test_innodb_lock 中的 a=4 数据；

mysql> set autocommit=0;

Query OK, 0 rows affected (0.00 sec)

mysql> update test_innodb_lock set b='4001' where a=4;

Query OK, 1 row affected (0.00 sec)

Rows matched: 1 Changed: 1 Warnings: 0

• session2 开启事务，修改 test_innodb_lock 中同 a=4 的数据，将导致 session2 发生阻塞，一旦 session1 提交事务，session2 将执行更新操作；因为锁行，所以只有一个事务会话操作，其他的会被阻塞。

mysql> set autocommit=0;

Query OK, 0 rows affected (0.00 sec)

mysql> update test_innodb_lock set b='4002' where a=4;

在这儿阻塞着呢~~~

时间太长，会报超时错误哦

mysql> update test_innodb_lock set b='4001' where a=4;

ERROR 1205 (HY000): Lock wait timeout exceeded; try restarting transaction

操作不同行数据

• session1 开启事务，修改 test_innodb_lock 中的数据

mysql> set autocommit=0;

Query OK, 0 rows affected (0.00 sec)

mysql> update test_innodb_lock set b='4001' where a=4;

Query OK, 0 rows affected (0.00 sec)

Rows matched: 1 Changed: 0 Warnings: 0

• session2 开启事务，修改 test_innodb_lock 中不同行的数据

• 由于采用行锁，session2 和 session1 互不干涉，所以 session2 中的修改操作没有阻塞
  

mysql> set autocommit=0;

Query OK, 0 rows affected (0.00 sec)

mysql> update test_innodb_lock set b='9001' where a=9;

《一线大厂 Java 面试题解析+后端开发学习笔记+最新架构讲解视频+实战项目源码讲义》无偿开源 威信搜索公众号【编程进阶路】 Query OK, 1 row affected (0.00 sec)

Rows matched: 1 Changed: 1 Warnings: 0

无索引导致行锁升级为表锁

索引使用不当，会将行锁升级为表锁；

• session1 开启事务，修改 test_innodb_lock 中的数据，varchar 不用 ’ ’ ，导致系统自动转换类型，导致索引失效

mysql> set autocommit=0;

Query OK, 0 rows affected (0.00 sec)

mysql> update test_innodb_lock set a=44 where b=4000;

Query OK, 1 row affected (0.00 sec)

Rows matched: 1 Changed: 1 Warnings: 0

• session2 开启事务，修改 test_innodb_lock 中不同行的数据

• 由于发生了自动类型转换，索引失效，导致行锁变为表锁

mysql> set autocommit=0;

Query OK, 0 rows affected (0.00 sec)

mysql> update test_innodb_lock set b='9001' where a=9;

在这儿阻塞着呢~~~

注意：varchar 类型要加上单引号，不然会导致行锁转换为表锁，出现莫名其妙自己可能都不知道的阻塞问题！

.

[](()3.3、间隙锁