memory存储引擎表级锁应该怎么理解?

来源:2-18 MySQL常用存储引擎之Memory

weixin_慕村3424678

2019-06-26

请教老师一个问题:老师在课程中提到memory存储引擎的表,是表级锁。我的理解是,如果对表的记录进行DML操作,会带来表级锁。而,我在验证的试试,并不是如我理解的那样:如下:
会话1:

mysql> show variables like '%iso%';
+---------------+-----------------+
| Variable_name | Value           |
+---------------+-----------------+
| tx_isolation  | REPEATABLE-READ |
+---------------+-----------------+
1 row in set (0.00 sec)

mysql> show create table mymemory\G
*************************** 1. row ***************************
       Table: mymemory
Create Table: CREATE TABLE `mymemory` (
  `id` int(11) DEFAULT NULL
) ENGINE=MEMORY DEFAULT CHARSET=latin1
1 row in set (0.00 sec)

mysql> select * from mymemory;
+------+
| id   |
+------+
|   11 |
|    2 |
+------+
2 rows in set (0.00 sec)

mysql> start transaction;
Query OK, 0 rows affected (0.00 sec)

mysql> update mymemory set id=1 where id=11;
Query OK, 1 row affected (0.00 sec)
Rows matched: 1  Changed: 1  Warnings: 0

mysql> 

会话2:

mysql> show variables like '%iso%';
+---------------+-----------------+
| Variable_name | Value           |
+---------------+-----------------+
| tx_isolation  | REPEATABLE-READ |
+---------------+-----------------+
1 row in set (0.00 sec)

mysql> select * from mymemory;
+------+
| id   |
+------+
|    1 |
|    2 |
+------+
2 rows in set (0.00 sec)

mysql> start transaction;
Query OK, 0 rows affected (0.00 sec)

mysql> update mymemory set id=22 where id=2;
Query OK, 1 row affected (0.00 sec)
Rows matched: 1  Changed: 1  Warnings: 0

mysql> insert into mymemory values(3);
Query OK, 1 row affected (0.00 sec)

mysql> select * from mymemory;
+------+
| id   |
+------+
|    1 |
|   22 |
|    3 |
+------+
3 rows in set (0.00 sec)

mysql> 

从上,按照我的理解,memory存储引擎的表级锁。应该是:会话1,开启事务,去更新id=1的记录,此时会获取该表的表级锁,也就意味着,在该事务未完成(提交或回滚)之前,其它会话,即使去更新该表的其它行记录时,也会被阻塞。可是,却不是我所理解的那样。请老师帮忙解释一下,此处的表级锁的含义,谢谢!

写回答

1回答

sqlercn

2019-06-26

memory是一个非事务引擎,所以你使用开启事务的方式是无法测试是否加锁的。你可以试试多线程并发更新的方式进行测试。

0
0

MySQL提升课程 全面讲解MySQL架构设计

面面俱到讲解影响MySQL性能的各个因素,让MySQL架构了然于胸。

4419 学习 · 547 问题

查看课程