0

Let's say I have the following transactions T1 and T2.

--T1
UPDATE Employee SET Salary=Salary*10;
UPDATE Employee SET Bonus=Bonus+5;


--T2
SET TRANSACTION ISOLATION LEVEL REPEATABLE READ;
SELECT AVG(Salary) FROM Employee;
SELECT MAX(Bonus) FROM Employee;

From my understanding repeatable read in T2 will put a lock on Salary and only release it when the transaction completes. Does that mean that if T2 starts first T1 will just wait until T2 finishes? Because T1 won't be able to update Salary until T2 releases that lock (i.e when it finishes).

Paul
  • 776
  • 1
  • 5
  • 18
  • Does this answer your question? [Difference between “read commited” and “repeatable read”](https://stackoverflow.com/q/4034976/2029983) – Thom A Feb 02 '21 at 22:04
  • @Larnu Not really, I understand T2 won't read phantom data in my situation, but I'm wondering if T1 can execute and commit in between the 2 selects from T2. Since technically the Salary lock will only be released after T2 finishes. – Paul Feb 02 '21 at 22:09
  • 2
    So the statement, in the first answer *"under REPEATABLE READ the second SELECT is guaranteed to display at least the rows that were returned from the first SELECT unchanged. New rows may be added by a concurrent transaction in that one minute, **but the existing rows cannot be deleted nor changed.**"* (emphasis mine) doesn't answer the question? What about that statement doesn't? – Thom A Feb 02 '21 at 22:10
  • The difference would be that my second select doesn't access the same column, that's why I was curious if that bolded statement still stands. – Paul Feb 02 '21 at 22:14
  • The most granular lock is row level, if the row is locked because another transaction is updating it you can't read other fields just because they are not the ones being updated. – Tony Feb 02 '21 at 22:26
  • 2
    Also, @Paul , you could easily have tested this to find out; so when you tested what were your results? – Thom A Feb 02 '21 at 22:32

0 Answers0