www.webdeveloper.com
Results 1 to 6 of 6

Thread: How to test table locking?

  1. #1
    Join Date
    Sep 2006
    Posts
    642

    Question How to test table locking?

    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

    Code:
    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

  2. #2
    Join Date
    Aug 2004
    Location
    Ankh-Morpork
    Posts
    19,154
    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.
    "Please give us a simple answer, so that we don't have to think, because if we think, we might find answers that don't fit the way we want the world to be."
    ~ Terry Pratchett in Nation

    eBookworm.us

  3. #3
    Join Date
    Jul 2010
    Location
    /ramdisk/
    Posts
    865
    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:

    Code:
    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.
    I use (, ; : -) as I please- instead of learning the English language specification: I decided to learn Scheme and Java;

  4. #4
    Join Date
    Sep 2006
    Posts
    642
    Hi Thanks for your reply.

    Quote Originally Posted by NogDog View Post
    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.

    Quote Originally Posted by NogDog View Post
    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.

  5. #5
    Join Date
    Sep 2006
    Posts
    642
    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

  6. #6
    Join Date
    Sep 2006
    Posts
    642
    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:

    Code:
    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:

    Code:
    SELECT GET_LOCK('lock1',0);
    LOCK TABLE users WRITE;
    SELECT * FROM users;
    Now, run the following command from another connection:

    Code:
    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:

    Code:
    UNLOCK TABLES;
    SELECT RELEASE_LOCK('lock1');

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

    Code:
    SELECT IS_FREE_LOCK('lock1');
    Mysql will output 1, means the lock is now released.


    Please let me know your comments on this.

    Thanks

Thread Information

Users Browsing this Thread

There are currently 1 users browsing this thread. (0 members and 1 guests)

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •  
HTML5 Development Center



Recent Articles