CODEWITHSUNDEEP
sqltransactionsisolation-level
kdt
kdt

Reputation: 28489

SQL: at REPEATABLE READ isolation level, are UPDATE locks held to the end of the transaction?

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?

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?

Upvotes: 2

Views: 363

Answers (1)

Mitch Wheat
Mitch Wheat

Reputation: 300559

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.

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?

Yes.

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.

Upvotes: 4

Related Questions