MySQL InnoDB存储引擎DML一定是行锁吗？
在一般开发或DBA看来，MySQL InnoDB存储引擎的锁粒度一定是行锁，然后思想就根深蒂固了。今天要跟大家分享行锁与表锁的条件
创建表并插入数据：
root@localhost[test]>create table test (id int primary key,name varchar(20) not null default '0');
Query OK, 0 rows affected (0.02 sec)
root@localhost[test]>insert into test (id,name) values (1,'rsc'),(2,'gmy');
Query OK, 2 rows affected (0.00 sec)
Records: 2 Duplicates: 0 Warnings: 0
root@localhost[test]>select * from test;
+----+------+
| id | name |
+----+------+
| 1 | rsc |
| 2 | gmy |
+----+------+
2 rows in set (0.00 sec)
root@localhost[test]>show create table test;
+-------+--------------------------------------------------------------------------------------------------------------------------------------------------------+
| Table | Create Table |
+-------+--------------------------------------------------------------------------------------------------------------------------------------------------------+
| test | CREATE TABLE `test` (
`id` int(11) NOT NULL,
`name` varchar(20) NOT NULL DEFAULT '0',
PRIMARY KEY (`id`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 |
+-------+--------------------------------------------------------------------------------------------------------------------------------------------------------+
1 row in set (0.00 sec)
显示加行锁：
root@localhost[test]> set autocommit=0;
root@localhost[test]>select * from test where id=1 for update;
+----+------+
| id | name |
+----+------+
| 1 | rsc |
+----+------+
1 row in set (0.00 sec)
//查询是否锁表
root@localhost[(none)]>show OPEN TABLES where In_use > 0;
Empty set (0.00 sec)
再启动另一个会话：
root@localhost[(none)]>set autocommit=0;
Query OK, 0 rows affected (0.00 sec)
root@localhost[test]>show tables;
+----------------+
| Tables_in_test |
+----------------+
| test |
+----------------+
1 row in set (0.00 sec)
root@localhost[test]>select * from test;
+----+------+
| id | name |
+----+------+
| 1 | rsc |
| 2 | gmy |
+----+------+
2 rows in set (0.00 sec)
root@localhost[test]>select * from test where id=2 for update;
+----+------+
| id | name |
+----+------+
| 2 | gmy |
+----+------+
1 row in set (0.00 sec)
root@localhost[test]>rollback;
Query OK, 0 rows affected (0.00 sec)
root@localhost[test]>select * from test where name='gmy' for update; #被锁住了
root@localhost[test]>update test set name='gmy1' where name='gmy';
root@localhost[test]>select * from test where name='gmy' for update;
ERROR 1205 (HY000): Lock wait timeout exceeded; try restarting transaction #表锁超时
查看锁类型：
是否是否锁表：
root@localhost[(none)]>show OPEN TABLES where In_use > 0;
+----------+-------+--------+-------------+
| Database | Table | In_use | Name_locked |
+----------+-------+--------+-------------+
| test | test | 1 | 0 |
+----------+-------+--------+-------------+
1 row in set (0.00 sec)
查看锁的具体情况：
root@localhost[(none)]>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 |
+---------------+-------------+-----------+-----------+---------------+------------+------------+-----------+----------+-----------+
| 23875:111:3:2 | 23875 | X | RECORD | `test`.`test` | PRIMARY | 111 | 3 | 2 | 1 |
| 23869:111:3:2 | 23869 | X | RECORD | `test`.`test` | PRIMARY | 111 | 3 | 2 | 1 |
+---------------+-------------+-----------+-----------+---------------+------------+------------+-----------+----------+-----------+
2 rows in set, 1 warning (0.00 sec)
root@localhost[(none)]>SELECT * FROM INFORMATION_SCHEMA.INNODB_LOCK_WAITS;
+-------------------+-------------------+-----------------+------------------+
| requesting_trx_id | requested_lock_id | blocking_trx_id | blocking_lock_id |
+-------------------+-------------------+-----------------+------------------+
| 23875 | 23875:111:3:2 | 23869 | 23869:111:3:2 |
+-------------------+-------------------+-----------------+------------------+
1 row in set, 1 warning (0.00 sec)
root@localhost[(none)]>select * from sys.innodb_lock_waits \G;
*************************** 1. row ***************************
wait_started: 2021-06-26 03:45:44
wait_age: 00:00:08
wait_age_secs: 8
locked_table: `test`.`test`
locked_index: PRIMARY
locked_type: RECORD
waiting_trx_id: 23875
waiting_trx_started: 2021-06-26 03:41:28
waiting_trx_age: 00:04:24
waiting_trx_rows_locked: 4
waiting_trx_rows_modified: 0
waiting_pid: 5
waiting_query: select * from test where name='gmy' for update
waiting_lock_id: 23875:111:3:2
waiting_lock_mode: X
blocking_trx_id: 23869
blocking_pid: 3
blocking_query: NULL
blocking_lock_id: 23869:111:3:2
blocking_lock_mode: X
blocking_trx_started: 2021-06-26 03:32:31
blocking_trx_age: 00:13:21
blocking_trx_rows_locked: 1
blocking_trx_rows_modified: 0
sql_kill_blocking_query: KILL QUERY 3
sql_kill_blocking_connection: KILL 3
1 row in set, 3 warnings (0.02 sec)
root@localhost[(none)]>show processlist;
+----+------+-----------+------+---------+------+--------------+------------------------------------------------+
| Id | User | Host | db | Command | Time | State | Info |
+----+------+-----------+------+---------+------+--------------+------------------------------------------------+
| 3 | root | localhost | test | Sleep | 863 | | NULL |
| 5 | root | localhost | test | Query | 8 | Sending data | select * from test where name='gmy' for update |
| 6 | root | localhost | NULL | Query | 0 | starting | show processlist |
+----+------+-----------+------+---------+------+--------------+------------------------------------------------+
3 rows in set (0.00 sec)
解决方法：
root@localhost[test]>alter table test add index idx_name(`name`);
Query OK, 0 rows affected (8.33 sec)
Records: 0 Duplicates: 0 Warnings: 0
再演示一下上面的情况：
会话一：
root@localhost[test]>set autocommit=0;
Query OK, 0 rows affected (0.00 sec)
root@localhost[test]>select * from test where id=1 for update;
+----+------+
| id | name |
+----+------+
| 1 | rsc |
+----+------+
1 row in set (0.00 sec)
会话二：
root@localhost[(none)]>use test;
Reading table information for completion of table and column names
You can turn off this feature to get a quicker startup with -A
Database changed
root@localhost[test]>show create table test;
+-------+-----------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+
| Table | Create Table |
+-------+-----------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+
| test | CREATE TABLE `test` (
`id` int(11) NOT NULL,
`name` varchar(20) NOT NULL DEFAULT '0',
PRIMARY KEY (`id`),
KEY `idx_name` (`name`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 |
+-------+-----------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+
1 row in set (0.01 sec)
root@localhost[test]>select * from test;
+----+------+
| id | name |
+----+------+
| 2 | gmy |
| 1 | rsc |
+----+------+
2 rows in set (0.00 sec)
root@localhost[test]>select * from test where name='gmy' for update;
+----+------+
| id | name |
+----+------+
| 2 | gmy |
+----+------+
1 row in set (0.00 sec)
总结：
MySQL如果要实现行级锁，在要查询或更新的字段上，一定要有主键或索引，如果没有主键或索引，将会导致表级锁
当update时，需要在where条件的字段上要创建索引，如果不创建会导致表锁
微信公众号视频地址：https://mp.weixin.qq.com/s/AUsWxCVId5HpOAL3RBk7CQ
