1

This is a follow-up question for Transaction isolation levels relation with locks on table

I wonder about the speed differences between the isolation levels. What speed benefits can you achieve switching between the levels? Can you demonstrate when each of these can lead to lock waiting comparing to the other?

I also wonder: Doing this update query:

UPDATE product SET sold = 1 WHERE id = 10 AND sold = 0;

If someone modifies the flag sold during execution of the query, would it have different consequences with these acquisition levels?

Bill Karwin
  • 538,548
  • 86
  • 673
  • 828
Vojtěch
  • 11,312
  • 31
  • 103
  • 173

1 Answers1

2

Gap locking is a common difference between READ COMMITTED and REPEATABLE READ. This can affect lock-wait times if you have concurrent queries in contention for the same gap locks.

In the UPDATE query you show, assuming id is the primary key, the query should use the primary key index to select the single row, therefore there will be no gap locks for that query.

Re your comment:

Locking has no impact on performance. Lock waits may delay the start of execution if the query has to wait to acquire locks it needs for the query, but once the execution starts, it will have the same performance in any transaction isolation level.

Also, if there aren't any concurrent queries that hold the needed locks, there will be no additional waiting for locks.

Bill Karwin
  • 538,548
  • 86
  • 673
  • 828
  • Can you please elaborate more? The first part of the question concerns performance. You answered the second – do you mean that the setting will have no impact on the query? – Vojtěch Mar 12 '18 at 05:12
  • Thanks for the update, what about the `READ UNCOMMITTED`? Will it have any impact on the mentioned query? – Vojtěch Mar 12 '18 at 09:24
  • None of the levels of transaction isolation have any impact on query execution time. Besides, you should not use READ UNCOMMITTED for any reason. It's a bad idea for a transaction to read another user's *unfinished* transaction. – Bill Karwin Mar 12 '18 at 14:45
  • @BillKarwin Thanks for the answer. So if I have two transactions, one doing a SELECT query on the this PRODUCT table but another one doing an UPDATE with something like SET sold = 2 WHERE sold = 1 (i.e. without any keys). Based on your answer, there would be no performance benefit if the SELECT query was run on READ_COMMITTED or REPEATABLE READ Isolation level, am I correct? Would a general query like SELECT * FROM PRODUCT requires any lock at all for different isolation level? Thanks – lawkai Mar 12 '20 at 01:17
  • 1
    No, a SELECT query doesn't require any row locks, unless you deliberately use the optional clauses for locking. – Bill Karwin Mar 12 '20 at 04:42