锁表sql + php PDO

I want to lock a table before doing the action on the table in PDO, so two users will not be able to do the same action in the same time.

I tried as follow:

$this -> link ->beginTransaction();
$this -> link ->exec('LOCK TABLES `MYTABLE` WRITE;');
//do something...
$this -> link ->exec('UNLOCK TABLES');
$this -> link ->commit();

but if two users try this query, they both stack. what I do wrong?

thanks!!

<?php
$this->link->beginTransaction();
$this->link->query('select yourcolumn from yourtable where yourcondition for update');
sleep(20);
$this->link->commit();

Then open mysql workbench and try run the following

begin;
select count(yourcolumn) from yourtable where yourcondition for update;
// will lock here

Just remember ALWAYS open transaction AND use select for update when selecting the column data

I have tested the following, which is also protected by locking, when you run the following in workbench (while the above php script is running)

update yourtable set yourcolumn=yourvalue where yourcondition;
// will lock here because php has obtained lock, no need to use transaction here

However, the following is NOT protected by locking, when you run the following in workbench (while the above php scriptis running)

select yourcolumn from yourtable where yourcondition;
// will immediately return resulset because you are not using transaction AND "for update"

In addition, you may have a look at all your codes where beginTransaction is used, because long running transaction will cuase long table locking. An example is you only a transaction in an import file operation. Table will be locked after you insert the first record into table.

begin;
insert into yourtable values yourvalues;
// will acquire lock here, other mysql workbench will be deadlock
// until your whole import operation is done and rollback/commit your upload