Transaction Management

Part:   1  2   3  4 

(Continued from previous part...)

Here is a simple test code on the default transaction:

-- Rollback.sql
-- Copyright (c) 2004 by Dr. Herong Yang
--
SET AUTOCOMMIT = 0;
USE test;
DROP TABLE IF EXISTS User;
CREATE TABLE User (Login VARCHAR(8), Password CHAR(8)) ENGINE=InnoDB;
INSERT INTO User VALUES ('bill','83BF9598');
COMMIT;
INSERT INTO User VALUES ('jack','8IS3KOXW');
ROLLBACK;
INSERT INTO User VALUES ('john','FC93846F');
COMMIT;
INSERT INTO User VALUES ('mike','86D47E70');

Output:

\mysql\bin\mysql test
mysql> SELECT * FROM user;
+-------+----------+
| Login | Password |
+-------+----------+
| bill  | 83BF9598 |
| john  | FC93846F |
+-------+----------+

Observe that:

• How InnoDB is specified as the storage engine for the table.
• How to turn off the auto-commit flag.
• How uncommitted changes can be rolled back.
• Uncommitted changes are rolled back at the end of the session.

Here is another simple test code on non-default transactions:

-- Transaction.sql
-- Copyright (c) 2004 by Dr. Herong Yang
--
SET AUTOCOMMIT = 1;
DROP TABLE IF EXISTS User;
CREATE TABLE User (Login VARCHAR(8), Password CHAR(8)) ENGINE=InnoDB;
START TRANSACTION;
INSERT INTO User VALUES ('bill','83BF9598');
COMMIT;
INSERT INTO User VALUES ('jack','8IS3KOXW');
ROLLBACK;
INSERT INTO User VALUES ('john','FC93846F');
COMMIT;
INSERT INTO User VALUES ('mike','86D47E70');
START TRANSACTION;
INSERT INTO User VALUES ('user1','pass1');
START TRANSACTION;
INSERT INTO User VALUES ('user2','pass2');
START TRANSACTION;
INSERT INTO User VALUES ('user3','pass3');
ROLLBACK;
INSERT INTO User VALUES ('user4','pass4');
ROLLBACK;
INSERT INTO User VALUES ('user5','pass5');
COMMIT;
SELECT 'User table:' AS '---';
SELECT * FROM User;

Output:

---
User table:
Login   Password
bill    83BF9598
jack    8IS3KOXW
john    FC93846F
mike    86D47E70
user1   pass1
user2   pass2
user4   pass4
user5   pass5

Observe that:

• "jack" is in the table because the previous commit statement ends the non-default transaction. The insert statement on "jack" is automatically committed.
• "user1" and "user2" are in the table because transactions can not be nested. A "start transaction" statement commits the changes and ends the current transaction.
• "user4" is in the table because the previous rollback statement ends the non-default transaction.

(Continued on next part...)

Part:   1  2   3  4