SQL:在 REPEATABLE READ 隔离级别，是否将 UPDATE 锁保持到事务结束? [英] SQL: at REPEATABLE READ isolation level, are UPDATE locks held to the end of the transaction?

问题描述

我已阅读有关可重复阅读的内容导致 SELECT 语句持有的锁一直持有到事务结束.UPDATE 语句采用的排他锁也是如此吗?那么，是不是我在事务中UPDATE一行时，后续的SELECT会返回UPDATE留下的值?

I have read about how REPEATABLE READ causes locks held by SELECT statements to be held to the end of the transaction. Is the same true for exclusive locks taken by UPDATE statements? Consequentially, is it the case that when I UPDATE a row in a transaction, subsequent SELECTs will return the value left by the UPDATE?

所以我明白，如果我在事务 1 中选择一行，那么事务 2 在事务 1 完成之前无法更新它.但是，如果我 UPDATE 事务 1 中的行，事务 2 是否还要等待事务 1 完成，事务 2 才能 UPDATE 它?

So I understand that if I SELECT a row in transaction 1, then transaction 2 cannot UPDATE it until transaction 1 completes. However, if I UPDATE The row in transaction 1, will transaction 2 still have to wait for transaction 1 to complete before transaction 2 can UPDATE it?

推荐答案

除非第二个事务在隔离级别 READ UNCOMMITED 下运行，否则第一个事务中的 UPDATE 语句采用的排他锁将阻止任何选择，直到第一个事务提交.

Unless a second transaction is running at Isolation level READ UNCOMMITED, exclusive locks taken by UPDATE statements in the first transaction will block any selects, until the first transaction commits.

所以我明白如果我在事务 1 中选择一行，那么事务 2 无法更新它，直到事务 1 完成.然而，如果我更新事务 1 中的行，事务 2 是否仍然需要等待事务 1 完成，然后事务 2 才能更新它?

So I understand that if I SELECT a row in transaction 1, then transaction 2 cannot UPDATE it until transaction 1 completes. However, if I UPDATE The row in transaction 1, will transaction 2 still have to wait for transaction 1 to complete before transaction 2 can UPDATE it?

是的.

如果在 REPEATABLE READ 下，您在事务 1 中执行 SELECT，事务 2 仍然可以在事务 1 中添加与 SELECT 的 WHERE 子句匹配的新数据.这是因为事务 1 将行读取锁定所有检索到的数据，但不锁定范围.

If under REPEATABLE READ, you perform a SELECT in transaction 1, transaction 2 could still add new data that matches the WHERE clause of the SELECT in transaction 1. This is because transaction 1 places row read locks on all retrieved data but not range locks.

这篇关于SQL:在 REPEATABLE READ 隔离级别，是否将 UPDATE 锁保持到事务结束?的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！