mysql row locking via php

Question

I am helping a friend with a web based form that is for their business. I am trying to get it ready to handle multiple users. I have set it up so that just before the record is displayed for editing I am locking the record with the following code.

$query = "START TRANSACTION;";
mysql_query($query);
$query = "SELECT field FROM table WHERE ID = \"$value\" FOR UPDATE;";
mysql_query($query);

(okay that is greatly simplified but that is the essence of the mysql)

It does not appear to be working. However, when I go directly to mysql from the command line, logging in with the same user and execute

START TRANSACTION;
SELECT field FROM table WHERE ID = "40" FOR UPDATE;

I can effectively block the web form from accessing record "40" and get the timeout warning.

I have tried using BEGIN instead of START TRANSACTION. I have tried doing SET AUTOCOMMIT=0 first and starting the transaction after locking but I cannot seem to lock the row from the PHP code. Since I can lock the row from the command line I do not think there is a problem with how the database is set up. I am really hoping that there is some simple something that I have missed in my reading.

FYI, I am developing on XAMPP version 1.7.3 which has Apache 2.2.14, MySQL 5.1.41 and PHP 5.3.1.

Thanks in advance. This is my first time posting but I have gleaned alot of knowledge from this site in the past.

Answer 1

This is an old discussion, but perhaps people are still following it. I use a method similar to JMack's but include the locking information in the table I want to row-lock. My new columns are LockTime and LockedBy. To attempt a lock, I do:

UPDATE table
SET LockTime='$now',LockedBy='$Userid'
WHERE Key='$value' AND (LockTime IS NULL OR LockTime<'$past' OR LockedBy='$Userid')

($past is 4 minutes ago)

If this fails, someone else has the lock. I can explicitly unlock as follows or let my lock expire:

UPDATE table
SET LockTime=NULL,LockedBy=''
WHERE Key='$value' AND LockedBy='$Userid'

A cron job could remove old locks, but it's not really necessary.

Answer 2

the problem are the mysql commands. You could use mysqli for this

http://nz.php.net/manual/en/class.mysqli.php

or PDO. Described here:

PHP + MySQL transactions examples

HTH

Answer 3

The problem is not the syntax of your code, but the way you are trying to use it.

just before the record is displayed for editing I am locking the record with the following code

From this I am assuming that you select and "lock" the row, then display that edit page to your user, then when they submit the changes it saves and "unlocks" the table. Here in lies the fundamental problem. When your page is done loading, the PHP exits and closes the MySQL connection. When this happens, all the locks are immediately released. This is why the console seems to behave differently than your PHP. The equivalent in the console would be you exiting the program.

You cannot lock the table rows for editing for an extended period. This is not their design. If you want to lock a record for editing, you need to track these locks in another table. Create a new table called "edit_locks", and store the record id being locked, the user id editing, and the time it was locked. When you want to open a record for editing, lock the entire edit_locks table, and query to see if the record is locked by someone else. If it is not, insert your lock record, if it is, then display a locked error. When the user saves or cancels, remove the lock record from edit_locks. If you want to make things easy, just lock this table any time your program wants to use it. This will help you to avoid a race condition.

There is one more scenario that can cause a problem. If the user opens a record for editing, then closes the browser without saving or canceling, the edit lock will just stay there forever. This is why I said store the time it was locked. The editor itself should make an AJAX call every 2 minutes or so to say "I still need the lock!". When the PHP program receives this "relock" request, it should search for the lock, then update the timestamp to the current. This way the timestamp on the lock is always up to date within 2 minutes. You also need to create another program to remove old stale locks. This should run in a cron job every few minutes. It should search for any locks with a timestamp older than 5 minutes or so, and remove. If the timestamp is older than that, then clearly the editor was close some how or the timestamp would be up to date within 2 minutes.

Like some of the others have mentioned, you should try to use mysqli. It stands for "MySQL Improved" and is the replacement for the old interface.

Answer 4

You should not use the mysql api because it's easy to make mistakes that enables sql-injections and such, as well as it lacks some functionality. I suspect that transaction is one of these because if i'm not wrong every query is sent "by itself" and not in a larger context.

The solution however is to use some other api, i prefer mysqli because its so similar to mysql and widely supported. You can easily rewrite your code to use mysqli instead as well.

For the transaction functionality set auto-commit to false and commit yourself when you want it to. This does the same as starting and stopping transactions.

For the reference look at:

http://www.php.net/manual/en/mysqli.autocommit.php

http://www.php.net/manual/en/mysqli.commit.php

Answer 5

Use PDO for this (and all database operations):

$value = 40;

try {
    $dbh = new PDO("mysql:host=localhost;dbname=dbname", 'username', 'password');
} catch (PDOException $e) {
    die('Could not connect to database.');
}

$dbh->beginTransaction();

try {
    $stm = $dbh->prepare("SELECT field FROM table WHERE ID = ? FOR UPDATE");
    $stm->execute(array($value));
    $dbh->commit();
} catch (PDOException $e) {
    $dbh->rollBack();
}

If you must use the antiquated mysql_* functions you can something like:

mysql_query('SET AUTOCOMMIT=0');
mysql_query('START TRANSACTION');
mysql_query($sql);
mysql_query('SET AUTOCOMMIT=1');