可重复阅读 - 我理解对吗? [英] Repeatable Read - am I understanding this right?

问题描述

试图完全理解 SQL Server 隔离级别 - 特别是可重复读.

我有一个 sproc，它启动一个事务并在一些数据周围放置一个光标(嘘声).这可能是相当大的数据块，因此可能需要一段时间才能完成.

然后它会 COMMIT 或 ROLLBACK.

在此期间，在事务关闭之前，如果有人调用一个方法导致其中一些受影响的行被读取，我的理解是该方法将停止，直到第一个方法完成.然后将向他们提供数据(只要没有先发生超时)

我认为我是对的，但问题是 - 我是吗?！

解决方案

REPEATABLE READ 防止 SELECTs 在事务结束之前解除他们放置的共享锁.>

将事务1设为READ COMMITTED，您可以在选中事务2后更新其中的一行在事务 1 中.

将事务 1 设为 REPEATABLE READ，您在选择事务2 后不能更新它在事务 1 中.

场景:

已提交阅读

1 SELECT -- 放置一个共享锁并立即解除它.2 UPDATE -- 放置一个排它锁.成功.1 SELECT -- 尝试放置共享锁，但它与 2. Locks 放置的排他锁冲突.

可重复阅读

1 SELECT -- 放置一个共享锁并保留它2 UPDATE -- 尝试放置一个排他锁，但它与共享锁不兼容.锁1 SELECT -- 锁已经放置.成功.

更新:

至于你的问题:在SQL Server中，SELECTs即使使用REPEATABLE READ也不会相互锁定，因为它们放置的共享锁是相互兼容:

CREATE TABLE t_lock (id INT NOT NULL PRIMARY KEY, value INT NOT NULL)插入INTO t_lock值 (1, 1)-- 第 1 节设置事务隔离级别可重复读取开始交易声明@id INT声明 cr_lock 游标动态为了选择 ID从 t_lock打开 cr_lock取回 cr_lockID——1-- 第 2 节设置事务隔离级别可重复读取开始交易声明@id INT声明 cr_lock 游标动态为了选择 ID从 t_lock打开 cr_lock取回 cr_lockID——1-- 第 1 节解除分配 cr_lock犯罪-- 第 2 节解除分配 cr_lock犯罪

Trying to completely understand SQL Server Isolation Levels - notably REPEATABLE READ.

I have a sproc that starts a transaction and puts a cursor around some data (boo hiss). This can be a fair chunk of data, so can take a while to do.

It will then COMMIT or ROLLBACK.

During this time, before the transaction has been closed, if someone calls a method which causes some of those affected rows to be READ, my understanding is that this method will stall until the first method is complete. They will then be served up the data (as long as a time-out doesn't occur first)

I think I'm right, but question is - am I?!

解决方案

REPEATABLE READ prevents SELECTs from lifting shared locks they placed until the end of the transaction.

With transaction 1 as READ COMMITTED, you can update a row in transaction 2 after you selected it in transaction 1.

With transaction 1 as REPEATABLE READ, you cannot update a row in transaction 2 after you selected it in transaction 1.

The scenarios:

READ COMMITTED

1 SELECT -- places a shared lock and immediately lifts it.
2 UPDATE -- places an exclusive lock. Succeeds.
1 SELECT -- tries to place a shared lock but it conflicts with the exclusive lock placed by 2. Locks.

REPEATABLE READ

1 SELECT -- places a shared lock and keeps it
2 UPDATE -- tries to places an exclusive lock but it's not compatible with the shared lock. Locks
1 SELECT -- the lock is already placed. Succeeds.

Update:

As for you question: in SQL Server, SELECTs will not lock each other even with REPEATABLE READ, since shared locks they place are compatible with each other:

CREATE TABLE t_lock (id INT NOT NULL PRIMARY KEY, value INT NOT NULL)
INSERT
INTO    t_lock
VALUES (1, 1)

-- Session 1

SET TRANSACTION ISOLATION LEVEL REPEATABLE READ
BEGIN TRANSACTION
DECLARE @id INT
DECLARE cr_lock CURSOR DYNAMIC
FOR
SELECT  id
FROM    t_lock
OPEN    cr_lock
FETCH   cr_lock

id
--
1

-- Session 2

SET TRANSACTION ISOLATION LEVEL REPEATABLE READ
BEGIN TRANSACTION
DECLARE @id INT
DECLARE cr_lock CURSOR DYNAMIC
FOR
SELECT  id
FROM    t_lock
OPEN    cr_lock
FETCH   cr_lock

id
--
1

-- Session 1

DEALLOCATE cr_lock
COMMIT

-- Session 2

DEALLOCATE cr_lock
COMMIT

这篇关于可重复阅读 - 我理解对吗?的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！