【MySQL 06】Transaktionsverarbeitung

1. ACID-Eigenschaften von Transaktionen

Transaktionen haben 4 Eigenschaften: Atomarität, Konsistenz, Isolation und Haltbarkeit.

Nehmen Sie „Banküberweisung“ als Beispiel:

• Atomizität: Die Anweisungen, aus denen eine Transaktion besteht, bilden eine logische Einheit und nur ein Teil davon kann nicht ausgeführt werden. Mit anderen Worten: Eine Transaktion ist die kleinste unteilbare Einheit. Beispiel: Bei der Überweisung muss der Überweisungsbetrag gleichzeitig von einem Konto abgebucht und einem anderen Konto gutgeschrieben werden. Es ist unzumutbar, nur ein Konto zu ändern.

• Konsistenz: Die Datenbank ist vor und nach der Transaktionsausführung konsistent. Das heißt, die Transaktion sollte den Systemstatus korrekt umwandeln. Beispiel: Beim Banküberweisungsprozess wird entweder der Überweisungsbetrag von einem Konto auf ein anderes Konto übertragen, oder beide Konten bleiben unverändert und es liegt keine andere Situation vor.

• Isolierung: Eine Transaktion hat keine Auswirkungen auf eine andere Transaktion. Das heißt, es ist für jede Transaktion unmöglich, eine Transaktion in einem unvollständigen Zustand zu sehen. Beispielsweise kann sich bei einer Banküberweisung vor der Übermittlung der Überweisungstransaktion eine andere Überweisungstransaktion nur im Wartezustand befinden.

• Dauerhaftigkeit: Die Auswirkungen der Transaktionsverarbeitung können dauerhaft erhalten bleiben. Umgekehrt sollten Transaktionen allen Ausfällen standhalten können, einschließlich Server-, Prozess-, Kommunikations-, Medienausfällen usw. Beispiel: Beim Banküberweisungsprozess muss der Kontostand nach der Überweisung gespeichert werden.

2. Transaktionsstatus

SET AUTOCOMMIT = 0 , 禁止自动提交 SET AUTOCOMMIT = 1, 开启自动提交
START TRANSACTION：开始事务，autocommit设为0，如果已经有一个事务在运行，则会触发一个隐藏的COMMIT
COMMIT：提交事务，保存更改，释放锁
ROLLBACK：回滚本事务对数据库的所有更改，然后结束事务，释放锁
SAVEPOINT savepoint_name：创建一个savepoint识别符来ROLLBACK TO SAVEPOINT
ROLLBACK TO SAVEPOINT savepoint_name：回滚到从savepoint_name开始对数据库的所有更改，这样就允许回滚事务中的一部分，保证更改的一个子集被提交
SET TRANSACTION：允许设置事务的隔离级别
LOCK TABLES：允许显式的锁住一个或多个table，会隐式的关闭当前打开的事务，建议在执行LOCK TABLES语句之前显式的commit或rollback。
我们一般所以一般在事务代码里不会使用LOCK TABLES

3. Transaktionsvorgang

(1) Erstellen Sie zunächst die Mitarbeiterdatentabelle:

mysql> create table employee(
    -> employeeID char(4),
    -> name varchar(20) not null,
    -> job varchar(20),
    -> departmentID int
    -> );
Query OK, 0 rows affected (0.10 sec)

mysql> insert into employee value ('7513' , 'Nora Edwar' , 'Programmer', 128);
mysql> insert into employee value ('9006' , 'Candy Burn' , 'Systems Ad',128 );
mysql> insert into employee value ( '9842' , 'Ben Smith' ,  'DBA' , 42);
mysql> insert into employee value ('9843',  'Pert Park'  , 'DBA' , 42 );
mysql> insert into employee value ('9845' , 'Ben Patel'  , 'DBA' , 128 );
mysql> insert into employee value ('9846' , 'Red Right' ,  null, 128 );
mysql> insert into employee value ('9847' , 'Run Wild'  ,  null , 128 );
mysql> insert into employee value ('9848' , 'Rip This J' , null , 128 );
mysql> insert into employee value ('9849' , 'Rip This J' , null  , 128 );
mysql> insert into employee value ( '9850' , 'Reader U' ,   null , 128 );
mysql> insert into employee value ('6651',  'Ajay Patel' , 'Programmer', 128 );

mysql> select * from employee;
+------------+------------+------------+--------------+
| employeeID | name       | job        | departmentID |
+------------+------------+------------+--------------+
| 6651       | Ajay Patel | Programmer |          128 |
| 7513       | Nora Edwar | Programmer |          128 |
| 9006       | Candy Burn | Systems Ad |          128 |
| 9842       | Ben Smith  | DBA        |           42 |
| 9843       | Pert Park  | DBA        |           42 |
| 9845       | Ben Patel  | DBA        |          128 |
| 9846       | Red Right  | NULL       |          128 |
| 9847       | Run Wild   | NULL       |          128 |
| 9848       | Rip This J | NULL       |          128 |
| 9849       | Rip This J | NULL       |          128 |
| 9850       | Reader U   | NULL       |          128 |
| 6651       | Ajay Patel | Programmer |          128 |
+------------+------------+------------+--------------+

(2) SET AUTOCOMMIT=0:

mysql> set autocommit = 0;//禁止自动提交

mysql> insert into employee values(null,'test1',null,128);

mysql> savepoint s1;//创建一个savepoint识别符

mysql> insert into employee values(null,"test2",null,128);

mysql> savepoint s2;//创建一个savepoint识别符

mysql> insert into employee values(null,"test3",null,128);

mysql> savepoint s3;//创建一个savepoint识别符mysql> select * from employee;
+------------+------------+------------+--------------+| employeeID | name       | job        | departmentID |
+------------+------------+------------+--------------+| 6651       | Ajay Patel | Programmer |          128 |
| 7513       | Nora Edwar | Programmer |          128 |
| 9006       | Candy Burn | Systems Ad |          128 |
| 9842       | Ben Smith  | DBA        |           42 |
| 9843       | Pert Park  | DBA        |           42 |
| 9845       | Ben Patel  | DBA        |          128 |
| 9846       | Red Right  | NULL       |          128 |
| 9847       | Run Wild   | NULL       |          128 |
| 9848       | Rip This J | NULL       |          128 |
| 9849       | Rip This J | NULL       |          128 |
| 9850       | Reader U   | NULL       |          128 |
| 6651       | Ajay Patel | Programmer |          128 |
| NULL       | test1      | NULL       |          128 |
| NULL       | test2      | NULL       |          128 || NULL       | test3      | NULL    |    128 |
+------------+------------+------------+--------------+

(3) ROLLBACK ZUM SAVEPOINT:

mysql> rollback to savepoint s1;//回滚到s1标签处：mysql> select * from employee;
+------------+------------+------------+--------------+| employeeID | name       | job        | departmentID |
+------------+------------+------------+--------------+| 6651       | Ajay Patel | Programmer |          128 |
| 7513       | Nora Edwar | Programmer |          128 |
| 9006       | Candy Burn | Systems Ad |          128 |
| 9842       | Ben Smith  | DBA        |           42 |
| 9843       | Pert Park  | DBA        |           42 |
| 9845       | Ben Patel  | DBA        |          128 |
| 9846       | Red Right  | NULL       |          128 |
| 9847       | Run Wild   | NULL       |          128 |
| 9848       | Rip This J | NULL       |          128 |
| 9849       | Rip This J | NULL       |          128 |
| 9850       | Reader U   | NULL       |          128 |
| 6651       | Ajay Patel | Programmer |          128 || NULL       | test1      | NULL       |      128 |
+------------+------------+------------+--------------+

(4) COMMIT :

mysql> commit;//提交事务
mysql> rollback to savepoint s2;
//一旦事务提交了，就不能再回滚ERROR 1305 (42000): SAVEPOINT s2 does not exist

(5) SET AUTOCOMMIT=1:

mysql> set autocommit = 1;//自动提交事务

mysql>  insert into employee values(null,"test4",null,128);

mysql> savepoint s4;//一旦创建，自动提交mysql> select * from employee;
+------------+------------+------------+--------------+| employeeID | name       | job        | departmentID |
+------------+------------+------------+--------------+| 6651       | Ajay Patel | Programmer |          128 |
| 7513       | Nora Edwar | Programmer |          128 |
| 9006       | Candy Burn | Systems Ad |          128 |
| 9842       | Ben Smith  | DBA        |           42 |
| 9843       | Pert Park  | DBA        |           42 |
| 9845       | Ben Patel  | DBA        |          128 |
| 9846       | Red Right  | NULL       |          128 |
| 9847       | Run Wild   | NULL       |          128 |
| 9848       | Rip This J | NULL       |          128 |
| 9849       | Rip This J | NULL       |          128 |
| 9850       | Reader U   | NULL       |          128 |
| 6651       | Ajay Patel | Programmer |          128 |
| NULL       | test1      | NULL       |          128 |
| NULL       | test2      | NULL       |          128 |
| NULL       | test3      | NULL       |          128 || NULL       | test4      | NULL       |          128 |
+------------+------------+------------+--------------+mysql> rollback to s4;//此时就无法回滚了
ERROR 1305 (42000): SAVEPOINT s4 does not exist

4. Sperren

gemeinsame Sperre, exklusive Sperre, pessimistische Sperre, optimistische Sperre, Zeilenebene Sperren, Sperren auf Tabellenebene

• Gemeinsame Sperren: Fügen Sie beim Lesen von Daten eine gemeinsame Sperre zu den Daten hinzu. Das Teilen steht nicht im Widerspruch zum direkten Teilen, wohl aber zum Konflikt mit exklusiven Sperren.

