Block records?

leatherback

Hey All,

This week KI was asked to build a web-based data management tool. All in all easy enough. Up unto the point where they asked me to put in an option for multi-user editing options. THey wat to have multiple users, at different locations, to work on the same database, through www interface.

The problem I see happening -unlikely as it may be- that one person opens one records, edits it, and resubmits it, while siumultaniously someone else does the same for that record, effectively removing the other edits. (Doesn this make any sense?)

Any way, I need some way of blocking a record. Is there a build-in function in MySQL / PhP 4 which I can use?

dopey

the Innodb table type in mysql has provision for this. see http://dev.mysql.com/doc/mysql/en/innodb-locking-reads.html but this only applies for updates occuring at the precise time another select is occuring and can only work in the same transaction, not across multiple db connections - as would happen with multiple page requests

The bigger problem is:
clientA opens page, editing record number 1, at time x
clientB opens page, editing record number 1, at time x+1
clientB saves record number 1 at time x+2
clientA saves record number 1 at time x+10, changes made by clientB are overwritten.

i.e. it doesn't necessariliy have to be at the exact same time - someone could leave the edit page open on their browser for some time before saving

the way i see it, there's 2 options:
put a 'lock' field in the record so if clientB trys to open, they cannot save any changes (enforced at the application level), only read. Client would have to keep refreshing until clientA releases record. Loadsa probs with this if clientA never releases lock
keep a lastupdated timestamp on the record. if this has changed between clientB reading the record and attampting to write the record, discard the changes. You could get more detailed by checking which fields have changed and only discard those changes, or at least warn user

gonna be interestin to see what u come up with :-)

leatherback

Hey Dopey,

Thanks for that reply,

I was thinking along similar lines. I was just hoping that some smart guy at MySQL or PhP had build the function form me.

The solution:

User logs in in an edit-mode
retrieves a record
Update timestamp of that record
Write last user to retrieve that record

Record is blocked untill
- The data is re-inserted
or
- the record-updatable time (15 minutes, same as session expiration time) expires.

After these 15 minutes, the user has to re-login. Then the database is checked for other users to have downloaded the record. The user can then update or not, depending on whether an other user retrieved the same record meanwhile.

When a user tries to retrieve a record, a match is made between the timestamp, and the lastuser colum: The last user of a record can ALWAYS retrieve that record.

Yours,

jdaggs

First, off the relevent link:

http://dev.mysql.com/doc/mysql/en/innodb-transactions-with-different-apis.html

Second to paraphrase above:

Basically, your grouping the serveral select and update statements into one logical transaction block with the 'BEGIN and "COMMIT" commands. If you 'select' and the underlying record changes before you get to the 'update' the update will return an error under certain transaction isolation levels. Check for that error to prompt users to attempt the whole transaction again. But, this time any commited data will be either viewable or still locked by other transactions resulting in the same error on the second attempt. But, It will insure the consistency that you are looking for

Third, Transaction isolation level see this:

http://dev.mysql.com/doc/mysql/search.php?q=transaction+isolation+level&from=%2Fdoc%2Fmysql%2Fen%2Finnodb-transactions-with-different-apis.html&lang=en

Transaction isolation levels specity what you can 'read' from the database pending any other outstanding transaction by other users.

Transaction isolation and transaction semantics can be confusing at first. But, mysql absolutely supports the 'correct' way to do what you are attempting.

Fourth, There is more.

Applilcation design will be greatly influenced by these new transaction semantics and so can performance. Most, if not all generic discussions concerning Transactions can be of further assistance. And don't forget to read about the ACID principles.

dopey

jdaggs, yes what you have suggested will serve to lock the records during a transaction and protect any other transactions from updating the same record. It's definately worth implementing.

Unfortunately, because of the stateless nature of http, it's not possible to maintain a sql transaction over multiple page views; most likely the 'read' and 'save' request come in on different webserver processes. This would mean different mysql connections and no way of maintaining the sql transaction.

Sxooter

What you need is "application" level locking. What you do is you create a table (or just add some fields to your present table) to track files as being "checked out" by individual users. Look at how some of the phpwiki packages do it if you need an example. What you do is something along the lines:

alter table content add checkout timestamp;

Note that in MySQL timestamp may or may not be some kind of autoupdating abomination, based on what version you're running and how you define it. In all other databases it's the same as MySQL's datetime type. Substitute that if you're on an old version of MySQL...

Anyway, after that, when someone "checks out" a file to edit, put a timestamp in that field of either the current time and have an offset of how long a user lock can last, or the time the user lock will expire.

Until the checkout times out, attempts to check out the file will fail, and afterwards, the new attempt passes, and the old edit session will not be allowed to be put in the database if someone else has checked out the file since the lock expired.