On Thu, 21 Nov 2013 07:29:49 -0600 John McKown <[email protected]> wrote:
> To get more to your question, what I would do is have another, > boolean, column in my table. I would call it something like > "being_edited". When a user wants to edit a car, I would start a > transaction (BEGIN TRANSACTION). I would then SELECT all the columns > for the given car_id. My C (or whatever) code would check > "being_edited". If it is TRUE, then tell the user that the car cannot > be modified at this time and END TRANSACTION. Otherwise, UPDATE the > "being_edited" to TRUE and UPDATE the row. Then END TRANSACTION. That's pretty good, but I think I can do better. 0. Add a column "status", with a domain representing the entities that can update the database, perhaps process ids or user ids. Let 0 be "no one". For each update, 1. update T set status = $uid where status in (0, $uid) 2. check row count and notify user if busy, etc. 3. select ... from T where status = $uid 4. [user putzes with data] 5. update T set ... , status = 0 where status = $uid 6. check row count and notify user, etc. 7. provide for resetting status of abandoned edits. For each delete, 1. delete from T where status = 0 In this scenario, the first one to reach #1 wins, provided everyone plays by the same rules. The record is "locked" according to those rules, but not according to the DBMS. You begin with an UPDATE to acquire the "lock" atomically. If that fails, the user will have go the the next cubicle and bang on the door. Then select the row(s) at your leisure, because you've claimed them with your id. When you apply the new values with the second UPDATE, check the status again, just to make sure. Measure twice, cut once. The above describes pessimistic locking. Large systems, such as airline reservations, use optimistic locking. The row has a counter; every select captures it, and every update both checks and increments it. If two readers read the same row, the first update succeeds (because the counter still matches) and second one fails. Inconvenient for the user, but half the I/O, and greater concurrency. --jkl _______________________________________________ sqlite-users mailing list [email protected] http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
