7

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?

pain.reign
  • 371
  • 2
  • 4
  • 17
  • It may not help answer your question, but you should stop using `mysql_*` functions. They're being deprecated. Instead use [PDO](http://php.net/manual/en/book.pdo.php) (supported as of PHP 5.1) or [mysqli](http://php.net/manual/en/book.mysqli.php) (supported as of PHP 4.1). If you're not sure which one to use, [read this article](http://www.deprecatedphp.com/mysql_/). – Matt Aug 29 '12 at 13:40
  • I believe the table will only be locked for that particular mysql connection instance. I think it works this way to prevent race conditions. – Travesty3 Aug 29 '12 at 13:40
  • what sense to lock it for particular mysql connection? Why would my script want to insert something when i don't insert anything? – pain.reign Aug 29 '12 at 13:42
  • Perhaps I'm wrong about that. Maybe I'm confused, and what I was thinking of was that it actually automatically locks the table during script execution to prevent race conditions...sorry, I'm not being much help here. – Travesty3 Aug 29 '12 at 13:45

4 Answers4

9

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

jsist
  • 5,223
  • 3
  • 28
  • 43
  • I tried it. Still doesn't lock anything. Second script can easily access data. – pain.reign Aug 29 '12 at 14:01
  • 2
    It seems that from your first script, first you are putting write lock on test1 then you are executing a query, then sleeping for 20 seconds, then unlocking the table. Untill you unlock, second script is waiting for table to unlock. As soon as table unlocks, query in second script gets access to the table and it returns data. Try increasing the sleep time to 100 or say 200 and then check the results. – jsist Aug 30 '12 at 06:11
6

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";
Fluffeh
  • 33,228
  • 16
  • 67
  • 80
  • 1
    I change modifier to "write" still second script returns all the records. – pain.reign Aug 29 '12 at 13:48
  • check my example i changed to write. still the same. reads everything fine. try it yourself, you will see it doesn't work. – pain.reign Aug 29 '12 at 13:52
  • @pain.reign Locks on tables are session specific, is this an arbitrary question (which is cool) or are you trying to emulate a transaction on the tables that you are locking? – Fluffeh Aug 29 '12 at 13:53
  • I don't understand you question. But i tried making all queries in one transaction for one script and without transaction. Still doesn't work. $query_ = "start transaction"; mysql_query($query_); $query_ = "lock tables test1 as test11 write"; mysql_query($query); $query_ = "select * from test11"; sleep(20); $query_ = "unlock tables"; mysql_query($query_); mysql_query("commit"); – pain.reign Aug 29 '12 at 13:58
  • This is example question. I try to see that tables are locked. And other scripts cannot access data. But I cannot get it work. – pain.reign Aug 29 '12 at 14:10
  • @pain.reign If you want to use transactions with `mysql_*` functions, I believe the syntax is `mysql_query("BEGIN");` (You may also need an `autocommit=0`) and ends with `mysql_query("commit");` - having said that, if you move to [PDO](http://au.php.net/manual/en/book.pdo.php) you can easily do transactions using [beginTransaction](http://php.net/manual/en/pdo.begintransaction.php) – Fluffeh Aug 29 '12 at 14:19
  • tried mysql_query("BEGIN") and set autocommit = 0 and withought. Still doesn't lock. – pain.reign Aug 29 '12 at 14:55
  • NOthing helps. PDO with transaction and without transaction doesn't lock too. – pain.reign Aug 29 '12 at 15:17
  • The way this answer is worded is confusing. Are you saying the only thing a read lock does is prevent the current session from writing to that table? So other sessions can still read from or write to that table? Also, in this context, what exactly is a "session"? – still_dreaming_1 Nov 09 '15 at 16:41
  • 2
    @still_dreaming_1 A Read Lock will prevent any session from writing to the table while the lock is in place - though other sessions may also pick up a read lock at the same time. A session is kept in place from the time the executing application makes a connection to the time that connection is terminated (either in the code or when the code finishes executing). – Fluffeh Nov 10 '15 at 07:10
2

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

Louis Tang
  • 21
  • 1
1

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.

still_dreaming_1
  • 8,661
  • 6
  • 39
  • 56