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

Rules of the Game - MySQL

mysqlOne of the most-requested MySQL features - transactions - is finally available in MySQL 4.0. In this first segment of a two-part article, learn about the theory behind the transactional model, find out how it can make your SQL applications more robust, and find out how to implement a transactional environment with MySQL's InnoDB table handler.

TABLE OF CONTENTS:
  1. Using Transactions In MySQL (Part 1)
  2. Money, Money, Money...
  3. The Acid Test
  4. Turning the Tables
  5. A Question of Commitment
  6. Rules of the Game
  7. Artificial Intelligence
  8. Time Out
By: icarus, (c) Melonfire
Rating: starstarstarstarstar / 74
November 03, 2003

print this article
SEARCH DEV SHED

TOOLS YOU CAN USE

advertisement
It is important to note that MySQL does not permit nested transactions. As in the example below, if you start a new transaction without ending the previous one with a COMMIT or ROLLBACK, MySQL will automatically commit the previous transaction's data to disk before beginning a new transaction.

mysql> START TRANSACTION;
Query OK, 0 rows affected (0.03 sec)

mysql> INSERT INTO users (name, pass) VALUES ('john', PASSWORD('john'));
Query OK, 1 row affected (0.57 sec)

mysql> START TRANSACTION;
Query OK, 0 rows affected (0.00 sec)

mysql> SELECT * FROM users;
+----+------+------------------+
| id | name | pass |
+----+------+------------------+
| 3 | alan | 5af23f026beddb81 |
| 4 | john | 2ca0ede551581d29 |
+----+------+------------------+
2 rows in set (0.26 sec)


A number of other SQL commands also perform such an implicit COMMIT - dropping, creating and altering tables; dropping and creating indexes; and dropping and creating databases.

That said, it is interesting to note that if you begin a transaction, but exit the session before issuing a COMMIT or ROLLBACK, MySQL does *not* perform an automatic COMMIT; instead, it issues a ROLLBACK. The following example demonstrates:

[me@host]$ mysql
Welcome to the MySQL monitor. Commands end with ; or g.
Your MySQL connection id is 5 to server version: 4.0.12-max-debug

Type 'help;' or 'h' for help. Type 'c' to clear the buffer.

mysql> USE master;
Database changed

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

mysql> SELECT * FROM users;
+----+------+------------------+
| id | name | pass |
+----+------+------------------+
| 3 | alan | 5af23f026beddb81 |
| 4 | john | 2ca0ede551581d29 |
+----+------+------------------+
2 rows in set (0.00 sec)

mysql> INSERT INTO users (name, pass) VALUES ('tim', PASSWORD('hoo'));
Query OK, 1 row affected (0.02 sec)

mysql> exit
Bye


Now, when you open a new session, and check the "users" table, you'll see that there is no record for Tim, since MySQL issued a ROLLBACK when the previous session exited.

[me@host]$ mysql
Welcome to the MySQL monitor. Commands end with ; or g.
Your MySQL connection id is 6 to server version: 4.0.12-max-debug

Type 'help;' or 'h' for help. Type 'c' to clear the buffer.

mysql> USE master;
Database changed


mysql> SELECT * FROM users;
+----+------+------------------+
| id | name | pass |
+----+------+------------------+
| 3 | alan | 5af23f026beddb81 |
| 4 | john | 2ca0ede551581d29 |
+----+------+------------------+
2 rows in set (0.01 sec)


This is of particular relevance to the next example - so flip the page and let's see how.

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

blog comments powered by Disqus
escort Bursa Bursa escort Antalya eskort
   

MYSQL ARTICLES

- 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: