事务隔离级别 transation isolation

JDBC 资料：
java.sql.Connection

Spring 资料：
org.springframework.transaction.annotation.Isolation

MySQL 资料：
https://dev.mysql.com/doc/refman/8.0/en/innodb-transaction-isolation-levels.html

SQL Server 资料：
https://learn.microsoft.com/en-us/sql/t-sql/statements/set-transaction-isolation-level-transact-sql?view=sql-server-ver16

Oracle 资料：
https://docs.oracle.com/en/database/oracle/oracle-database/19/cncpt/transactions.html#GUID-B97790CB-DF82-442D-B9D5-50CCE6BF9FBD

Derby 资料：
https://docs.oracle.com/javadb/10.8.3.0/devguide/cdevconcepts15366.html

 Berkeley 资料：

https://docs.oracle.com/database/bdb181/html/gsg_txn/CXX/isolation.html

 

 

 

隔离级别	java.sql.Connection	备注
READ UNCOMMITTED	A constant indicating that dirty reads, non-repeatable reads and phantom reads can occur.This level allows a row changed by one transaction to be readby another transaction before any changes in that row have been committed (a "dirty read"). If any of the changes are rolled back,the second transaction will have retrieved an invalid row.	脏读、不可重复读、幻读可能发生。
READ COMMITTED	A constant indicating thatdirty reads are prevented; non-repeatable reads and phantom reads can occur. This level only prohibits a transaction from reading a row with uncommitted changes in it.	禁止脏读；不可重复读、幻读可能发生。
REPEATABLE READ	A constant indicating that dirty reads and non-repeatable reads are prevented; phantom reads can occur. This level prohibits a transaction from reading a row with uncommitted changes in it, and it also prohibits the situation where one transaction reads a row,a second transaction alters the row, and the first transaction rereads the row, getting different values the second time(a "non-repeatable read").	禁止脏读，禁止重复度；幻读可能发生。
SERIALIZABLE	A constant indicating that dirty reads, non-repeatable reads and phantom reads are prevented.This level includes the prohibitions in TRANSACTION_REPEATABLE_READ and further prohibits the situation where one transaction reads all rows that satisfya WHERE condition, a second transaction inserts a row that satisfies that WHERE condition, and the first transaction rereads for the same condition, retrieving the additional"phantom" row in the second read.	禁止脏读，禁止不可重复度，禁止幻读。

 

补充：

java.sql.Connection.TRANSACTION_NONE  代表不支持事务

org.springframework.transaction.annotation.Isolation 是@Transactional 注解的属性值，且默认取值 DEFAULT ，代表使用数据库的隔离级别

mysql 查看隔离级别 

show VARIABLES where VARIABLE_name like '%isolation%'

 

Navicat  MySQL 开启事务：

start TRANSACTION;
insert aa values ('11');
commit;
-- rollback;

 

 MySQL InnoDB 默认事务隔离级别是：REPEATABLE READ

 MySQL 可以修改事务隔离级别：

SET [GLOBAL | SESSION] TRANSACTION
    transaction_characteristic [, transaction_characteristic] ...

transaction_characteristic: {
    ISOLATION LEVEL level
  | access_mode
}

level: {
     REPEATABLE READ
   | READ COMMITTED
   | READ UNCOMMITTED
   | SERIALIZABLE
}

access_mode: {
     READ WRITE
   | READ ONLY
}

Transaction Characteristic Scope
You can set transaction characteristics globally, for the current session, or for the next transaction only:

With the GLOBAL keyword:

The statement applies globally for all subsequent sessions.

Existing sessions are unaffected.

With the SESSION keyword:

The statement applies to all subsequent transactions performed within the current session.

The statement is permitted within transactions, but does not affect the current ongoing transaction.

If executed between transactions, the statement overrides any preceding statement that sets the next-transaction value of the named characteristics.

Without any SESSION or GLOBAL keyword:

