1

In MariaDB the default isolation level is repeatable read. I understand this means when I open a transaction I will not see any concurrent writes to the DB.

I also have a situation where I need to ensure concurrent changes are not made to rows. For this, I can acquire an exclusive lock. My question is really about how the two work in conjunction.

If I acquire the lock inside my update transaction it means it is possible I open the transaction and am blocked because another process has the exclusive lock, when I obtain the lock changes may have been made to the row which I would not see but might need to be aware of.

I am hesitant to change the isolation level as this is a specific scenario.

At the moment I have settled on acquiring the lock before I begin my update transaction but this makes releasing the lock more problematic as it would not be released once my transaction commits. Am I missing something obvious? So basically I open a transaction SELECT FOR UPDATE. Then open a second transaction to make the update.

Semi-related: Does the above mean that optimistic locking when your transaction level is repeatable read is kind of a no-go? Optimistic locking involves checking a version/timestamp to ensure no other transaction has modified it since it was initially read. With repeatable reads, we wouldn't have visibility of other transactions until our transaction is complete which defeats the purpose. Or does it just mean I should be acquiring locks outside of the transactions?

1
  • Please edit the question to limit it to a specific problem with enough detail to identify an adequate answer.
    – Community Bot
    Commented Oct 2, 2023 at 23:03

2 Answers 2

1

So basically I open a transaction SELECT FOR UPDATE. Then open a second transaction to make the update.

Do you mean a second statement?

If you are really starting a second transaction, then the locks acquired by your SELECT FOR UPDATE are released. The START TRANSACTION statement causes an implicit commit, which releases any locks held by the prior transaction.

Typically you would use SELECT FOR UPDATE to acquire some locks when you are preparing to UPDATE in the same transaction.

It's not immediately obvious, but in InnoDB, locking statements (including SELECT FOR UPDATE or SELECT FOR SHARE) always lock the most recently committed version of a row, as if you were using the read-committed isolation level, even if a non-locking read can't view that version of that row. This is surprising to some people, but it helps because it avoids some phantom updates.

1
  • 1
    "It's not immediately obvious, but in InnoDB, locking statements (including SELECT FOR UPDATE or SELECT FOR SHARE) always lock the most recently committed version of a row, as if you were using the read-committed isolation level, even if a non-locking read can't view that version of that row." aha this is the info I was looking for :) makes sense. Because as i mentioned in my question if it did not how would optimistic locking with a transaction even work? when using repeatable read that is. thanks so much Commented Oct 5, 2023 at 15:32
1

When I open a transaction, I will not see any concurrent writes to the DB.

But your transaction may be blocked.

When there is a blockage, one of two things happens:

  • A "deadlock". And one of the two transactions is killed. (Usually the appropriate reaction is to rerun the killed transaction.)
  • A timeout. (See innodb_lock_wait_timeout.) In this case, the second transaction to be blocked will stall until the first releases some lock. You will be unaware of this, and all will be well (though delayed).
  • If the blockage lasts longer than that setting (default is a ridiculously long 50 seconds), it will be killed.

Be sure to use FOR UPDATE on any SELECTs that fetch data that may be subsequently changed in that transaction. This lets other threads know that you don't want the value changing unexpectedly.

Follow the above advice, and you may never need to change the isolation level.

"Optimistic locking" -- The code is optimized for COMMIT; ROLLBACK takes more work.

"Dirty reads" are faster than "repeatable reads", but you lose most of the benefits of the ACID of transactions.

"acquiring locks outside of the transaction" -- Not really possible. You should minimize the amount of work inside a transaction.

  • Never interact with the user inside a transaction; you have not control over how long their coffee break will be.
  • If something can easily be pulled of of a transaction, do so.

Regardless of how you phrase it, everything involves a transaction. autocommit=ON without BEGIN...COMMIT makes each statement a transaction unto itself.

I need to ensure concurrent changes are not made to rows. --

BEGIN;
SELECT ... FOR UPDATE;
...
UPDATE/INSERT/...
COMMIT;

The necessary locks will be taken and honored.

At the moment I have settled on acquiring the lock before I begin a transaction

  • That is essentially impossible in InnoDB.
  • Do not use the LOCK statement; it probably has virtually no place with InnoDB tables.

Note how I steered you away from dwelling on "locks" and "isolation levels".

Another way to look at it...

When you BEGIN a transaction (and assuming 'repeatable read') a giant camera takes a picture of all the data. Your transaction proceeds using the data in the picture. And even UPDATEing values in the picture. When you COMMIT, all changes that you made on the picture are instantly copied to disk for others to see.

What is missing in that simplistic view is what if some other process is trying to change the same values you are Updating. That's where "locks" are communicated between the processes. Then the detailed rules (shared-read, exclusive, etc) come into play. That leads to continuing, delaying, or deadlocking of one process to resolve the locks.

The "giant camera" is implemented by having a sequence number (a la timestamp) connected to every value in every column of every table.
When a BEGIN happens, the sequence number is grabbed; it is used to say "I can see only the latest values before that number." When a column is UPDATEd, there are [temporarily] two values, each with a different sequence number. After COMMIT, all the extra copies of column values are cleaned up [in the background]. (cf "history")

Back to "optimistic" -- Note how the new values are poised to be stored permanently, whereas ROLLBACK has a messier cleanup to do.

Back to "repeatable read" -- Note how the giant picture continues to show only one value; hence the same SELECT will get the same results, independent of what other processes are trying to change.

"Dirty read" does not care about repeatability; hence may avoid locking and run faster. "Serializable" does not care about transactional stuff, but runs slower by not being able to run multiple processes simultaneously.

I hope this simplistic look helps. It helps me wrap my head around the complexity of InnoDB. I also hope others will point out any major flaws in it.

4
  • thanks for taking the time to answer! I understand RE autocommit etc My specific question was a little nuanced I guess but hoping you might be able to shed the final piece of light If I am to begin a transaction and my isolation level is set to repeatable read then does it mean (assuming a concurrent transaction is writing to the row I want to select FOR UPDATE) it means i will not see the concurrent write? Or does my entire transaction block and it does not truly start so I would see this update? Commented Oct 3, 2023 at 15:30
  • I've read the last line where BEGIN; SELECT ... FOR UPDATE; ... UPDATE/INSERT/... COMMIT; This makes sense but my question is about how SELECT FOR UPDATE would work if another transaction had the lock. Once the other releases and it acquires will my transaction be able to see the changes made by the other? Commented Oct 3, 2023 at 15:34
  • 1
    @DanielBenzie - I added a lot fo my Answer.
    – Rick James
    Commented Oct 3, 2023 at 15:57
  • 1
    @DanielBenzie - No. I'm pretty sure that your FOR UPDATE will conflict with the other process enough to cause one or the other to either delay or deadlock. In any case, you cannot see the new value (based on the scenario as described). For example, dirty read is a different situation.
    – Rick James
    Commented Oct 3, 2023 at 16:01

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge you have read our privacy policy.

Not the answer you're looking for? Browse other questions tagged or ask your own question.