CODEWITHSUNDEEP
mysqllocking
morpheus
morpheus

Reputation: 20360

Why do we need to lock a mysql table during SELECT when beginning a transaction causes any pending transaction to be committed?

On this page it says:

Beginning a transaction causes any pending transaction to be committed.

So then in essence begin transaction acts like a lock. But then on this page it says:

If you query data and then insert or update related data within the same transaction, the regular SELECT statement does not give enough protection. Other transactions can update or delete the same rows you just queried.

These statements appear contradictory to me and how do I reconcile them? To take an example, suppose I am implementing a Like Button. There is a table with following columns (user, post, is_liked) and another table that stores (post, total_likes). So to process a like request I would like to:

  1. Read the value of is_liked which is a boolean given the user and post.
  2. Then toggle is_liked and update like count

All this needs to be done atomically. According to first reference, I don't need to lock while reading since beginning a transaction causes any pending transaction to be committed. And if some other transaction tries to update the rows concurrently, it will be blocked by my pending transaction. But the second reference says otherwise. So who is correct and why?

Question 2: If I do need to lock (which I suspect I do), do I use LOCK IN SHARE MODE or FOR UPDATE?

Upvotes: 0

Views: 1873

Answers (3)

morpheus
morpheus

Reputation: 20360

This statement in MySQL documentation:

enter image description here

is incorrect and cause of the confusion in the question. If Beginning a transaction causes any pending transaction to be committed then all transactions are effectively serialized. The correct statement is probably: Beginning a transaction causes any pending transaction to be committed within the same session (connection)

See https://forums.mysql.com/read.php?10,698642,698642#msg-698642

Upvotes: 1

Bill Karwin
Bill Karwin

Reputation: 562631

Starting a transaction is not like a lock. That's a misunderstanding.

InnoDB implements "optimistic locking" by default. No locks are acquired by starting a transaction. When you execute a locking SQL statement, then locks are acquired as needed.

In your case, you should choose SELECT ... FOR UPDATE because that's what you are preparing to do — update the record after reading it.

Re your comments:

If you were to use SELECT LOCK IN SHARE MODE, it could lead to deadlocks.

SESSION 1                       SESSION 2

SELECT ... LOCK IN SHARE MODE
ok
                                SELECT ... LOCK IN SHARE MODE
                                ok
UPDATE
waits
                                UPDATE
                                waits

In the sequence above, you end up with two sessions waiting on each other, which is a deadlock. Neither one will give up, so MySQL has to kill one or the other.

Upvotes: 1

nbk
nbk

Reputation: 49385

You would hide the increasing or decreasing in a trigger. so there is no need for a transaction

But you can lockl the table, also in a tranbsaction see.

If you query data and then insert or update related data within the same transaction, the regular SELECT statement does not give enough protection. Other transactions can update or delete the same rows you just queried. InnoDB supports two types of locking reads that offer extra safety:

https://dev.mysql.com/doc/refman/8.0/en/innodb-locking-reads.html

Upvotes: 0

Related Questions