CODEWITHSUNDEEP
phpmysqllocking
pain.reign
pain.reign

Reputation: 371

Lock mysql table with php

Script 1.

$query_ = "lock tables test1 as test11 write";
mysql_query($query);
$query_ = "select * from test11";
sleep(20);
$query_ = "unlock tables";
mysql_query($query_);

Script 2.

$query_ = "select * from test1";
$result = mysql_query($query_);

The problem is that if i run second script while running first script. Table is not locked. And i can read any data from it.

I need it to be locked and return error.

How to make this work?

Upvotes: 7

Views: 30072

Answers (4)

still_dreaming_1
still_dreaming_1

Reputation: 9135

You have a misconception about what locks do. Locks to do not prevent other scripts from accessing data, instead locks affect the timing of when other scripts will access the data. When script 2 tries to access or modify data that it is not allowed to because of a lock, it will pause and wait for the lock to be released. After script 1 unlocks the table, script 2 will proceed and complete without any problems.

Upvotes: 1

Louis Tang
Louis Tang

Reputation: 21

You have to grant the rights of lock tables to the db user.

Upvotes: 2

Fluffeh
Fluffeh

Reputation: 33504

You are read locking the table with $query_ = "lock tables test1 as test11 read";- which means that other queries can still read it without any problems what-so-ever (Relevant link - scroll down to the section on lock types):

Info on the read lock type:

  • The session that holds the lock can read the table (but not write it).
  • Multiple sessions can acquire a READ lock for the table at the same time.
  • Other sessions can read the table without explicitly acquiring a READ lock.

If you want to stop anything else so much as reding the table, you need to use a write lock as follows:

$query_ = "lock tables test1 as test11 write";

Upvotes: 6

jsist
jsist

Reputation: 5253

If you do not want others to access that table then use 

LOCK TABLES test1 WRITE;

Other script will not return error but will wait until lock is released.

Hope it helps...

Upvotes: 9

Related Questions