• Exklusive Sperre: Installieren Sie beim Aktualisieren von Daten eine exklusive Sperre und verbieten Sie alle anderen Aktionen.

• Pessimistische Sperre: Wird verwendet, wenn es viele Aktualisierungen und wenige Abfragen gibt. Die pessimistische Sperre ist keine echte Sperre in der Datenbank, sondern die Einstellung der Menschen zu Transaktionen.

• Optimistische Sperre: Es gibt nur wenige Updates und sie wird für viele Abfragen verwendet. Es handelt sich nicht um eine echte Sperre in der Datenbank, sondern um die Einstellung der Menschen zu Transaktionen.

5. Parallelitätsverarbeitung

• Dirty Read: Eine Transaktion liest Daten, die nicht von einer anderen Transaktion übermittelt wurden
  Transaktion 1: Aktualisierung Ein Datenelement
  ————>Transaktion 2: Lesen Sie den durch Transaktion 1 aktualisierten Datensatz
  Transaktion 1: Rufen Sie Commit zum Senden auf
  Die von Transaktion 2 zu diesem Zeitpunkt gelesenen Daten sind die im Datenbankspeicher, sogenanntes Dirty Reading.
  Bei den gelesenen Daten handelt es sich um schmutzige Daten.
  Detaillierte Erklärung:
  Dirty Reading bedeutet: Wenn eine Transaktion auf Daten zugreift und die Daten geändert hat, diese Änderung jedoch noch nicht an die Datenbank übermittelt wurde, ist dies der Fall Zu diesem Zeitpunkt ,
  Eine andere Transaktion greift ebenfalls auf diese Daten zu und verwendet diese Daten dann. Da diese Daten noch nicht übermittelt wurden, handelt es sich bei den von einer anderen
  -Transaktion gelesenen Daten um fehlerhafte Daten, und die auf den fehlerhaften Daten basierenden Vorgänge sind möglicherweise falsch.

• Nicht wiederholbares Lesen: In derselben Transaktion werden dieselben Daten zweimal gelesen und der Inhalt ist unterschiedlich
  Transaktion 1: Abfrage eines Datensatzes
  ————- > Transaktion 2: Aktualisieren Sie die von Transaktion 1 abgefragten Datensätze
  ————->Transaktion 2: Rufen Sie Commit zum Senden auf
  Transaktion 1: Fragen Sie den letzten Datensatz erneut ab
  Zu diesem Zeitpunkt fragt Transaktion 1 ab die gleichen Daten zweimal, der verfügbare Inhalt ist unterschiedlich, was als nicht wiederholbares Lesen bezeichnet wird

• 幻读：同一事务中，用同样的操作读取两次，得到的记录数不相同 
  事务1：查询表中所有记录 
  ———->事务2：插入一条记录 
  ———->事务2：调用commit进行提交 
  事务1：再次查询表中所有记录 
  此时事务1两次查询到的记录是不一样的，称为幻读 
  详细解释： 
  幻读是指当事务不是独立执行时发生的一种现象，例如第一个事务对一个表中的数据进行了修改， 
  这种修改涉及到表中的全部数据行。同时，第二个事务也修改这个表中的数据，这种修改是向表 
  中插入一行新数据。那么，以后就会发生操作第一个事务的用户发现表中还有没有修改的数据行， 
  就好象发生了幻觉一样。

6、事务隔离

事务隔离五种级别：

TRANSACTION_NONE 不使用事务。 
TRANSACTION_READ_UNCOMMITTED 允许脏读。 
TRANSACTION_READ_COMMITTED 防止脏读，最常用的隔离级别,并且是大多数数据库的默认隔离级别 
TRANSACTION_REPEATABLE_READ 可以防止脏读和不可重复读， 
TRANSACTION_SERIALIZABLE 可以防止脏读，不可重复读取和幻读，（事务串行化）会降低数据库的效率

以上的五个事务隔离级别都是在Connection接口中定义的静态常量，

使用setTransactionIsolation(int level) 方法可以设置事务隔离级别。 
如:con.setTransactionIsolation(Connection.REPEATABLE_READ);

注意：事务的隔离级别受到数据库的限制，不同的数据库支持的的隔离级别不一定相同

summary： 
（1）Serializable：可避免脏读、不可重复读、虚读情况的发生。 
（2）Repeatable read：可避免脏读、不可重复读情况的发生。（可重复读，是 MySQL 默认的事务隔离级别） 
（3）Read committed：可避免脏读情况发生。（读取已提交的数据） 
（4）Read uncommitted：最低级别，以上情况均无法保证。（读取到了未提交的数据）

以上就是 【MySQL 06】事务处理的内容，更多相关内容请关注PHP中文网（www.php.cn）！