• Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 3142
  • Last Modified:

Locking & Unlocking Tables in MySql

I am using the following code:

 LOCK TABLES matrix WRITE;
 UPDATE matrix SET dollarsGained = dollarsGained - 10;
 UNLOCK TABLES;

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
0
jmokrauer
Asked:
jmokrauer
  • 2
1 Solution
 
hernst42Commented:
See http://dev.mysql.com/doc/refman/5.0/en/internal-locking.html there all your questions are answerd
0
 
jmokrauerAuthor Commented:
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?
0
 
hernst42Commented:
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.
0

Featured Post

Restore individual SQL databases with ease

Veeam Explorer for Microsoft SQL Server delivers an easy-to-use, wizard-driven interface for restoring your databases from a backup. No expert SQL background required. Web interface provides a complete view of all available SQL databases to simplify the recovery of lost database

  • 2
Tackle projects and never again get stuck behind a technical roadblock.
Join Now