mysql事务：一个事务进行查询，另一个事务进行改、删除、插入等操作

时间：2021-07-09 12:43:50

mysql事务不加锁一致性读：当事务隔离级别为可重复读时，事务A进行查询，事务B进行改、删除、插入时，进行查询的事务A不受事务B的影响，仍然为原始数据库中的数据，即使事务A内对数据做了更改会提交，事务A查询到的仍然为事务开始之前数据库中的数据；当事务隔离级别为读提交时，事务A会读取事务A内的最新提交的数据，如果事务B对事务有更改或提交新数据，不会影响事务A的查询数据。

mysql说明文档：

       With READ COMMITTED isolation level, each consistent read within a transaction sets and reads its own fresh snapshot.

Consistent read is the default mode in which InnoDB processesSELECT statements in READ COMMITTED and REPEATABLE READ isolation levels. A consistent read does not set any locks on the tables it accesses, and therefore other sessions are free to modify those tables at the same time a consistent read is being performed on the table.

Suppose that you are running in the default REPEATABLE READ isolation level. When you issue a consistent read (that is, an ordinarySELECT statement), InnoDB gives your transaction a timepoint according to which your query sees the database. If another transaction deletes a row and commits after your timepoint was assigned, you do not see the row as having been deleted. Inserts and updates are treated similarly.

Note

The snapshot of the database state applies to SELECT statements within a transaction, not necessarily toDML statements. If you insert or modify some rows and then commit that transaction, aDELETE or UPDATE statement issued from another concurrent REPEATABLE READ transaction could affect those just-committed rows, even though the session could not query them. If a transaction does update or delete rows committed by a different transaction, those changes do become visible to the current transaction. For example, you might encounter a situation like the following: 

SELECT COUNT(c1) FROM t1 WHERE c1 = 'xyz'; -- Returns 0: no rows match.
DELETE FROM t1 WHERE c1 = 'xyz'; -- Deletes several rows recently committed by other transaction.

SELECT COUNT(c2) FROM t1 WHERE c2 = 'abc'; -- Returns 0: no rows match.
UPDATE t1 SET c2 = 'cba' WHERE c2 = 'abc'; -- Affects 10 rows: another txn just committed 10 rows with 'abc' values.
SELECT COUNT(c2) FROM t1 WHERE c2 = 'cba'; -- Returns 10: this txn can now see the rows it just updated.

You can advance your timepoint by committing your transaction and then doing anotherSELECT or START TRANSACTION WITH CONSISTENT SNAPSHOT.

This is called multi-versioned concurrency control.

In the following example, session A sees the row inserted by B only when B has committed the insert and A has committed as well, so that the timepoint is advanced past the commit of B.

             Session A              Session B

           SET autocommit=0;      SET autocommit=0;
time
|          SELECT * FROM t;
|          empty set
|                                 INSERT INTO t VALUES (1, 2);
|
v          SELECT * FROM t;
           empty set
                                  COMMIT;

           SELECT * FROM t;
           empty set

           COMMIT;

           SELECT * FROM t;
           ---------------------
           |    1    |    2    |
           ---------------------
           1 row in set

       If you want to see the “freshest” state of the database, use either theREAD COMMITTED isolation level or a locking read: 

SELECT * FROM t LOCK IN SHARE MODE;

With READ COMMITTED isolation level, each consistent read within a transaction sets and reads its own fresh snapshot. WithLOCK IN SHARE MODE, a locking read occurs instead: ASELECT blocks until the transaction containing the freshest rows ends (seeSection 14.2.5, “Locking Reads (SELECT ... FOR UPDATE and SELECT ... LOCK IN SHARE MODE)”).

Consistent read does not work over certain DDL statements:

  • Consistent read does not work over DROP TABLE, because MySQL cannot use a table that has been dropped andInnoDB destroys the table.

  • Consistent read does not work over ALTER TABLE, because that statement makes a temporary copy of the original table and deletes the original table when the temporary copy is built. When you reissue a consistent read within a transaction, rows in the new table are not visible because those rows did not exist when the transaction's snapshot was taken. In this case, the transaction returns an error as of MySQL 5.6.6:ER_TABLE_DEF_CHANGED, “Table definition has changed, please retry transaction”.

The type of read varies for selects in clauses like INSERT INTO ... SELECT, UPDATE ... (SELECT), and CREATE TABLE ... SELECT that do not specify FOR UPDATE or LOCK IN SHARE MODE:


标签：

相关文章