SELECT FOR UPDATE for locked queries
I am using MySql 5.x and in my environment I have a table named CALLS.
The CALLS table has a column status that accepts the enumeration {inprogress, completed}.
I want table reads / updates to be row-locked, so:
SET TRANSACTION ISOLATION LEVEL SERIALIZABLE;
SET AUTOCOMMIT = 0;
SELECT amount from CALLS where callId=1213 FOR UPDATE;
COMMIT
Mostly I do UPDATE even in situations where I only need to read the amount and return. I believe this allows me to ensure that the read / update does not interfere with each other. However, I was told that this would reduce the concurrency of the application.
Is there anyway, to achieve the same sequence of transactions without incurring lock overhead? Thank.
source to share
Disclaimer: MySQL is usually full of surprises, so the following might not be true.
What you are doing doesn't make any sense to me: you commit after a SELECT which should break the lock. Therefore, in my opinion, your code should not impose any significant overhead; but it also doesn't give you any consistency improvements.
Overall, SELECT FOR UPDATE can be a very reliable and sane way to maintain consistency without requiring more locks than is actually necessary. But of course, it should only be used when needed. Perhaps you should have different code codes: one (using FOR UPDATE) used when the retrieved value is used in a subsequent update operation. And one more (not using FOR UPDATE), when the value does not need to be protected from changes.
source to share
What you have implemented there - in case you are not familiar with it, is called pessimistic locking . You sacrifice academic performance for consistency, which is sometimes the right choice. In my professional experience, I have found that pessimistic blocking is much more difficult than helping.
First, it can lead to a deadlock .
An alternative (better imho) is optimistic blocking , where you accept the assumption that collisions happen infrequently and you just deal with them when they do. You are executing your command in a transaction, so the collision should not leave your data in an inconsistent state.
There is more information on optimistic locking in the Java sense here, but the ideas apply to everything.
source to share