Locking & Unlocking Tables in MySql

Posted on 2007-10-08
Last Modified: 2008-01-09
I am using the following code:

 UPDATE matrix SET dollarsGained = dollarsGained - 10;

1) How doI check that the table is actually locked?
2) How do I just unlock the table 'matix' without unlocking all of them?
3) How do I check that the table is actually unlocked?

Server info:
MySQL 5.0.45-community-nt via TCP/IP
MySQL Client Version 5.1.11
MYISAM tables
Question by:jmokrauer
    LVL 48

    Expert Comment

    See there all your questions are answerd

    Author Comment

    I cannot seem to find the answers you refer to in that link.  Would you cut and paste the actual text you are referring to that answer each of the three questions?
    LVL 48

    Accepted Solution

    1 and 3)
    Instead of using row-level locks, you can employ application-level locks, such as those provided by GET_LOCK() and RELEASE_LOCK() in MySQL. These are advisory locks, so they work only in well-behaved applications. See Section 11.10.4, Miscellaneous Functions. -> next link

    mysql> SELECT GET_LOCK('lock1',10);
            -> 1
    mysql> SELECT IS_FREE_LOCK('lock2');
            -> 1
    mysql> SELECT GET_LOCK('lock2',10);
            -> 1
    mysql> SELECT RELEASE_LOCK('lock2');
            -> 1
    mysql> SELECT RELEASE_LOCK('lock1');
            -> NULL

    2) not possible to unlock only one table, as this would lead to fast deadlocks in the database. Locks can only be gotten additiv. Allowing to unlock single tables would, as already mentioned, lead to deadlocks very fast.

    Featured Post

    Looking for New Ways to Advertise?

    Engage with tech pros in our community with native advertising, as a Vendor Expert, and more.

    Join & Write a Comment

    Does the idea of dealing with bits scare or confuse you? Does it seem like a waste of time in an age where we all have terabytes of storage? If so, you're missing out on one of the core tools in every professional programmer's toolbox. Learn how to …
    Using SQL Scripts we can save all the SQL queries as files that we use very frequently on our database later point of time. This is one of the feature present under SQL Workshop in Oracle Application Express.
    Video by: Steve
    Using examples as well as descriptions, step through each of the common simple join types, explaining differences in syntax, differences in expected outputs and showing how the queries run along with the actual outputs based upon a simple set of dem…
    Polish reports in Access so they look terrific. Take yourself to another level. Equations, Back Color, Alternate Back Color. Write easy VBA Code. Tighten space to use less pages. Launch report from a menu, considering criteria only when it is filled…

    745 members asked questions and received personalized solutions in the past 7 days.

    Join the community of 500,000 technology professionals and ask your questions.

    Join & Ask a Question

    Need Help in Real-Time?

    Connect with top rated Experts

    17 Experts available now in Live!

    Get 1:1 Help Now