Logo Questions Linux Laravel Mysql Ubuntu Git Menu
 

Lock mysql table with php

Tags:

php

mysql

locking

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?

like image 564
pain.reign Avatar asked Aug 29 '12 13:08

pain.reign


People also ask

How do I lock a table in MySQL?

To lock a table using the MySQL LOCK TABLES Statement you need have the TABLE LOCK and SELECT privileges. READ LOCK − If you apply this lock on a table the write operations on it are restricted. i.e., only the sessions that holds the lock can write into this table.

Does MySQL SELECT lock the table?

MySQL uses table locking (instead of row locking or column locking) on all table types, except InnoDB and BDB tables, to achieve a very high lock speed.

How can I tell if a MySQL table is locked?

In MySQL, locked tables are identified using the SHOW OPEN TABLES command. In its simplest form is displays all locked tables. All open tables in the table cache are listed, but the IN_USE column indicates of the table is locked. When the first lock is taken, the value increments to 1.

What is lock table?

A locked table remains locked until you either commit your transaction or roll it back, either entirely or to a savepoint before you locked the table. A lock never prevents other users from querying the table. A query never places a lock on a table.


2 Answers

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...

like image 55
jsist Avatar answered Oct 04 '22 07:10

jsist


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";
like image 21
Fluffeh Avatar answered Oct 04 '22 07:10

Fluffeh