How to test table locking?

[cancer10]

Hi

I have been reading this tutorial to learn how to read/write lock a table.

But I wish to know how do I test it?

I ran the command

LOCK TABLE users WRITE;

The above code should ideally lock the table for reading/writing to other connections. But when I open phpmyadmin in another browser, i can still read the data of that table.

Is there anyway to test if its working?

Database: MySQL 5.1.41-3
Table Type: MyISAM

Thanks in advance

[NogDog]

I believe a "WRITE" lock will only prevent the other connection from writing, not from reading?

Also, how are you doing the lock? If it's via a PHP script, for instance, the lock is probably released as soon as the script finishes. If so, you could try putting a sleep() after the lock so that it will hold it for at least that many seconds.

[eval(BadCode)]

I have not personally used transactions or locking, I have helped a friend use row level locking to prevent redundant update operations which are handled at the application layer.

InnoDB offers more support from what I have heard.
My understanding, for innoDB, is that you need to do this:

BEGIN TRANSACTION;  (begin may not be the correct word)
LOCK TABLE users WRITE;
UPDATE users set foo = 'bar';  //or whatever you're up to
COMMIT TRANSACTION;  (when you are finished, the locks are removed, you can remove them in the middle of a transaction also)

The problem with PHP MyAdmin is that the MySQL daemon probably assumes you have finished your operations because PMA will, and correctly so, close the connection.

NogDog has a good idea, just add something like sleep(). Then open up another browser window with PMA and try to test the lock while the first query is sleeping.

[cancer10]

Hi Thanks for your reply.

I believe a "WRITE" lock will only prevent the other connection from writing, not from reading?

According to Mysql manual:

If you specify WRITE, the current connection can read or write to the table, but no other connections can access the table until the lock has been removed.

Also, how are you doing the lock? If it's via a PHP script, for instance, the lock is probably released as soon as the script finishes. If so, you could try putting a sleep() after the lock so that it will hold it for at least that many seconds.

I am doing this via phpmyadmin. As per the mysql manual, the lock can be released by running the command:

UNLOCK TABLES.


@eval(BadCode): Thanks for your reply. But I am looking for table level locking and not row level locking. As I do not want the other user to access the table (read or write) during the lock.

[cancer10]

Update: I tried doing this by running a script and sleep() does no good.

What I actually want to see is when the script is run from the other connection, I should see a mysql warning or error stating something like "Table is locked by another user" or something like that.


Thanks

[cancer10]

Update 2:
Someone suggested me a solution and I thought I should share it here:

Mysql GET_LOCK() function can do this:

Here is the complete syntax:

SELECT GET_LOCK('lock1',0);
LOCK TABLE users WRITE;
SELECT * FROM users;
UNLOCK TABLES;
SELECT RELEASE_LOCK('lock1');

Now I can easily test if my table has been locked by running only the first 3 lines. This will lock the table users until the lock is released:

SELECT GET_LOCK('lock1',0);
LOCK TABLE users WRITE;
SELECT * FROM users;

Now, run the following command from another connection:

SELECT IS_FREE_LOCK('lock1');

Mysql will output 0, means the lock is NOT yet released:

Now, to release the lock, I run the following commands:

UNLOCK TABLES;
SELECT RELEASE_LOCK('lock1');

To test if the lock has been released, run the following command once again:

SELECT IS_FREE_LOCK('lock1');

Mysql will output 1, means the lock is now released.


Please let me know your comments on this.

Thanks