Erfahren Sie mehr über Transaktionen und Sperren in MySQL

MySQL-Datenbank ist ein Mehrbenutzerzugriffssystem. Wenn also mehrere Benutzer gleichzeitig Daten lesen und aktualisieren, werden Sperren nicht zerstört Wenn ein Benutzer versucht, Datensätze in der Datenbank zu ändern, muss er zuerst die Sperre erwerben. Während der Benutzer, der die Sperre besitzt, noch Änderungen vornimmt, können andere Benutzer diese Datensätze nicht ändern. [Verwandte Empfehlungen: MySQL-Video-Tutorial]

Sperren in MySQL

Aber im Vergleich zu anderen Datenbanken verfügen verschiedene MySQL-Speicher-Engines über unterschiedliche Sperrmechanismen, einschließlich MylSAM- und MEMORY-Tabellen-. Es werden Sperren auf Ebenenebene verwendet, und die BDB-Speicher-Engine verwendet Seitensperren. Die häufig verwendete InnoDB-Speicher-Engine unterstützt Sperren auf Zeilenebene und Sperren auf Tabellenebene, und standardmäßig werden Sperren auf Zeilenebene verwendet.

Die Merkmale dieser drei Arten von Sperren sind wie folgt:

• Sperren auf Tabellenebene: geringer Overhead, schnelles Sperren, keine Deadlocks, große Sperrgranularität, höchste Wahrscheinlichkeit von Sperrenkonflikten und niedrigste Parallelität.

• Sperre auf Zeilenebene: hoher Overhead, langsame Sperre, Deadlock tritt auf, kleinste Sperrgranularität, geringste Wahrscheinlichkeit eines Sperrenkonflikts und höchste Parallelität.

• Seitensperre: Die Kosten und die Sperrzeit liegen zwischen Tabellensperren und Zeilensperren, es kommt zu Deadlocks, die Sperrgranularität liegt zwischen Tabellensperren und Zeilensperren und die Parallelität ist durchschnittlich.

MyISAM

MyISAM-Tabellensperre

MySQL bietet zwei Arten von Sperren für Tabellen:

• LESESPERRE: Ermöglicht Benutzern, nur Daten aus der Tabelle zu lesen.

• SCHREIBSPERRE: Ermöglicht Benutzern das Lesen und Schreiben in die Tabelle.

Der Lesevorgang von MyISAM für die Tabelle blockiert nicht die Leseanforderungen anderer Benutzer für dieselbe Tabelle, aber die Schreibvorgänge von MyISAM für die Tabelle blockieren die Lese- und Schreibanforderungen anderer Benutzer Die Schreiboperationen in derselben Tabelle, die Leseoperationen und Schreiboperationen der MyISAM-Tabelle sowie die Schreiboperationen sind seriell.

MyISAM fügt vor der Ausführung der Abfrageanweisung (SELECT) automatisch Lesesperren zu allen verwendeten Tabellen hinzu erfordert keinen manuellen Eingriff durch uns, sodass wir im Allgemeinen nicht den Befehl LOCK TABLE verwenden müssen, um die MyISAM-Tabelle explizit zu sperren. Es gibt jedoch kein Problem mit der Anzeige der Sperre.

Auch wenn Sie LOCK TABLES zum expliziten Hinzufügen von Tabellensperren zu einer Tabelle verwenden, müssen Sie alle Sperren für die beteiligten Tabellen gleichzeitig erhalten, da Sie nach der Ausführung von LOCK TABLES nur auf die explizit gesperrten Tabellen zugreifen können, nicht auf die entsperrten. Andernfalls wird beim Hinzufügen einer Lesesperre nur der Abfragevorgang ausgeführt, ansonsten wird ein Fehler gemeldet Aus diesem Grund wird die MyISAM-Tabelle nicht automatisch gesperrt.

Sehen Sie sich unten ein Beispiel an.

