Databases Reference
In-Depth Information
thread has the table locked. Thus, if you see this, you know the thread is waiting for a
lock in the MySQL server, not in the storage engine.
Explicit locks, however, are not the only type of lock that might block such an opera-
tion. As we mentioned earlier, the server implicitly locks tables during queries. An easy
way to show this is with a long-running query, which you can create easily with the
SLEEP()
function:
mysql>
SELECT SLEEP(30) FROM sakila.film LIMIT 1;
If you try again to lock
sakila.film
while that query is running, the operation will hang
because of the implicit lock, just as it did when you had the explicit lock. You'll be able
to see the effects in the process list, as before:
mysql>
SHOW PROCESSLIST\G
*************************** 1. row ***************************
Id: 7
User: baron
Host: localhost
db: NULL
Command: Query
Time: 12
State: Sending data
Info: SELECT SLEEP(30) FROM sakila.film LIMIT 1
*************************** 2. row ***************************
Id: 11
User: baron
Host: localhost
db: NULL
Command: Query
Time: 9
State: Locked
Info: LOCK TABLES sakila.film WRITE
In this example, the implicit read lock for the
SELECT
query blocks the explicit write
lock requested by
LOCK TABLES
. Implicit locks can block each other, too.
You might be wondering about the difference between implicit and explicit locks. In-
ternally, they are the same type of structure, and the same MySQL server code controls
them. Externally, you can control explicit locks yourself with
LOCK TABLES
and
UNLOCK
TABLES
.
When it comes to storage engines other than MyISAM, however, there's one very im-
portant difference between them. When you create a lock explicitly, it does what you
tell it to, but implicit locks are hidden and “magical.” The server creates and releases
implicit locks automatically as needed, and it tells the storage engine about them. Stor-
age engines “convert” these locks as they see fit. For example, InnoDB has rules about
what type of InnoDB table lock it should create for a given server-level table lock. This
can make it hard to understand what locks InnoDB is really creating behind the scenes.
