Heim >Datenbank >MySQL-Tutorial >Detaillierte Erklärung und Vergleich der vier Transaktionsisolationsstufen von MySQL
Detaillierte Erklärung und Vergleich der vier Transaktionsisolationsstufen von MySQL
- 黄舟Original
- 2017-01-18 11:29:421555Durchsuche
Relationales MySQL-Datenbankverwaltungssystem
MySQL ist ein kleines relationales Open-Source-Datenbankverwaltungssystem, das von der schwedischen Firma MySQL AB entwickelt wurde. MySQL wird häufig auf kleinen und mittelgroßen Websites im Internet verwendet. Aufgrund der geringen Größe, der hohen Geschwindigkeit, der niedrigen Gesamtbetriebskosten und insbesondere der Eigenschaften von Open Source wählen viele kleine und mittlere Websites MySQL als Website-Datenbank, um die Gesamtbetriebskosten der Website zu senken.
Dieser Artikel führt hauptsächlich die detaillierte Erklärung und den Vergleich der vier Transaktionsisolationsstufen von MySQL ein. Hier finden Sie eine detaillierte Einführung in die Grundkenntnisse von MySQL und seiner Transaktionsisolation Vergleich, Freunde in Not können sich auf
Ausführliche Erklärung und Vergleich der vier Transaktionsisolationsstufen von MySQL beziehen
Gemäß der Transaktionsisolationsstufe von SQL:1992 ist InnoDB standardmäßig REPEATABLE READ). MySQL/InnoDB bietet alle vier im SQL-Standard beschriebenen Transaktionsisolationsstufen. Sie können die Standardisolationsstufe für alle Verbindungen mithilfe der Option --transaction-isolation in der Befehlszeile oder in einer Optionsdatei festlegen.
Sie können diese Option beispielsweise im Abschnitt [mysqld] der Datei my.inf wie folgt festlegen:
transaction-isolation = {READ-UNCOMMITTED | READ-COMMITTED | REPEATABLE-READ | SERIALIZABLE}Benutzer können die SET TRANSACTION-Anweisung verwenden, um die Isolationsstufe einer einzelnen Datei zu ändern Sitzung oder alle eingehenden Verbindungen. Die Syntax lautet wie folgt:
SET [SESSION | GLOBAL] TRANSACTION ISOLATION LEVEL {READ UNCOMMITTED | READ COMMITTED | REPEATABLE READ | SERIALIZABLE}Hinweis: Das Standardverhalten (ohne Sitzung und global) besteht darin, die Isolationsstufe für die nächste (nicht gestartete) Transaktion festzulegen. Wenn Sie das Schlüsselwort GLOBAL verwenden, legt die Anweisung die Standardtransaktionsebene global für alle neuen Verbindungen fest, die ab diesem Zeitpunkt erstellt werden (mit Ausnahme nicht vorhandener Verbindungen). Dazu benötigen Sie SUPER-Berechtigungen. Verwenden Sie das Schlüsselwort SESSION, um die Standardtransaktionsebene für zukünftige Transaktionen festzulegen, die auf der aktuellen Verbindung ausgeführt werden. Es steht jedem Client frei, die Sitzungsisolationsstufe zu ändern (auch mitten in einer Transaktion) oder die Isolationsstufe für die nächste Transaktion festzulegen.
Sie können die Isolationsstufen für globale und Sitzungstransaktionen mithilfe der folgenden Anweisungen abfragen:
SELECT @@global.tx_isolation; SELECT @@session.tx_isolation; SELECT @@tx_isolation;
----Theoretische Kenntnisse im obigen Handbuch;
====== == ============================================== == ================================
Isolationsstufe Dirty Read NonRepeatable Read ) Phantom Read
======================================== ======== ======================================== =
Lesen uncommitted Möglich Möglich
Read committed Unmöglich Möglich
Wiederholbares Lesen Unmöglich Nein Möglich Möglich
Serialisierbar Unmöglich Unmöglich
========== ============== ================================== ============== ==================
·Read Uncommitted: Dirty Reads sind erlaubt, d. h. Nicht festgeschriebene Transaktionsänderungen in anderen Sitzungen können gelesen werden.
·Festgeschrieben lesen: Nur übermittelte Daten können gelesen werden. Die meisten Datenbanken wie Oracle verwenden standardmäßig diese Ebene (nicht wiederholtes Lesen).
·Wiederholtes Lesen: wiederholbares Lesen. Abfragen innerhalb derselben Transaktion sind zu Beginn der Transaktion konsistent, InnoDB-Standardebene. Im SQL-Standard eliminiert diese Isolationsstufe nicht wiederholbare Lesevorgänge, es gibt jedoch weiterhin Phantom-Lesevorgänge
·Serieller Lesevorgang (serialisierbar): Vollständig serialisierter Lesevorgang. Für jeden Lesevorgang ist eine gemeinsame Sperre auf Tabellenebene erforderlich. Lese- und Schreibvorgänge werden ausgeführt sich gegenseitig blockieren
Verwenden Sie Beispiele, um die Situation auf jeder Ebene zu veranschaulichen:
① 脏读: 脏读就是指当一个事务正在访问数据，并且对数据进行了修改，而这种修改还没有提交到数据库中，这时，另外一个事务也访问这个数据，然后使用了这个数据。
session 1: mysql> select @@global.tx_isolation; +-----------------------+ | @@global.tx_isolation | +-----------------------+ | REPEATABLE-READ | +-----------------------+ 1 row in set (0.00 sec) mysql> select @@session.tx_isolation; +-----------------------+ | @@session.tx_isolation | +-----------------------+ | REPEATABLE-READ | +-----------------------+ 1 row in set (0.00 sec) mysql> start transaction; Query OK, 0 rows affected (0.00 sec) mysql> insert into ttd values(1); Query OK, 1 row affected (0.05 sec) mysql> select * from ttd; +------+ | id | +------+ | 1 | +------+ 1 row in set (0.00 sec) session 2: mysql> select @@session.tx_isolation; +------------------------+ | @@session.tx_isolation | +------------------------+ | REPEATABLE-READ | +------------------------+ 1 row in set (0.00 sec) mysql> select @@global.tx_isolation; +-----------------------+ | @@global.tx_isolation | +-----------------------+ | REPEATABLE-READ | --------该隔离级别下(除了 read uncommitted) +-----------------------+ 1 row in set (0.00 sec) mysql> select * from ttd; Empty set (0.00 sec) --------不会出现脏读 mysql> set session transaction isolation level read uncommitted; Query OK, 0 rows affected (0.00 sec) mysql> select @@session.tx_isolation; +------------------------+ | @@session.tx_isolation | +------------------------+ | READ-UNCOMMITTED | --------该隔离级别下 +------------------------+ 1 row in set (0.00 sec) mysql> select * from ttd; +------+ | id | +------+ | 1 | --------REPEATABLE-READ级别出现脏读 +------+ 1 row in set (0.00 sec)
结论:session 2 在READ-UNCOMMITTED 下读取到session 1 中未提交事务修改的数据.
② 不可重复读:是指在一个事务内，多次读同一数据。在这个事务还没有结束时，另外一个事务也访问该同一数据。那么，在第一个事务中的两次读数据之间，由于第二个事务的修改，那么第一个事务两次读到的的数据可能是不一样的。这样就发生了在一个事务内两次读到的数据是不一样的，因此称为是不可重复读。
session 1: mysql> select @@session.tx_isolation; +------------------------+ | @@session.tx_isolation | +------------------------+ | READ-COMMITTED | +------------------------+ 1 row in set (0.00 sec) mysql> start transaction; Query OK, 0 rows affected (0.00 sec) mysql> select * from ttd; +------+ | id | +------+ | 1 | +------+ 1 row in set (0.00 sec) session 2 : mysql> select @@session.tx_isolation; +------------------------+ | @@session.tx_isolation | +------------------------+ | REPEATABLE-READ | +------------------------+ 1 row in set (0.00 sec) mysql> start transaction; Query OK, 0 rows affected (0.00 sec) mysql> select * from ttd; +------+ | id | +------+ | 1 | +------+ 1 row in set (0.00 sec) mysql> insert into ttd values(2); /也可以更新数据 Query OK, 1 row affected (0.00 sec) mysql> select * from ttd; +------+ | id | +------+ | 1 | | 2 | +------+ 2 rows in set (0.00 sec) mysql> commit; Query OK, 0 rows affected (0.02 sec) session 2 提交后,查看session 1 的结果; session 1: mysql> select * from ttd; +------+ | id | +------+ | 1 | --------和第一次的结果不一样,READ-COMMITTED 级别出现了不重复读 | 2 | +------+ 2 rows in set (0.00 sec) ③ 可重复读: session 1: mysql> select @@session.tx_isolation; +------------------------+ | @@session.tx_isolation | +------------------------+ | REPEATABLE-READ | +------------------------+ 1 row in set (0.00 sec) mysql> start transaction; Query OK, 0 rows affected (0.00 sec) mysql> select * from ttd; +------+ | id | +------+ | 1 | | 2 | +------+ 2 rows in set (0.00 sec) session 2 : mysql> select @@session.tx_isolation; +------------------------+ | @@session.tx_isolation | +------------------------+ | REPEATABLE-READ | +------------------------+ 1 row in set (0.00 sec) mysql> start transaction; Query OK, 0 rows affected (0.00 sec) mysql> insert into ttd values(3); Query OK, 1 row affected (0.00 sec) mysql> commit; Query OK, 0 rows affected (0.03 sec) session 2 提交后,查看session 1 的结果; session 1: mysql> select * from ttd; +------+ | id | +------+ | 1 | --------和第一次的结果一样,REPEATABLE-READ级别出现了重复读 | 2 | +------+ 2 rows in set (0.00 sec) (commit session 1 之后 再select * from ttd 可以看到session 2 插入的数据3)
④ 幻读:第一个事务对一个表中的数据进行了修改，这种修改涉及到表中的全部数据行。同时，第二个事务也修改这个表中的数据，这种修改是向表中插入一行新数据。那么，以后就会发生操作第一个事务的用户发现表中还有没有修改的数据行，就好象发生了幻觉一样。
mysql>CREATE TABLE `t_bitfly` ( `id` bigint(20) NOT NULL default '0', `value` varchar(32) default NULL, PRIMARY KEY (`id`) ) ENGINE=InnoDB mysql> select @@global.tx_isolation, @@tx_isolation; +-----------------------+-----------------+ | @@global.tx_isolation | @@tx_isolation | +-----------------------+-----------------+ | REPEATABLE-READ | REPEATABLE-READ | +-----------------------+-----------------+ 实验一： t Session A Session B | | START TRANSACTION; START TRANSACTION; | | SELECT * FROM t_bitfly; | empty set | INSERT INTO t_bitfly | VALUES (1, 'a'); | | SELECT * FROM t_bitfly; | empty set | COMMIT; | | SELECT * FROM t_bitfly; | empty set | | INSERT INTO t_bitfly VALUES (1, 'a'); | ERROR 1062 (23000): | Duplicate entry '1' for key 1 v (shit, 刚刚明明告诉我没有这条记录的) 如此就出现了幻读，以为表里没有数据，其实数据已经存在了，傻乎乎的提交后，才发现数据冲突了。 实验二： t Session A Session B | | START TRANSACTION; START TRANSACTION; | | SELECT * FROM t_bitfly; | +------+-------+ | | id | value | | +------+-------+ | | 1 | a | | +------+-------+ | INSERT INTO t_bitfly | VALUES (2, 'b'); | | SELECT * FROM t_bitfly; | +------+-------+ | | id | value | | +------+-------+ | | 1 | a | | +------+-------+ | COMMIT; | | SELECT * FROM t_bitfly; | +------+-------+ | | id | value | | +------+-------+ | | 1 | a | | +------+-------+ | | UPDATE t_bitfly SET value='z'; | Rows matched: 2 Changed: 2 Warnings: 0 | (怎么多出来一行) | | SELECT * FROM t_bitfly; | +------+-------+ | | id | value | | +------+-------+ | | 1 | z | | | 2 | z | | +------+-------+
本事务中第一次读取出一行，做了一次更新后，另一个事务里提交的数据就出现了。也可以看做是一种幻读。
当隔离级别是可重复读，且禁用innodb_locks_unsafe_for_binlog的情况下，在搜索和扫描index的时候使用的next-key locks可以避免幻读。
再看一个实验，要注意，表t_bitfly里的id为主键字段。
实验三： t Session A Session B | | START TRANSACTION; START TRANSACTION; | | SELECT * FROM t_bitfly | WHERE id<=1 | FOR UPDATE; | +------+-------+ | | id | value | | +------+-------+ | | 1 | a | | +------+-------+ | INSERT INTO t_bitfly | VALUES (2, 'b'); | Query OK, 1 row affected | | SELECT * FROM t_bitfly; | +------+-------+ | | id | value | | +------+-------+ | | 1 | a | | +------+-------+ | INSERT INTO t_bitfly | VALUES (0, '0'); | (waiting for lock ...then timeout) | ERROR 1205 (HY000): | Lock wait timeout exceeded; | try restarting transaction | | SELECT * FROM t_bitfly; | +------+-------+ | | id | value | | +------+-------+ | | 1 | a | | +------+-------+ | COMMIT; | | SELECT * FROM t_bitfly; | +------+-------+ | | id | value | | +------+-------+ | | 1 | a | | +------+-------+
可以看到，用id<=1加的锁，只锁住了id<=1的范围，可以成功添加id为2的记录，添加id为0的记录时就会等待锁的释放。
实验四：一致性读和提交读 t Session A Session B | | START TRANSACTION; START TRANSACTION; | | SELECT * FROM t_bitfly; | +----+-------+ | | id | value | | +----+-------+ | | 1 | a | | +----+-------+ | INSERT INTO t_bitfly | VALUES (2, 'b'); | COMMIT; | | SELECT * FROM t_bitfly; | +----+-------+ | | id | value | | +----+-------+ | | 1 | a | | +----+-------+ | | SELECT * FROM t_bitfly LOCK IN SHARE MODE; | +----+-------+ | | id | value | | +----+-------+ | | 1 | a | | | 2 | b | | +----+-------+ | | SELECT * FROM t_bitfly FOR UPDATE; | +----+-------+ | | id | value | | +----+-------+ | | 1 | a | | | 2 | b | | +----+-------+ | | SELECT * FROM t_bitfly; | +----+-------+ | | id | value | | +----+-------+ | | 1 | a | | +----+-------+
如果使用普通的读，会得到一致性的结果，如果使用了加锁的读，就会读到“最新的”“提交”读的结果。
本身，可重复读和提交读是矛盾的。在同一个事务里，如果保证了可重复读，就会看不到其他事务的提交，违背了提交读；如果保证了提交读，就会导致前后两次读到的结果不一致，违背了可重复读。
可以这么讲，InnoDB提供了这样的机制，在默认的可重复读的隔离级别里，可以使用加锁读去查询最新的数据（提交读）。
MySQL InnoDB的可重复读并不保证避免幻读，需要应用使用加锁读来保证。而这个加锁度使用到的机制就是next-key locks。
以上就是MySQL 四种事务隔离级别详解及对比的内容，更多相关内容请关注PHP中文网（www.php.cn）！