SQL Server 2012 - 如何实现“可重复读取"?隔离级别工作? [英] SQL Server 2012 - How does "Repeatable Read" isolation level work?

问题描述

我觉得我应该知道这一点，但我找不到任何具体概述这一点的内容，所以在这里.

SQL Server 的文档将可重复读取描述为:><块引用>

指定语句不能读取已修改的数据但尚未被其他事务提交并且没有其他事务可以修改当前事务已经读取的数据，直到当前事务完成

这是有道理的，但是当这些情况之一出现时，实际上会发生什么?例如，如果事务 A 读取第 1 行，然后事务 B 尝试更新第 1 行，会发生什么?事务 B 是否要等到事务 A 完成后再重试?还是抛出异常?

解决方案

REPEATABLE READ 对查询计划运算符在事务期间读取的所有行采用 S 锁.您的问题的答案如下:

1. 如果先读取，则 S 锁定该行，写入必须等待.
2. 如果先写入，则 S-lock 等待写入提交.

在 Hekaton 下它的工作方式不同，因为没有锁.

I feel like I should know this, but I can't find anything that specifically outlines this, so here goes.

The documentation for SQL Server describes REPEATABLE READ as:

Specifies that statements cannot read data that has been modified but not yet committed by other transactions and that no other transactions can modify data that has been read by the current transaction until the current transaction completes

This makes sense, but what actually happens when one of these situation arises? If, for example, Transaction A reads row 1, and then Transaction B attempts to update row 1, what happens? Does Transaction B wait until Transaction A has finished and then try again? Or is an exception thrown?

解决方案

REPEATABLE READ takes S-locks on all rows that have been read by query plan operators for the duration of the transaction. The answer to your question follows from that:

1. If the read comes first it S-locks the row and the write must wait.
2. If the write comes first the S-lock waits for the write to commit.

Under Hekaton it works differently because there are no locks.

这篇关于SQL Server 2012 - 如何实现“可重复读取"?隔离级别工作?的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！