"Read Community" and "Reperationable Read" isolation level: in -depth differences

SQL Server's "Read Community" and "Reperationable Read" isolation levels all guarantee data consistency, but its key differences cannot be ignored.

Read Community isolation level

] Read Community ensures that the data read for transactions has been submitted before the transaction is visible. However, if the data is updated with the data and the same data is read again, the result is consistent.

Repertable Read isolated level

In contrast, the Repeatable Read provides higher -level consistency. It not only guarantees that the data reads has been submitted, but also ensures that the data remains unchanged during the entire transaction. Even if other transactions are updated, the original reading results remain consistent.

Example

Considering the following scene, Table T contains a column C with a value of '1'.

begin transaction; Select * from t; WAITFOR Delay '00: 01: 00 '; Select * from t; Commit;

read committed:

The second select statement may return any updated or modified data because the data may change during the delay.

BEGIN TRANSACTION;
SELECT * FROM T;
WAITFOR DELAY '00:01:00';
SELECT * FROM T;
COMMIT;

• other isolation levels
In addition to the Read Community and Repertable Read, SQL Server also provides other isolation levels:
• Serializable:
to ensure completely isolation to prevent any concurrent modification, delete or insert operation.

Snapshot: is similar to Serializable, but use snapshots to provide consistency without blocking concurrent transactions.

Select the right isolation level

The choice of isolation level depends on the specific application needs. Although higher isolation levels ensure consistency, they may also affect scalability and performance. For applications that can be tolerated repeatedly, Read Community is a suitable isolation level. For applications that need to be consistent and cannot tolerate data changes during the execution of transactions, it is recommended to use Repeatable Read.