1. Erstellen Sie eine Tabelle

CREATE TABLE test_table (   
      Id INT NOT NULL AUTO_INCREMENT,   
      Name VARCHAR(50) NOT NULL,   
      Message VARCHAR(80) NOT NULL,  
      PRIMARY KEY (Id)  
);

2. Sitzung 1 erhält die Schreibsperre

mysql> lock table  test_table write;
Query OK, 0 rows affected (0.01 sec)

3.

Wir wissen, dass alle anderen Sitzungen nicht auf die Daten der Tabelle zugreifen können, wenn eine Sitzung die Schreibsperre hält. Wenn also die zweite Sitzung die folgende Anweisung ausführt, befindet sie sich immer im Wartezustand.

mysql> select * from test_table;

4. Sitzung 1 freigeschaltet

unlock table;

Gleichzeitige Einfügungen

Lese- und Schreibvorgänge in MyISAM sind seriell, aber gemäß der Einstellung von concurrent_insert kann MyISAM parallele Abfragen und Einfügungen unterstützen.

concurrent_insert hat die folgenden Werte:

• 0: Die gleichzeitige Einfügungsfunktion ist nicht zulässig.

• 1: Gleichzeitige Einfügungen für Tabellen ohne Löcher zulassen, mit neuen Daten am Ende der Datendatei (Standard).

• 2: Unabhängig davon, ob die Tabelle Löcher aufweist oder nicht, ist das gleichzeitige Einfügen am Ende der Datendatei zulässig.

Löcher beziehen sich auf Zeilen in der Mitte der Tabelle, die nicht gelöscht wurden.

InnoDB

InnoDB unterscheidet sich von MyISAM. Zum einen unterstützt es Transaktionen und zum anderen verwendet es Sperren auf Zeilenebene Schlösser.

Transaktionseigenschaften

• Atomizität

  Eine Transaktion ist eine atomare Operationseinheit. Alle Änderungen an den Daten werden entweder ausgeführt oder überhaupt nicht ausgeführt.

• Konsistenz

  Daten müssen konsistent bleiben, wenn eine Transaktion gestartet und abgeschlossen wird. Das bedeutet, dass alle relevanten Datenregeln auf Transaktionsänderungen angewendet werden müssen, um die Datenintegrität aufrechtzuerhalten.

• Isolation

  Das Datenbanksystem stellt sicher, dass Transaktionen nicht durch externe gleichzeitige Vorgänge beeinträchtigt werden und in einer „unabhängigen“ Umgebung ausgeführt werden können, was bedeutet, dass der Zwischenzustand während der Transaktionsverarbeitung für außen unsichtbar ist.

• Persistenz

  Nach Abschluss der Transaktion ist die Änderung der Daten dauerhaft und kann auch bei einem Systemausfall beibehalten werden.

Probleme, die durch die gleichzeitige Transaktionsverarbeitung verursacht werden

Obwohl die Ressourcennutzung verbessert wird und mehr Benutzer unterstützt werden können, bringt die gleichzeitige Transaktionsverarbeitung im Vergleich zur seriellen Verarbeitung auch einige Probleme mit sich, hauptsächlich einschließlich der folgenden Situationen.

Update verloren

由于每个事务都不知道其他事务的存在，就会发生丢失更新问题，也就是最后的更新覆盖了由其他事务所做的更新。

脏读

脏读又称无效数据的读出，当事务1将某一值修改后，然后事务2读取该值，后面事务1又因为一些原因撤销对该值的修改，这就导致了事务2所读取到的数据是无效的。

不可重复读

指的是一个事务在读取某些数据后，再次读取之前读过的数据，却发现读出的数据已经发生了改变。

幻读

当事务1按相同的查询条件重新读取以前查询过的数据时，却发现其他事务插入了满足这个条件的新数据。

事务隔离级别

上面说的"更新丢失"是应该完全避免的，但防止更新丢失，并不能单靠数据库事务控制器来解决，需要应用程序对要更新的数据加必要的锁。

