Locking Concepts

Locking is a mechanism that prevents problems from occuring with simultaneous data access by my multpile clients. Locks are managed by the server.

The effect of the locking mechanism is to serialize access to data so that when multiple clients want to perform conflicting operations, each must wait its turn.

Not all types of concurrent access produce conlicts :

If a client wants to read data, other clients that want to read the same data do not produce a conflict, and they all can read at the same time. A client that wants to write must wait until the read has finside.

If a client wants to write data, all other clients must wait until the write has finished, regardless of whether those clients want to read or write.

A reader must block writers, but not other readers\/ A writer must block both readers and writers.

Acquiring Locks

A lock can be acquired implicitly or explicitly.

For a client that does nothing special to acquire locks, the MySQL Server impicitly acquires locks as necessary to process the client's statmenets safely. For example, the server acquires a read lock when the client issues a SELECT statmenet.

Acquire Lock Explicitly

LOCK TABLES

mysql> lock tables person read;
Query OK, 0 rows affected (0.00 sec)

and check that the lock has been acquired, table shows its in use

mysql> show open tables where in_use = 1;
+----------+--------+--------+-------------+
| Database | Table | In_use | Name_locked |
+----------+--------+--------+-------------+
| test | person | 1 | 0 |
+----------+--------+--------+-------------+

Unlock the tables

There's no need to pass in the table names. All the locks are realesed for the current client.

mysql> unlock tables ;
Query OK, 0 rows affected (0.00 sec)

Explicity Lock Types

READ

Locks a table for reading. A READ lock locks a table for read quieries such as SELECT that retrieve data from a table. It does not allow write operations such as INSERT, DELETE, or UPDATE that modify the table, even by the client that holds the lock.

WRITE

Locks a table for wiriting. A WRITE lock is an exclsive lock. Once acquired, only the client holding the write lock can read from or write to the table. Other clients can neither read from nor write to it.

READ LOCAL

Locks a table for reading, but allows concurrent inserts. Only applies to MyISAM table. Insert will be appended at the end. Will not work if the table have holes,(deletes inside the table).

LOW_PRIORITY_WRITE

Locks a table for writing, but acquires the lock with a lower priority.That is, if the client must wait for the lock, other clients that request read locks during the wait are allowed to get their lock first.