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



分割线
打赏
YWSOS.COM 平台代运维解决方案
 评论
 发表评论
姓   名:

Powered by AKCMS