MySQL - 事务和锁中的互斥？ [英] MySQL - Mutual exclusion in transactions and locks?

问题描述

我目前正在了解MySQL的事务和锁定功能。

I am currently learning about MySQL's transaction and lock features.

是隔离级别的事务 SERIALIZABLE 和在同一个表上执行的 LOCK 和 UNLOCK 语句之间的语句互斥？

Are transactions with the isolation-level SERIALIZABLE and statements between a LOCK and UNLOCK statement on the same table executed mutually exclusive?

EDIT 1 ：对于隔离级别 SERIALIZABLE 的事务，是否甚至可以确定事务是否实际上是相互排斥的或者只是像没有幻影读取的要求被满足？或者，这两个属性是否具有相同的行为？

EDIT 1: For the transaction thing with isolation level SERIALIZABLE, is it even possible to determine whether the transaction is actually mutually exclusive or just the requirements like no phantom reads are fulfilled? Or do these two properties imply the same behavior?

EDIT 2 ：如果两个或多个事务实际上在相同的数据上工作，以致读取现象实际发生，那么提供事务的给定隔离级别的机制是活动的。

EDIT 2: Oh, and are the mechanisms that provide the given isolation-level of a transaction only active if two or more transactions actually work on the same data so that the read phenomena could actually happen?

推荐答案

在InnoDB中，所有SERIALIZABLE都将 SELECT 转换为隐式 SELECT ... LOCK IN SHARE MODE 。所以这只影响对同一行的INSERT / UPDATE / DELETE。

In InnoDB, all SERIALIZABLE does is turn a SELECT into an implicit SELECT...LOCK IN SHARE MODE. So this only affects INSERT/UPDATE/DELETE against the same row(s).

你可以观察SHOW ENGINE INNODB STATUS中的锁：

You can observe the locks in SHOW ENGINE INNODB STATUS:

---TRANSACTION 14594, ACTIVE 5 sec
2 lock struct(s), heap size 360, 8 row lock(s)
MySQL thread id 24, OS thread handle 0x7f65c8624700, query id 324 192.168.56.1 root cleaning up
TABLE LOCK table `imdb`.`kind_type` trx id 14594 lock mode IS
RECORD LOCKS space id 24 page no 4 n bits 80 index `kind` of table `imdb`.`kind_type` trx id 14594 lock mode S

在设置tx_isolation = SERIALIZABLE和autcommit = 0之后，我所做的只是 SELECT * FROM imdb.kind_type 。

All I did was SELECT * FROM imdb.kind_type after setting tx_isolation=SERIALIZABLE and autcommit=0.

你看到它有一个IS表锁，它将阻塞 LOCK TABLES kind_type WRITE 。

You see that it holds an IS table lock, which will block a LOCK TABLES kind_type WRITE.

但是如果你正在执行事务自动提交模式，它甚至不这样做;它只是像REPEATABLE-READ。

But if you're executing the transaction autocommit mode, it doesn't even do that; it just acts like REPEATABLE-READ.

这篇关于MySQL - 事务和锁中的互斥？的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！