phpmysqllockinginnodb

PDO Lock Table Row INNODB

I have a table where sensitive data is stored and need to take care, that only one session is able to read/write on a specific row.

My table has 2 columns

id (int) primary
amount (int) index

I want to lock the table but only one row.

Something like

LOCK TABLEROWS `mytable` WRITE WHERE `id` = 1

I'm using pdo and startTransaction won't prevent other sessions to read/write due that time.

I read the InnoDB documentation but didn't get it to run.

EDIT:

$_PDO->exec('START TRANSACTION');
$_PDO->query('SELECT * FROM `currency` WHERE `id` = '.$userid.' FOR UPDATE');  
//maybe do update or not check if
$_PDO->exec('COMMIT');

So that's all I need to do?

Solution

  • The example you show will cause other sessions doing SELECT...FOR UPDATE to wait for your COMMIT. The locks requested by SELECT...FOR UPDATE are exclusive locks, so only one session at a time can acquire the lock. Therefore if your session holds the lock, other sessions will wait.

    You cannot block non-locking reads. Another session can run SELECT with no locking clause, and still read the data. But they can't update the data, nor can they request a locking read.

    You could alternatively make each session request a lock on the table with LOCK TABLES, but you said you want locks on a row scale.

    You can create your own custom locks with the GET_LOCK() function. This allows you to make a distinct lock for each user id. If you do this for all code that accesses the table, you don't need to use FOR UPDATE.

    $lockName = 'currency' . (int) $userid;

$_PDO->beginTransaction();

$stmt = $_PDO->prepare("SELECT GET_LOCK(?, -1)");
$stmt->execute([$lockName]);

$stmt = $_PDO->prepare('SELECT * FROM `currency` WHERE `id` = ?');  
$stmt->execute([$userid]);

//maybe do update or not check if

$_PDO->commit();

$stmt = $_PDO->prepare("SELECT RELEASE_LOCK(?)");
$stmt->execute([$lockName]);

    This depends on all client code cooperating. They all need to acquire the lock before they work on a given row. You can either use SELECT...FOR UPDATE or else you can use GET_LOCK().

    But you can't block clients that want to do non-locking reads with SELECT.