MySQL / Transactions

[bruceg]

I am building an application, which I think will require transaction support. The application is written in PHP, and will use MySQL for the db. I have a "MySQL-MAX" version of MySQL, with Innodb support.

Since the application will have multiple users, possibly editing the same row at the same time, I'm guessing I will need to make use of transactions, so the edits to the records are done properly.

This is the scenario which I would like to work properly: If user-A is in record_id 1, and user-B is in record_id 1, and both are changing fields, if user-A modifies first and saves, the information on user-B's screen is now the "old" version of the data, because user-A has just made changes. Now user-B saves their changes, which would "overwrite" the data user-A had changed.

How would I avoid this scenario, using transactions? Since HTTP is stateless, this could happen quite often.

TIA...

• Bruce

[Sxooter]

You've run into the bugaboo that web apps present of "stateless" behaviour.

Basically, you have to come up with a "check out/check in" system.

When someone brings up a record to edit, you need to set a check out flag or expiry time somewhere that your application knows means that someone is editing the file so no one else can.

Then, you need to cron up something to check for checkout timeouts that will get rid of the check out, and the code that would check it back in has to know that if the time to check in has expired that it can't write to the database.

You likely don't actually need a transactional database for most of that.

[bruceg]

Actually I was thinking of that before seeing if transactions would help me with this issue. Looks like I'm going to have to build my "lock" table after all :-)

• Bruce