Home arrow MySQL arrow Page 6 - Using Transactions In MySQL (Part 2)

Nothing Like the Real Thing - MySQL

mysqlThis concluding segment looks at the MySQL transactional model in a multi-user scenario, illustrating some of the data corruption problems that are likely to arise and explaining how to control them using MySQL's various isolation levels. It also includes a sample Perl application demonstrating transaction usage at the application level, and shows you how to emulate transactions with non-transactional MyISAM tables.

  1. Using Transactions In MySQL (Part 2)
  2. Isolating Yourself
  3. The Three Rís
  4. Peeping Tom
  5. Locks and Keys
  6. Nothing Like the Real Thing
  7. Holding Pattern
  8. Timberrrrrrrrrr!
  9. Perl of Wisdom
  10. End Work
By: icarus, (c) Melonfire
Rating: starstarstarstarstar / 41
December 22, 2003

print this article


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.

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
escort Bursa Bursa escort Antalya eskort


- Oracle Unveils MySQL 5.6
- MySQL Vulnerabilities Threaten Databases
- MySQL Cloud Options Expand with Google Cloud...
- MySQL 5.6 Prepped to Handle Demanding Web Use
- ScaleBase Service Virtualizes MySQL Databases
- Oracle Unveils MySQL Conversion Tools
- Akiban Opens Database Software for MySQL Use...
- Oracle Fixes MySQL Bug
- MySQL Databases Vulnerable to Password Hack
- MySQL: Overview of the ALTER TABLE Statement
- MySQL: How to Use the GRANT Statement
- MySQL: Creating, Listing, and Removing Datab...
- MySQL: Create, Show, and Describe Database T...
- MySQL Data and Table Types
- McAfee Releases Audit Plugin for MySQL Users

Developer Shed Affiliates


Dev Shed Tutorial Topics: