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

问题描述

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

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                --             --              --

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

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)

以下是事务隔离中可能出现的三种现象
脏读-无锁
Nonrepeatable Read - 没有脏读作为锁提交的数据
幻读 - 锁定sql块(使用select查询选择)

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)

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

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

PS:我已经浏览了 oracle 中的隔离级别 中的链接，但它们看起来笨拙地谈论特定于数据库的内容

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

推荐答案

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

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

For example, you have 3 concurrent processes 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 the same table T.

• READ UNCOMMITTED - 没有锁定表.您可以在写入数据的同时读取表中的数据.这意味着 A 写入数据(未提交)，B 可以读取此未提交数据并使用它(用于任何目的).如果 A 执行回滚，B 仍然读取数据并使用它.这是处理数据的最快但最不安全的方式，因为这会导致在物理上不相关的表中出现数据漏洞(是的，在实际应用中，两个表可以在逻辑上但物理上不相关=).
• READ COMMITTED - 锁定已提交的数据.您可以读取仅提交的数据.这意味着 A 写入数据，B 无法读取 A 保存的数据，直到 A 执行提交.这里的问题是 C 可以更新在 B 上读取和使用的数据，而 B 客户端不会有更新的数据.
• REPEATABLE READ - 锁定 SQL 块(使用选择查询选择).这意味着 B 在某种条件下读取数据，即 WHERE aField >10 AND aField <20，A插入aField值在10到20之间的数据，然后B再次读取数据，得到不同的结果.
• SERIALIZABLE - 锁定一个完整的表(在该表上触发 Select 查询).这意味着，B 读取数据并且没有其他事务可以修改表上的数据.这是处理数据的最安全但最慢的方式.此外，由于简单的读取操作锁定了表，这可能会导致生产上的严重问题:假设 T 表是一个 Invoice 表，用户 X 想知道当天的发票，而用户 Y 想要创建新发票，因此当 X 执行读取发票时，Y 无法添加新发票(当涉及到钱时，人们会非常生气，尤其是老板).

• READ UNCOMMITTED - no lock on the table. You can read data in the table while writing on it. This means A writes data (uncommitted) and B can read this uncommitted 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 committed. 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 a 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 a 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, especially the bosses).

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

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

使用 JDBC，您可以使用 Connection#setTransactionIsolation.

Using JDBC, you define it using Connection#setTransactionIsolation.

使用休眠:

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

哪里

• 1:阅读未提交的内容
• 2:读已提交
• 4:可重复阅读
• 8:可序列化

Hibernate 配置取自这里(抱歉，它是西班牙语的).

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

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

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

• MySQL 隔离级别、
• SQL Server 隔离级别
• Informix 隔离级别(个人注意:我永远不会忘记 SET ISOLATION TO DIRTY READ 这句话.)

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

等等……

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