The statement applies only to the next single transaction performed within the session.

Subsequent transactions revert to using the session value of the named characteristics.

 

 

Anomaly	Example
Dirty Reads A dirty read happens when a transaction reads data that is being modified by another transaction that has not yet committed.	Transaction A begins. UPDATE employee SET salary = 31650 WHERE empno = '000090' Transaction B begins. SELECT * FROM employee (Transaction B sees data updated by transaction A. Those updates have not yet been committed.)
Nonrepeatable Reads Nonrepeatable reads happen when a query returns data that would be different if the query were repeated within the same transaction. Nonrepeatable reads can occur when other transactions are modifying data that a transaction is reading.	Transaction A begins. SELECT * FROM employee WHERE empno = '000090' Transaction B begins. UPDATE employee SET salary = 30100 WHERE empno = '000090' (Transaction B updates rows viewed by transaction A before transaction A commits.) If Transaction A issues the same SELECT statement, the results will be different.
Phantom Reads Records that appear in a set being read by another transaction. Phantom reads can occur when other transactions insert rows that would satisfy the WHERE clause of another transaction's statement.	Transaction A begins. SELECT * FROM employee WHERE salary > 30000 Transaction B begins. INSERT INTO employee (empno, firstnme, midinit, lastname, job, salary) VALUES ('000350', 'NICK', 'A','GREEN','LEGAL COUNSEL',35000) Transaction B inserts a row that would satisfy the query in Transaction A if it were issued again.

 

事务原子行，一致性，隔离性，持久性

https://dev.mysql.com/doc/refman/8.0/en/glossary.html#glos_acid

An acronym standing for atomicity, consistency, isolation, and durability. These properties are all desirable in a database system, and are all closely tied to the notion of a transaction. The transactional features of InnoDB adhere to the ACID principles.

Transactions are atomic units of work that can be committed or rolled back. When a transaction makes multiple changes to the database, either all the changes succeed when the transaction is committed, or all the changes are undone when the transaction is rolled back.

The database remains in a consistent state at all times — after each commit or rollback, and while transactions are in progress. If related data is being updated across multiple tables, queries see either all old values or all new values, not a mix of old and new values.

Transactions are protected (isolated) from each other while they are in progress; they cannot interfere with each other or see each other's uncommitted data. This isolation is achieved through the locking mechanism. Experienced users can adjust the isolation level, trading off less protection in favor of increased performance and concurrency, when they can be sure that the transactions really do not interfere with each other.

The results of transactions are durable: once a commit operation succeeds, the changes made by that transaction are safe from power failures, system crashes, race conditions, or other potential dangers that many non-database applications are vulnerable to. Durability typically involves writing to disk storage, with a certain amount of redundancy to protect against power failures or software crashes during write operations. (In InnoDB, the doublewrite buffer assists with durability.)

 

Atomicity（原子性）：

一个事务（transaction）中的所有操作，要么全部完成，要么全部不完成，不会结束在中间某个环节。事务在执行过程中发生错误，会被恢复（Rollback）到事务开始前的状态，就像这个事务从来没有执行过一样。

Consistency（一致性）：

在事务开始之前和事务结束以后，数据库的完整性没有被破坏。这表示写入的资料必须完全符合所有的预设规则，这包含资料的精确度、串联性以及后续数据库可以自发性地完成预定的工作。

 

Isolation（隔离性）：

数据库允许多个并发事务同时对其数据进行读写和修改的能力，隔离性可以防止多个事务并发执行时由于交叉执行而导致数据的不一致。事务隔离分为不同级别，包括读未提交（Read uncommitted）、读提交（read committed）、可重复读（repeatable read）和串行化（Serializable）。

Durability（持久性）：

事务处理结束后，对数据的修改就是永久的，即便系统故障也不会丢失。

 

思考：分布式事务如何保证ACID ??    消息驱动如何保证ACID??