用php 实现 mysql 锁表,用PHP锁定MySQL表

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?

解决方案

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";