事务中的锁操作，UNLOCK自动提交事务。

最新推荐文章于 2024-04-19 10:17:21 发布

lumengabc

最新推荐文章于 2024-04-19 10:17:21 发布

阅读量1.1k

点赞数

CC 4.0 BY-SA版权

分类专栏： mysql

本文链接：https://blog.youkuaiyun.com/webnoties/article/details/17219143

mysql 专栏收录该内容

127 篇文章

订阅专栏

本文通过三个会话演示了MySQL中事务与锁的工作原理，包括读锁、写锁的使用及UNLOCKTABLES如何影响事务的提交过程。

摘要生成于 C知道，由 DeepSeek-R1 满血版支持，前往体验 >

当当前所有的表均被锁定时，UNLOCK TABLES可以提交事务。

从这上看，是只要UNLOCK TABLES就会提交事务。

主要看b会话，事务中加了读锁和解锁后，数据就自动提交到数据库了。(我想UNLOCK TABLES但不想提交事务，怎么办？)

我们分为3个会话：A,B和C

其中a和b会话有2个事务

mysql> COMMIT; #提交事务
Query OK, 0 rows affected (0.00 sec)

mysql> select title from feedback where id=1;
+-------+
| title |
+-------+
| 0     |
+-------+
1 row in set (0.00 sec)

mysql> #会话a
mysql> START TRANSACTION; #开始事务1
Query OK, 0 rows affected (0.00 sec)

mysql> select title from feedback where id=1;#事务B update 修改title=1后(未提交)
+-------+
| title |
+-------+
| 1     |
+-------+
1 row in set (0.00 sec)

mysql> select sleep(10) from feedback where id < 5;#隐式读锁执行
+-----------+
| sleep(10) |
+-----------+
|         0 |
|         0 |
|         0 |
|         0 |
+-----------+
4 rows in set (40.00 sec)

mysql> select title from feedback where id=1;#写锁状态等待
+-------+
| title |
+-------+
| 写1   |
+-------+
1 row in set (54.02 sec)

mysql> COMMIT; #提交事务
Query OK, 0 rows affected (0.00 sec)

mysql>  select * from a limit 1,1;    #对a表操作
+-----+-----+-------+
| uid | gid | title |
+-----+-----+-------+
|   2 |   0 | aaaa2 |
+-----+-----+-------+
1 row in set (0.00 sec)

mysql>  select * from b limit 1,1;    #对b表操作
+----+-----+--------------+------+
| id | uid | urluid       | jid  |
+----+-----+--------------+------+
|  2 |   3 | xx.php?uid=3 | NULL |
+----+-----+--------------+------+
1 row in set (0.00 sec)

mysql> #会话b
mysql> START TRANSACTION; #开始事务2
Query OK, 0 rows affected (0.00 sec)

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

mysql>  select * from a limit 1,1;    #对a表操作
+-----+-----+-------+
| uid | gid | title |
+-----+-----+-------+
|   2 |   0 | aaaa2 |
+-----+-----+-------+
1 row in set (0.00 sec)

mysql>  select * from b limit 1,1;    #对b表操作
+----+-----+--------------+------+
| id | uid | urluid       | jid  |
+----+-----+--------------+------+
|  2 |   3 | xx.php?uid=3 | NULL |
+----+-----+--------------+------+
1 row in set (0.00 sec)

mysql>
mysql>  LOCK TABLES feedback WRITE;#加上写锁
Query OK, 0 rows affected (34.92 sec)

mysql> update feedback set title='写1' where id=1;#写锁状态，执行写操作
Query OK, 1 row affected (0.09 sec)
Rows matched: 1  Changed: 1  Warnings: 0

mysql> UNLOCK TABLES; #解除锁（注意：当当前所有的表均被锁定时，UNLOCK TABLES可以提交事务，我想UNLOCK TABLES但不想提交事务，怎么办？）
Query OK, 0 rows affected (0.00 sec)

mysql> COMMIT; #提交事务
Query OK, 0 rows affected (0.00 sec)

mysql> select title from feedback where id=1;
+-------+
| title |
+-------+
| 0     |
+-------+
1 row in set (0.00 sec)

mysql> select title from feedback where id=1;#事务B update 修改title=1后(未提交)
+-------+
| title |
+-------+
| 0     |
+-------+
1 row in set (0.00 sec)

mysql> select title from feedback where id=1;#写锁状态等待
+-------+
| title |
+-------+
| 写1   |
+-------+
1 row in set (32.86 sec)