mysql row locking via php
- by deezee
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.