而脏读、不可重复读、幻读，都是数据库读一致性问题，必须由数据库提供事务隔离机制来解决。数据库实现事务隔离的方式，可分为以下两种，一种是在读取数据前加锁，阻止其他事务对数据进行修改，另一种不需要锁，通过MVCC或MCC来实现，这种技术叫做数据多版本并发控制，通过一定机制生成一个数据请求时间点的一致性数据快照，并用这个快照来提供一定级别的一致性读取。

数据库的事务隔离越严格，并发副作用越小，但付出的代价也就越大，因为事务隔离实质上就是使事务在一定程度上串行化进行。

InnoDB有四个事务隔离级别： READ UNCOMMITTED， READ COMMITTED， REPEATABLE READ，和 SERIALIZABLE。默认隔离级别是REPEATABLE READ。

隔离级别	脏读	不可重复性	幻读
读未提交	√	√	√
读已提交	×	√	√
可重复读取	×	×	√
可序列化（serializable）	×	×	×

查询/更改隔离级别

显示隔离级别
show global variables like '%isolation%';
select @@transaction_isolation;

设置隔离级别
set global transaction_isolation ='read-committed';
set session transaction isolation level read uncommitted;

READ UNCOMMITTED（读未提交）

在这个隔离级别，所有事务都可以看到其他未提交事务的执行结果。这种隔离级别在实际应用中很少使用，读取未提交的数据也称为脏读。

例子

启动两个会话，并设置隔离级别为READ UNCOMMITTED。

mysql> select * from user;
+-----------+---------+
| user_name | balance |
+-----------+---------+
| 张三      |     100 |
| 李四      |     100 |
| 王五      |      80 |
+-----------+---------+

时间	事务1	事务2
T1	begin;	begin;
T2	select * from user where user_name="张三"; 此时张三余额100
T3		select * from user where user_name="张三"; 此时张三余额100
T4	update user set balance =80 where user_name ="张三";
T4		select * from user where user_name="张三"; 此时张三余额80
T5	commit	commit

可以看到，在T4时刻，事务1没有提交，但是事务2可以看到被事务1锁更改的数据。

READ COMMITTED （读已提交）

这是大多数数据库系统的默认隔离级别，但不是MySQL的默认级别，他避免了脏读现象，因为在任何未提交的事务前，对任何其他事务都是不可见的，也就是其他事务看不到未提交的数据，允许不可重复读。

例子

将两个会话中隔离级别设置为读已提交
set session transaction isolation level read committed;

时间	事务1	事务2
T1	begin;	begin;
T2	select * from user where user_name="张三"; 此时张三余额100
T3		select * from user where user_name="张三"; 此时张三余额100
T4	update user set balance =80 where user_name ="张三";
T4		select * from user where user_name="张三"; 此时张三余额100
T5	commit
T5		select * from user where user_name="张三"; 此时张三余额80

可以看到，在T4时刻，事务1没有提交，但是事务2读取到的数据还是100,当事务1提交后，事务2才可以看到。

REPEATABLE READ （可重复读）

这是 MySQL 的默认事务隔离级别，它确保同一事务读取数据时，将看到相同的数据行，但是会出现幻读，当事务1按条件进行查询后，另一个事务在该范围内插入一个新数据，那么事务1再次读取时，就会读到这个新数据。InnoDB 和 Falcon 存储引擎通过 mvcc（多版本并发控制）机制解决了这个问题。

例子

设置两个会话隔离级别为可重复读
set session transaction isolation level repeatable read;

时间	事务1	事务2
T1	begin;	begin;
T2	update user set balance =80 where user_name ="张三";
T3	commit;
T4		select * from user where user_name="张三"; 张三余额为100

可以看到，在T3时刻，事务1已经提交更改，但是在T4时刻的事务2中，还是读取到了原来的数据，但是如果事务2在原来的基础上再减10元，那么最终余额是90还是70呢？,答案是70。.

