Transaction in mysql

Transaction

A transaction is a set of one or more SQL statements that perform a set of related actions.
In addition to ensuring the proper execution of a set of SQL statements, a transaction locks the tables
involved in the transaction so that other users cannot modify any rows that are involved in the transaction.

suppose you have an application that does not use transactions, and two users attempt to
purchase the same CD, but there is only one in stock. When the first user initiates the sale, an INSERT statement is issued against the Sales table to add the CD order to the table. The application then experiences a delay before trying to issue an UPDATE statement against the Inventory table to subtract the CD from the inventory. Between the time when the INSERT statement is executed and the UPDATE statement is waiting to be executed, a second user initiates a sale for the same CD. As a result, a second INSERT statement is issued against the Sales table for that CD, and a second UPDATE statement is issued against the Inventory table, subtracting that CD from the inventory before the first user completes the order process.

In order for an operation in a relational database to qualify as a transaction, it must pass what is referred to as the ACID test. ACID is an acronym for the four properties that describe a transaction—atomic,
consistent, isolated, and durable
MySQL supports transactions for only two table types: InnoDB and BDB.
InnoDB table is the only type that supports full foreign key functionality, it is clearly the best choice for the transactional-safe relational database.

MySQL includes a number of statements that should not be included in a transaction.

❑ ALTER TABLE: Modifies a table definition.
❑ CREATE INDEX: Creates an index on a table.
❑ DROP DATABASE: Removes a database from a MySQL server.
❑ DROP INDEX: Removes an index on a table.
❑ DROP TABLE: Removes a table from a database.
❑ LOCK TABLES: Prevents concurrent access to tables.
❑ RENAME TABLES: Renames a table.
❑ SET AUTOCOMMIT=1: Sets the autocommit mode to on.
❑ START TRANSACTION: Begins a transaction.
❑ TRUNCATE TABLE: Removes data from a table.
❑ UNLOCK TABLES: Unlocks locked tables.

Adding Savepoints to Your Transaction

Now that you know how to use the START TRANSACTION, COMMIT, and ROLLBACK statements to create a basic transaction, you’re ready to use the SAVEPOINT and ROLLBACK TO SAVEPOINT statements to isolate portions of your transaction. 

The SAVEPOINT statement allows you to define a savepoint in a transaction,
and the ROLLBACK TO SAVEPOINT statement allows you to roll back a transaction to a specified savepoint.

The SAVEPOINT statement and the ROLLBACK TO SAVEPOINT statement. The SAVEPOINT statement is added to the transaction after the first two INSERT statements. If the statements are executed successfully, the savepoint is defined.

If the statements fail, the database is rolled back to its original statement. Once the savepoint is defined, two more INSERT statements are executed. If the statements are executed successfully, the changes are committed to the database. If either of the INSERT statements fails, however, the database is rolled back to the savepoint, undoing the changes made by the second set of INSERT statements, but preserving the changes made by the first two INSERT statements. Any changes made before the savepoint are saved.

START TRANSACTION;
INSERT INTO Books VALUES (103, ‘Hell\’s Angels’, 1966);
INSERT INTO Books VALUES (104, ‘Black Elk Speaks’, 1932);
SAVEPOINT sp1;

In the following transaction, a savepoint is defined and a ROLLBACK TO SAVEPOINT statement
is used to roll back to the savepoint:


START TRANSACTION;
INSERT INTO Books VALUES (103, ‘Hell\’s Angels’, 1966);
INSERT INTO Books VALUES (104, ‘Black Elk Speaks’, 1932);

SAVEPOINT sp1;
INSERT INTO Books VALUES (105, ‘Noncomformity’, 1996);
INSERT INTO Books VALUES (106, ‘A Confederacy of Dunces’, 1980);

ROLLBACK TO SAVEPOINT sp1;
INSERT INTO Books VALUES (107, ‘Postcards’, 1992);
INSERT INTO Books VALUES (108, ‘The Shipping News’, 1993);
COMMIT;

As you can see, a savepoint named sp1 is defined after the first two INSERT statements. Then, after the second two INSERT statements, a ROLLBACK TO SAVEPOINT statement is used to roll back the transaction to savepoint sp1. Following the ROLLBACK TO SAVEPOINT statement, two more INSERT statements are included in the transaction, followed by a COMMIT statement. If you were now to use a SELECT statement to view the contents of the Books table.

As the result set shows, the rows with a BookID value of 103, 104, 107, and 108 have been added to the table, but no rows with a value of 105 or 106. The first two and last two INSERT statements were committed to the database, but not the middle two. The middle two were rolled back because the ROLLBACK TO SAVEPOINT statement was used to roll back the transaction to the savepoint. Any statements executed between the SAVEPOINT statement and the ROLLBACK TO SAVEPOINT statement were rolled back, and the rows were not added to the table.


