patternsqlMinor
Is there any risk in SELECT ... FOR UPDATE outside transaction scope in MySQL?
Viewed 0 times
updateriskanyoutsidescopemysqlfortransactionselectthere
Problem
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
Use case 2 - Transaction, Select the book, check that is valid and if so update
In both of the above examples, the function getBook inside the repository does select 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.
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.
Solution
A query is always in a transaction even if you don't start it explicitly.
So, nothing bad will happen if you run
Deadlocks are possible, too. If another transaction holds a shared lock and then wants to update the records while
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.Context
StackExchange Database Administrators Q#211456, answer score: 8
Revisions (0)
No revisions yet.