Is there any risk in SELECT ... FOR UPDATE outside transaction scope in MySQL?

Question

In a project where I work the sql for selecting objects does always select for update, whether the context is a transaction or not.

My question is if it has any risk, or performance issue, or chance causing deadlock or something else naughty ?

I know that select for update can lock a row only inside a transaction and that is the common use case.

I provide below two concrete use cases, and after the select for update used regardless of inside of transaction or not.

Use case 1 - Just select a book and return it

book = booksRepository->getBook(7);
return book;

Use case 2 - Transaction, Select the book, check that is valid and if so update

bookRepository->startTransaction();
book = booksRepository->getBook(7);
if (//some condition with the book properties) {
   bookRepository->updateBookAuthor(7, 'bbb');
}
bookRepository->commitTransaction();

In both of the above examples, the function getBook inside the repository does select for update

SELECT * FROM BOOKS
WHERE id = :id
FOR UPDATE;

I asked the other devs and the answer to why they do this was "Because otherwise we would have to create two functions in the repository, one getBook and one getBookForUpdate, so we just select always for update"...

That answer does not provide any insight or explanation for where I can learn, so I am asking here.

Answer 1

A query is always in a transaction even if you don't start it explicitly. SELECT FOR UPDATE acquires an exclusive lock on rows it matches. There is a possibility that other transactions want to modify same records and will have to wait until the SELECT FOR UPDATE finishes and release the lock.

So, nothing bad will happen if you run SELECT FOR UPDATE, but overall performance may be lower due to possible lock waits.

Deadlocks are possible, too. If another transaction holds a shared lock and then wants to update the records while SELECT FOR UPDATE is waiting.

Answer 2

Every statement is in some transaction. There are 3 cases:

• autocommit=ON, no preceding BEGIN:

The statement is a transaction, effectively BEGIN; Sql; COMMIT;

• autocommit=OFF, no preceding BEGIN:

All statements are part of the same transaction until you issue COMMIT (or some DDL that implicitly commits). I dislike this case because it is too easy to forget to issue the COMMIT.

• BEGIN has occurred without a COMMIT yet:

All statements are part of the same transaction until you issue COMMIT (or some DDL that implicitly commits).

So...

If you write your code so that BEGIN...COMMIT (explicit or implicit) is as short a time as practical, then don't worry.

If you write long-running transactions, then this SELECT ... FOR UPDATE is just one of many cases where your code will stall unexpectedly.

That is, it is more important to watch the "big picture" than to focus on one detail.

Meanwhile, I believe (without proof) that your lone SELECT ... FOR UPDATE could stall if some other connection is about to UPDATE the record. But maybe that is a good thing? When it does finish, it will have the updated values.