Re: Table Row Locking
Posted by:
Rick James
Date: January 13, 2012 09:53AM
Let me focus on this statement:
> So, to prevent data corruption, I'd need to lock that row.
First, how long will it need to be locked?
* Long enough to change it? MyISAM and InnoDB do that in the UPDATE statement.
* Long enough for a special-user to fetch the row, think about editing it, take a coffee break, and finally submit a change? That's a much different case.
But wait, what are you locking it against? Some other special-user changing it? (And, subsequently, one of the special-users having his change overwritten by the other?)
That kind of lock needs to be done by the application. It should include a timestamp, so that if (no--WHEN) a special-user forgets to submit his change, the system (your code) can automatically unlock it.
Perhaps a new table with (item_id, user_id, timestamp) would suffice. Insert a row when he "checks out" an item; delete the row when he submits his change or "cancels" his action. If the row is not there when he goes to "submit", then thrash him with a wet noodle for taking too long a coffee break. If, when checking, out, there is already a row in the table, then (depending on the timestamp) either tell him to wait for the other user to finish, or grab it.
The handling of this extra table, together with the original table, may (or may not) need locking (InnoDB's BEGIN...COMMIT, or MyISAM's LOCK...UNLOCK).