数据库事务是否防止其他用户干扰它

2022-01-06 00:00:00 sql database transactions mysql

假设我这样做了(注意:下面的语法可能不正确，但不要担心……它只是为了说明问题)

开始交易INSERT INTO table (id, data) VALUES (100,20), (100,30);SELECT * FROM table WHERE id = 100;结束交易

因此选择的目标是从表中获取所有信息，这些信息刚刚被前面的插入插入，并且只被前面的插入......

现在假设在执行过程中，在执行 INSERT 之后，其他一些用户也执行了一个 id = 100 的 INSERT...

事务下一步的 SELECT 语句是否也会得到其他用户执行的 INSERT 插入的行，还是只会得到事务中前一个 INSERT 插入的两行?

顺便说一句，我正在使用 MySQL，所以请根据 MySQL 定制您的答案

解决方案

这完全取决于 DB Connection 使用的事务隔离.

根据 :允许事务查看其他事务所做的未提交更改.此隔离级别允许发生脏读、不可重复读和幻像.

  • READ-COMMITTED :允许事务仅在其他事务已提交时查看其他事务所做的更改.未提交的更改仍然不可见.此隔离级别允许不可重复读取和幻像发生.
  • 可重复阅读(默认):确保一个事务两次发出相同的 SELECT，无论其他事务是否已提交或未提交的更改，它都会获得相同的结果.换句话说，它从同一查询的不同执行中获得一致的结果.在某些数据库系统中，REPEATABLE READ 隔离级别允许幻象，这样如果另一个事务在 SELECT 语句之间插入新行，第二个 SELECT 将看到它们.对于 InnoDB 而言，情况并非如此；REPEATABLE READ 级别不会出现幻像.
  • SERIALIZABLE :将一项交易的影响与其他交易的影响完全隔离.它类似于 REPEATABLE READ，但有一个附加限制，即一个事务选择的行在第一个事务完成之前不能被另一个更改.

    • 可以在全局、会话内或特定事务中为您的数据库会话设置隔离级别:

    SET GLOBAL TRANSACTION ISOLATION LEVEL isolation_level;设置会话事务隔离级别隔离级别；设置事务隔离级别隔离级别；

    其中isolation_level 是以下值之一:

    • '阅读未提交'
    • 'READ COMMITTED'
    • '可重复阅读'
    • 'SERIALIZABLE'

    在my.cnf中你也可以设置默认值:

    [mysqld]事务隔离 = READ-COMMITTED

    Suppose I do (note: the syntax below is probably not correct, but don't worry about it...it's just there to make a point)

    Start Transaction
INSERT INTO table (id, data) VALUES (100,20), (100,30);
SELECT * FROM table WHERE id = 100;
End Transaction

    Hence the goal of the select is to get ALL info from the table that just got inserted by the preceding insert and ONLY by the preceding INSERT...

    Now suppose that during the execution, after the INSERT got executed, some other user also performs an INSERT with id = 100...

    Will the SELECT statement in the next step of the transaction also get the row inserted by the executed INSERT by the other user or will it just get the two rows inserted by the preceding INSERT within the transaction?

    Btw, I'm using MySQL so please tailor your answer to MySQL

    解决方案

    This depends entirely on the Transaction Isolation that is used by the DB Connection.

    According to MySQL 5.0 Certification Study Guide

    Page 420 describes three transactional conditions handled by Isolation Levels

    • A dirty read is a read by one transaction of uncommitted changes made by another. Suppose the transaction T1 modifies a row. If transaction T2 reads the row and sees the modificationeven though T1 has not committed it, that is a dirty read. One reason this is a problem is that if T1 roll backs, the change is undone but T2 does not know that.
    • A non-repeatable read occurs when a transaction performs the same retrieval twice but gets a different result each time.Suppose that T1 reads some rows, and that T2 then changes some of those rows and commits the changes. If T1 sees the changes when it reads the rows again, it gets a different result; the initial read is non-repeatable. This is a problem because T1 does not get a consistent result from the same query.
    • A phantom is a row that appears where it was not visible before. Suppose that T1 and T2 begin, and T1 reads some rows. If T2 inserts a new and T1 sees that row when it reads again, the row is a phantom.

    Page 421 describes the four(4) Transaction Isolation Levels:

    • READ-UNCOMMITTED : allows a transaction to see uncommitted changes made by other transactions. This isolation level allows dirty reads, non-repeatable reads, and phantoms to occur.
    • READ-COMMITTED : allows a transaction to see changes made by other transactions only if they've been committed. Uncommitted changes remains invisible. This isolation level allows non-repeatable reads, and phantoms to occur.
    • REPEATABLE READ (default) : ensure that is a transaction issues the same SELECT twice, it gets the same result both times, regardless of committed or uncommitted changesmade by other transactions. In other words, it gets a consistent result from different executions of the same query. In some database systems, REPEATABLE READ isolation level allows phantoms, such that if another transaction inserts new rows,in the inerbal between the SELECT statements, the second SELECT will see them. This is not true for InnoDB; phantoms do not occur for the REPEATABLE READ level.
    • SERIALIZABLE : completely isolates the effects of one transaction from others. It is similar to REPEATABLE READ with the additional restriction that rows selected by one transaction cannot be changed by another until the first transaction finishes.

    Isolation level can be set for your DB Session globally, within your session, or for a specific transaction:

    SET GLOBAL TRANSACTION ISOLATION LEVEL isolation_level;
SET SESSION TRANSACTION ISOLATION LEVEL isolation_level;
SET TRANSACTION ISOLATION LEVEL isolation_level;

    where isolation_level is one of the following values:

    • 'READ UNCOMMITTED'
    • 'READ COMMITTED'
    • 'REPEATABLE READ'
    • 'SERIALIZABLE'

    In my.cnf you can set the default as well:

    [mysqld]
transaction-isolation = READ-COMMITTED

    相关文章