Auto commit
SET AUTOCOMMIT=0;

All statements that follow must be explicitly committed to the database. If you fail to commit them before ending a session, the statements will be rolled back.

SET AUTOCOMMIT=1;

When you set the autocommit mode to on, all statements that precede the SET clause are committed to the database, as if you had executed a COMMIT statement, and individual statements that follow are each committed automatically. Once the autocommit mode is set to on, you must explicitly start your transactions if they include more than one statement that you want treated as a unit.

If the autocommit mode has been set to off in a session and you end that session, the autocommit mode is automatically set to on when you start a new session.

SELECT @@autocommit;
When you execute this statement, MySQL returns the current autocommit mode.

dirty read

One problem that can occur when multiple transactions try to access the same table at the same time or near to the same time is the dirty read. A dirty read can occur when one transaction modifies data in a table, a second transaction reads the table before those modifications are committed to the database, and then the first transaction rolls back the modification, returning the database to its original statement. The second transaction might then try to modify the table based on its initial read, which is no longer accurate.


CREATE TABLE t (i INT) ENGINE = InnoDB;
START TRANSACTION;
INSERT INTO t VALUES(1);
SAVEPOINT my_savepoint;
INSERT INTO t VALUES(2);
ROLLBACK TO SAVEPOINT my_savepoint;
INSERT INTO t VALUES(3);
COMMIT;
SELECT * FROM t;

After executing these statements, the first and third rows have been inserted, but the
second one has been canceled by the partial rollback to the my_savepoint savepoint.

Transactions and Locks

The downside of this locking strategy is that transaction B must wait for transaction
A to complete. The more programs you have waiting for locks to clear, the less
throughput your transactional system will be able to support.

MySQL/InnoDB minimizes the amount of lock contention by locking at the row
level only. In our example, updates to other rows in the ACCOUNT_BALANCE table are
able to proceed without restriction. Furthermore, with InnoDB, reads do not normally
cause locks to occur, and readers do not need to wait for locks to be released
before accessing data. Other transactional storage engines—and other RDBMS systems—
may behave differently.

You can, however, place locks on rows that have only been read by using the FOR
UPDATE or LOCK IN SHARE MODE clause in the SELECT statement,

Deadlocks

A deadlock occurs when two transactions are each waiting for the other to release a
lock—they each block each other, and neither can proceed.

Example 8-7. Stored procedure with deadlock-handling logic
1 CREATE PROCEDURE tfer_funds2
2 (from_account INT, to_account INT,
3 tfer_amount numeric(10,2), OUT out_status INT,
4 OUT out_message VARCHAR(30))
5 BEGIN

7 DECLARE deadlock INT DEFAULT 0;
8 DECLARE attempts INT DEFAULT 0;

10 tfer_loop:WHILE (attempts<3) DO
11 BEGIN
12 DECLARE deadlock_detected CONDITION FOR 1213;
13 DECLARE EXIT HANDLER FOR deadlock_detected

14 BEGIN
15 ROLLBACK;
16 SET deadlock=1;
17 END;
18 SET deadlock=0;

20 START TRANSACTION;
22 UPDATE account_balance
23 SET balance=balance-tfer_amount
24 WHERE account_id=from_account;
25
26 UPDATE account_balance
27 SET balance=balance+tfer_amount
28 WHERE account_id=to_account;

30 COMMIT;
32 END;

33 IF deadlock=0 THEN
34 LEAVE tfer_loop;
35 ELSE
36 SET attempts=attempts+1;
37 END IF;
38 END WHILE tfer_loop;

40 IF deadlock=1 THEN
41 SET out_status=-1;
42 SET out_message="Failed with deadlock for 3 attempts";

44 ELSE
45 SET out_status=0;
46 SET out_message=CONCAT("OK (",attempts," deadlocks)");
47 END IF;
49 END;

Example 8-8. Locking rows in order to avoid deadlock conditions

CREATE PROCEDURE tfer_funds3
(from_account INT, to_account INT,tfer_amount NUMERIC(10,2))
BEGIN
DECLARE local_account_id INT;
DECLARE lock_cursor CURSOR FOR
SELECT account_id
FROM account_balance
WHERE account_id IN (from_account,to_account)
ORDER BY account_id

FOR UPDATE;
START TRANSACTION;
OPEN lock_cursor;
FETCH lock_cursor INTO local_account_id;
UPDATE account_balance
SET balance=balance-tfer_amount
WHERE account_id=from_account;
UPDATE account_balance
SET balance=balance+tfer_amount
WHERE account_id=to_account;
CLOSE lock_cursor;
COMMIT;
END;