MySQL - Transactions

Author: tom000
Date: 2007-03-06 17:32:03

Transaction is some kind of operations which we execute on data. We can add data, modify, delete when transaction is go on, but all changes will be apply when transaction will be successful finished.

Samples of some transactions

Transaction 1:

START TRANSACTION;
INSERT INTO trans (re,txt) VALUES (0,'rek 1');
INSERT INTO trans (re,txt) VALUES (0,'rek 2');

Preview state of table in case of successful finish of transaction:

SELECT * FROM trans;
+----+----+-------+
| id | re | txt   |
+----+----+-------+
|  1 |  0 | rek 1 |
|  2 |  0 | rek 2 |
+----+----+-------+

COMMIT;

Here is end of transaction – added records now are in database.

Transaction 2:

START TRANSACTION;
INSERT INTO trans (re,txt) VALUES (5,'trans 2');

Preview of transaction state:

SELECT * FROM trans;
+----+----+---------+
| id | re | txt     |
+----+----+---------+
|  1 |  0 | rek 1   |
|  2 |  0 | rek 2   |
|  3 |  5 | trans 2 |
+----+----+---------+

ROLLBACK;

Here transaction was break. To show that the entry wasn't added to database we execute again command:

SELECT * FROM trans;
+----+----+-------+
| id | re | txt   |
+----+----+-------+
|  1 |  0 | rek 1 |
|  2 |  0 | rek 2 |
+----+----+-------+

Transaction 3:

START TRANSACTION;
UPDATE trans SET re=5 WHERE id=2;
SAVEPOINT krok1;
INSERT INTO trans (re,txt) VALUES (5,'trans 3');

Check state of transaction:

SELECT * FROM trans;
+----+----+---------+
| id | re | txt     |
+----+----+---------+
|  1 |  0 | rek 1   |
|  2 |  5 | rek 2   |
|  4 |  5 | trans 3 |
+----+----+---------+

ROLLBACK TO krok1;
COMMIT;

Transaction was finish successful, but before finish of it we go back to earlier saved point transaction state. In result of this command add new record was called off:

SELECT * FROM trans;
+----+----+-------+
| id | re | txt   |
+----+----+-------+
|  1 |  0 | rek 1 |
|  2 |  5 | rek 2 |
+----+----+-------+

« 1 [2] »

Login :
Website :
Comment :

Image text :

Samples of some transactions

Comments

Add comment