hi everyone,

I want to solicit advice from you as to the best approach to use to prevent simultaneous access to a database record (row) by two or more users.

The scenario is this: the app is multi-user, and whenever one user would complete entering information for that record into the system, the system will give him/her a new record to work on. So if two or more users submit their respective data into the system roughly simultaneously, how do I prevent them from retrieving the same piece of data to work on?

I'm thinking of using MySQL LOCK TABLES <tablename> WRITE for this to enforce a strict lock on the table while a user session is looking for a candidate record to edit. Once the system has found a record (one that isn't taken yet), it will mark the record with a timestamp (and probably the name of the user) so as to declare that record "unavailable" to other users. Then the session will UNLOCK TABLES so as to enable the other user sessions to get their own records.

Is this approach good, or scalable? I'm kinda wary on how this approach would work given, say, 30 simultaneous users all competing for access to records they can edit. What other gotchas can occur in such a scenario, if ever? I'm thinking as well of a background cron-activated task that looks for records that have been "locked" for more than, say, 20 minutes so that it can "unlock" them and make them available to other users. I'm also interested to know how other database servers (e.g. Oracle or MS SQL Server) are best used to handle such a concern.

Thanks for any input you can give :)

...peep...

nai-intimidate yatang sumagot ang iba sa "the great" cruizer.

Before gumawa ako ng application using InfoPath front end with an Oracle DB.

No concurrent user is allowed to edit a specific record. What I did is to create a field that is flagged as "locked" and a field that holds the current user editing the record. On user update, record is unlocked.  Orphaned records with "locked" status is manually unlock by an administrator, but in your case a background task would be necessary to clean up orphaned records with locked status.

 

When issuing a MySQL LOCK TABLES <tablename> WRITE command, naka-queue ba ang ibang request, or completely inaccessible ang table? If its the later,  magkaka problema with other users trying to edit a different record.

the request just blocks (hangs) until the table is unlocked by whoever locked it.

napagana ko na yun isa pang approach by using MySQL InnoDB transactional tables. I begin a transaction then SELECT the record FOR UPDATE. This prevents other connections/clients from reading the same row. Then the record is updated with the field set to "locked" and the transaction committed. When other connections/clients do exactly the same thing, they won't get the same rows. Kaso di ko pa natetest yun performance...I'll do a simple threading test with .NET and see how fast N simultaneous requests go using this approach.

the threaded test/benchmark code is right here: http://devpinoy.org/files/folders/architechture/entry8003.aspx

as expected, the SELECT FOR UPDATE approach is faster.

I was hesitant to reply since this is a MySql question and I know only of Sql Server ;)

Anyways, we were previously using something of a locking table, which contain nothing but a dummy column(usually an int column) Whenever we update/read something from the entry table we first issue an exclusive lock to that locking table, then safely modify/read to the entry table.  If any subsequent request for that entry table should be reading first the locking table, if the table is locked it won't be able to modify/read the entry table. However we abandoned it for the following reasons:
1.) It isn't bulletproof, anybody can still request/modify the entry table by going directly to the entry table itself, thus defeating the purpose of that locking table in the first place.
2.)It requires n+1, db objects for every  n tables, assuming every table is transactional by nature.
3.)If for some reason, the locks are not released  the dreaded dead locks  occur.

Currently we use the following to minimize locking:
1.) Don't issue a Begin Transaction, before user prompting, if the user won't respond locks the entry tables. This is just to stupid in the first place.
2.) Don't forget to rollback transaction from errors, and again place the rollback before prompting the user.
3.) Timestamps inclusion/checking, before and after modifications.
4.) Triggers
4.) Constraints  <- this one is for data integrity but I think should be part of data modification strategy

Now the above is based on Sql Server environment but I think this applies to MySql too(although I don't know about Triggers for pre MySql 5 versions) ;)

based on my experiment, doing table locking costs about 3 to 4x the processing time compared to using only record/row-level locking.

Yup, that's why  ISOLATION LEVELS in Sql Server should be explicit..

if you really want to scale properly i suggest that you create a middleware which handles the queuing of records to modify. this way the user only gets the record from the the queue. pluses to this approach is its very simple to implement and frees your server from non-performant row locking mechanisms.

just make sure your dequeue operations are thread safe. otherwise youll hit the same problem except this time its on the middleware.

hth
jake