事务隔离级别与表上的锁定关系 [英] Transaction isolation levels relation with locks on table

问题描述

我已经阅读了大约4个级别的隔离：

 隔离级别脏读不可重复读取幻像读取
 READ UNCOMMITTED允许允许允许
读取允许 - 允许允许
重复阅读 -   - 允许
 SERIALIZABLE  -   -   -  
  

我想了解每个事务隔离对表的锁定

  READ UNCOMMITTED  - 没有锁定表
 READ COMMITTED  - 锁定已提交的数据
 REPEATABLE READ  - 锁定sql块（使用select查询选择）
 SERIALIZABLE  - 锁定全表（在其上触发Select查询）
  

以下是三种现象可以在交易隔离中发生


脏读 - 无锁


不可重复读 - 没有脏读作为已提交数据的锁定


幻像读取 - 锁定sql块（通过使用选择查询选择）

我想了解我们在哪里定义这些隔离级别：仅在jdbc / hibernate级别或在DB中

PS：我已经浏览了 oracle中的隔离级别中的链接，但是它们看起来很笨拙并且在数据库特定方面进行讨论

解决方案

我想了解每个事务隔离对表执行的锁定

例如，您有3个并发进程A，B和C.启动事务，写入数据和提交/回滚（取决于结果）。 B只执行 SELECT 语句来读取数据。 C读取和更新数据。所有这些过程都在同一个表T上工作。

• READ UNCOMMITTED - 没有锁定表。您可以在书写时读取表格中的数据。这意味着，A写入数据（未提交），B可以读取此未提交的数据并使用它（出于任何目的）。如果A执行回滚，则B仍然读取数据并使用它。这是处理数据的最快但最不安全的方式，因为这可能导致物理上不相关的表中的数据漏洞（是的，两个表可以在逻辑上但在实际应用中没有物理相关= \）。


• READ COMMITTED - 锁定已提交的数据。您可以读取仅提交的数据。这意味着，A写入数据，B不能读取A保存的数据，直到A执行提交。这里的问题是C可以更新在B上读取和使用的数据，B客户端将没有更新的数据。


• REPEATABLE READ - 锁定sql块（已选中）通过使用选择查询）。这意味着，B在某些条件下读取数据，即 WHERE aField> 10和aField< 20 ，A插入数据，其中 aField 值介于10和20之间，然后B再次读取数据并获得不同的结果。


• SERIALIZABLE - 锁定全表（触发Select查询）。这意味着，B读取数据并且没有其他事务可以修改表上的数据。这是处理数据的最安全但最慢的方法。此外，由于简单的读取操作锁定表，这可能导致生产中的严重问题：假设T表是发票表，用户X想知道当天的发票和用户Y想要的创建一个新的发票，所以当X执行发票的读取时，Y不能添加新的发票（当它是关于钱的时候，人们真的很生气，特别是老板）。

我想了解我们在哪里定义这些隔离级别：仅在jdbc / hibernate级别或DB中的

使用JDBC，使用 连接＃setTransactionIsolation

使用Hibernate：

 < property name =hibernate.connection.isolation > 2'; /性> 
  

其中

• 1：READ UNCOMMITTED


• 2：READ COMMITTED


• 4：REPEATABLE READ


• 8：SERIALIZABLE

Hibernate配置取自这里（抱歉，它是西班牙文）。

顺便说一下，你也可以在RDBMS上设置隔离级别：

• MySQL隔离级别，


• SQL Server隔离级别


• Informix隔离级别（个人注意：我永远不会忘记 SET ISOLATION TO DIRTY READ 句子。）

等等...... / p>

I have read about 4 levels of isolation:

Isolation Level       Dirty Read    Nonrepeatable Read  Phantom Read  
READ UNCOMMITTED      Permitted       Permitted           Permitted
READ COMMITTED              --        Permitted           Permitted
REPEATABLE READ             --             --             Permitted
SERIALIZABLE                --             --              --

I want to understand the lock each transaction isolation takes on the table

READ UNCOMMITTED - no lock on table
READ COMMITTED - lock on committed data
REPEATABLE READ - lock on block of sql(which is selected by using select query)
SERIALIZABLE - lock on full table(on which Select query is fired)

below are the three phenomena which can occur in transaction isolation
Dirty Read- no lock
Nonrepeatable Read - no dirty read as lock on committed data
Phantom Read - lock on block of sql(which is selected by using select query)

I want to understand where we define these isolation levels : only at jdbc/hibernate level or in DB also

PS: I have gone through the links in Isolation levels in oracle, but they looks clumsy and talk on database specific

解决方案

I want to understand the lock each transaction isolation takes on the table

For example, you have 3 concurrent process A, B and C. A starts a transaction, writes data and commit/rollback (depending on results). B just executes a SELECT statement to read data. C reads and updates data. All these process work on same table T.

• READ UNCOMMITTED - no lock on table. You can read data in the table while writing on it. This means, A writes data (uncommited) and B can read this uncommited data and use it (for any purpose). If A executes a rollback, B still has read the data and used it. This is the fastest but most insecure way to work with data since can lead to data holes in not physically related tables (yes, two tables can be logically but not physically related in real world apps =\).
• READ COMMITTED - lock on committed data. You can read the data that was only commited. This means, A writes data and B can't read the data saved by A until A executes a commit. The problem here is that C can update data that was read and used on B and B client won't have the updated data.
• REPEATABLE READ - lock on block of sql(which is selected by using select query). This means, B reads the data under some condition i.e. WHERE aField > 10 AND aField < 20, A inserts data where aField value is between 10 and 20, then B reads the data again and get a different result.
• SERIALIZABLE - lock on full table(on which Select query is fired). This means, B reads the data and no other transaction can modify the data on the table. This is the most secure but slowest way to work with data. Also, since a simple read operation locks the table, this can lead to heavy problems on production: imagine that T table is an Invoice table, user X wants to know the invoices of the day and user Y wants to create a new invoice, so while X executes the read of the invoices, Y can't add a new invoice (and when it's about money, people get really mad, specially the bosses).

I want to understand where we define these isolation levels : only at jdbc/hibernate level or in DB also

Using JDBC, you define it using Connection#setTransactionIsolation.

Using Hibernate:

<property name="hibernate.connection.isolation">2</property>

Where

• 1: READ UNCOMMITTED
• 2: READ COMMITTED
• 4: REPEATABLE READ
• 8: SERIALIZABLE

Hibernate configuration taken from here (sorry, it's in Spanish).

By the way, you can set the isolation level on RDBMS as well:

• MySQL isolation level,
• SQL Server isolation level
• Informix isolation level (Personal Note: I will never forget about SET ISOLATION TO DIRTY READ sentence.)

and on and on...

这篇关于事务隔离级别与表上的锁定关系的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！