做数据库事务防止其他用户干扰它 [英] do database transactions prevent other users from interfering with it

问题描述

假设我做了（注意：下面的语法可能不正确，但不要担心...它只是在那里提出一点）

 开始事务
 INSERT INTO表（id，data）VALUES（100,20），（100,30）; 
 SELECT * FROM table WHERE id = 100; 
结束事务
  

因此，select的目标是从表中获取所有信息只是由前面的插入插入，只有前面的INSERT ...

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

事务的下一步中的SELECT语句还会获取由其他用户执行的INSERT插入的行，只是得到事务中前面INSERT插入的两行？

Btw，我使用MySQL，所以请定制您的答案MySQL

解决方案

这完全取决于DB连接使用的事务隔离。

根据：允许交易查看其他交易提交的未提交更改。此隔离级别允许脏读取，不可重复读取和幻影发生。

READ-COMMITTED ：允许交易只有在提交后才能查看其他交易所做的更改。未提交的更改保持不可见。此隔离级别允许发生不可重复的读取和幻影。

REPEATABLE READ （默认）：确保是一个事务发出同一个SELECT两次，它获得相同的结果，而不管其他事务所做的承诺或未提交的更改。换句话说，它从同一查询的不同执行获得一致的结果。在某些数据库系统中，REPEATABLE READ隔离级别允许幻像，如果另一个事务插入新行，在SELECT语句之间的内部，第二个SELECT将看到它们。这不是真的InnoDB;

SERIALIZABLE ：完全隔离一个事务与其他事务的影响。它类似于REPEATABLE READ，其中包含一个事务所选择的行在第一个事务完成之前不能更改的附加限制。

可以为您的DB会话在全局，会话或特定事务中设置隔离级别：

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

其中isolation_level是以下值之一：

• 'READ UNCOMMITTED'


• 'READ COMMITTED' code>


• 'REPEATABLE READ'


• 'SERIALIZABLE'

在 my.cnf 您还可以设置默认值：

  [mysqld] 
 transaction-isolation = 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

这篇关于做数据库事务防止其他用户干扰它的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！