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是一个非事务引擎,所以你使用开启事务的方式是无法测试是否加锁的。你可以试试多线程并发更新的方式进行测试。
00
相似问题
不支持事务的存储引擎有什么作用呢?
回答 1
InnoDB存储引擎支持数据压缩吗?
回答 1