Nothing Like the Real Thing - MySQL

MyISAM tables support two types of table locks: read locks and write locks. A read lock means that the table is available to all clients only for reads; a write lock means that the table is only available to the client creating the lock for reads and writes, while all other clients will be denied access.

Table locks are initiated through the use of the LOCK TABLES command, which may be followed by one or more table names and the type of lock needed. This is illustrated below:

mysql> LOCK TABLES users READ, groups WRITE;
Query OK, 0 rows affected (0.08 sec)

Tables are unlocked with the UNLOCK TABLES command.

mysql> UNLOCK TABLES;
Query OK, 0 rows affected (0.01 sec)

The UNLOCK TABLES command does not need to be given a list of tables to unlock, it automatically unlocks all tables locked with the previous LOCK TABLES command.

Let's look at a quick example of how you can use these two commands to simulate a transaction with MySQL:

mysql> LOCK TABLES users WRITE, groups WRITE, mailboxes WRITE;
Query OK, 0 rows affected (0.03 sec)mysql> INSERT INTO users (name, pass) VALUES ('paul', mysql> PASSWORD('hitme'));Query OK, 1 row affected (0.06 sec)mysql> INSERT INTO groups (uid, grp) VALUES (LAST_INSERT_ID(), mysql> 'operations');Query OK, 1 row affected (0.00 sec)mysql> INSERT INTO mailboxes (uid, host, mboxname, mboxpass) VALUES(LAST_INSERT_ID(), 'some.host.com', 'pop3', 'hitmeagain'); Query OK, 1 row affected (0.01 sec)mysql> UNLOCK TABLES;Query OK, 0 rows affected (0.00 sec)

In this example, the LOCK TABLES command is used to lock all the tables needed by the "transaction" to successfully complete. Once the tables have been locked, SQL can be used to make changes to the tables without any fear that other sessions will interfere with the transaction. On successful completion, the tables are unlocked and other sessions can then view the changed data.

You'll notice that this type of simulated transaction does not include any recovery mechanism in case things go wrong; there is no way, for example, to ROLLBACK the changes made, and the durability of the transaction is also not as high as with InnoDB/BDB tables.

>>> More MySQL Articles          >>> More By icarus, (c) Melonfire

blog comments powered by Disqus