mysql> update user set balance=balance-10 where user_name="张三";
Query OK, 1 row affected (0.00 sec)
Rows matched: 1  Changed: 1  Warnings: 0

mysql> select * from user where user_name="张三";
+-----------+---------+
| user_name | balance |
+-----------+---------+
| 张三      |      70 |
+-----------+---------+
1 row in set (0.00 sec)

SERIALIZABLE （序列化）

他是最高的隔离级别，InnoDB将所有普通SELECT语句隐式转换为SELECT ... LOCK IN SHARE MODE，所有事务按照顺序依次执行，因此，脏读、不可重复读、幻读都不会出现。但是，由于事务是串行执行，所以效率会大大下降，

例子

设置隔离级别为序列化
set session transaction isolation level serializable;

时间	事务1	事务2
T1	begin;	begin;
T2	select * from user where user_name="张三";
T3		update user set balance =80 where user_name ="张三";

这一次，有趣的是，事务2在T3时刻更新被阻止了,原因是在serializable隔离级别下，MySQL隐式地将所有普通SELECT查询转换为SELECT FOR SHARE， 持有SELECT FOR SHARE锁的事务只允许其他事务对SELECT行进行处理，而不允许其他事务UPDATE或DELETE它们。

所以有了这个锁定机制，我们之前看到的不一致数据场景就不再可能了。

但是，这个锁具有超时时间，在等待一会后，如果其他事务在这段时间内没有提交或回滚释放锁，将抛出锁等待超时错误，如下所示：

ERROR 1205 (HY000): Lock wait timeout exceeded; try restarting transaction

InnoDB行锁

InnoDB 的行级锁也分为共享锁和排他锁两种。

• 共享锁允许持有锁的事务读取行。

• 独占锁允许持有锁事务的更新或删除行。

为了允许行锁和表锁共存，实现多粒度锁机制，InnoDB还有两种内部使用的意向锁，这两种意向锁都是表锁。

• 意向共享锁 事务想要获得一张表中某几行的共享锁。
• 意向排他锁 事务想要获得一张表中某几行的排他锁。

InnoDB 行锁是通过锁定索引上的索引条目来实现的，因此，InnoDB 只有在通过索引条件检索到数据时才使用行级锁；否则，InnoDB 将使用表锁。

我们可以显示的加锁，但对于update、delete、insert语句，InnoDB会自动给涉及的数据集加排他锁，对于普通的 select 语句，InnoDB 不会加任何锁，下面是显示的加锁方式：

• 共享锁：SELECT  FROM table_name WHERE … LOCK IN SHARE MODE
• 排他锁：SELECT * FROM table_name WHERE … FOR UPDATE

Next-Key锁

当我们使用范围条件而不是相等条件检索数据，并请求其共享或排他锁时，InnoDB会给符合条件的已有数据记录的索引项加锁，对于在条件范围内但并不存在的记录，叫做间隙(GAP), InnoDB也会对这个"间隙"加锁，这种锁机制就是所谓的Next-Key锁。

举例来说，假如user表中只有101条记录，其user_id的值分别是1.2. ..100. 101，当查找大于100的user_id时，使用下面SQL。

select.* from emp where user_id > 100 for update;

这就是一个范围条件的查询， InnoDB不仅会对user_id为101的记录加锁，也会对user_id大于101的"间隙"加锁，虽然这些记录并不存在。

InnoDB使用Next-Key锁的目的，一方面是为了防止幻读，另一方面， 是为了满足恢复和复制的需要。

更多编程相关知识，请访问：编程视频！！

Das obige ist der detaillierte Inhalt vonErfahren Sie mehr über Transaktionen und Sperren in MySQL. Für weitere Informationen folgen Sie bitte anderen verwandten Artikeln auf der PHP chinesischen Website!