Analysis of mysql Stored Procedure Transaction Management

ACID: Atomic, Consistent, Isolated, Durable
A storage program provides an excellent mechanism to define, encapsulate, and manage transactions.
1. MySQL transaction support
1) MySQL transaction support is not bound to the MySQL server itself, but related to the storage engine:
SQL code Copy codeThe Code is as follows: MyISAM: transactions are not supported and used by read-only programs to improve performance.
InnoDB: supports ACID transactions, row-level locks, and concurrency.
Berkeley DB: supports transactions.
MyISAM: transactions are not supported and used by read-only programs to improve performance.
InnoDB: supports ACID transactions, row-level locks, and concurrency.
Berkeley DB: supports transactions 2) isolation level:

The isolation level determines the impact of transactions in one session on another session, the operations of concurrent sessions on the database, and the consistency of data seen in one session.
The ANSI standard defines four isolation levels. MySQL InnoDB supports:
Java codeCopy codeThe Code is as follows: read uncommitted: the lowest level of isolation, usually called dirty read, which allows a transaction to READ data that has not been committed, which may improve performance, however, dirty read may not be what we want.
Read committed: only records with commit are allowed to be visible in a transaction. If the select statement in the session is still being queried and another session inserts a record at this time, the newly added data is invisible.
Repeatable read: after a transaction starts, modifications made by other sessions to the database are invisible in the event service until the transaction is committed or rollback. The same result is returned when the select statement is repeated in a transaction, unless the database is updated in the event service.
SERIALIZABLE: isolation at the highest level, which only allows serial execution of transactions. To this end, the database locks the records read by each row. Other sessions cannot modify the data until the previous transaction ends. The lock is released only when the transaction is committed or canceled.
Read uncommitted: the lowest level of isolation, usually known as dirty read, which allows a transaction to read data without commit, which may improve performance, but dirty READ may not be what we want
Read committed: only records with commit are allowed to be visible in a transaction. If the select statement in the session is still being queried and another session inserts a record at this time, the newly added data is invisible.
Repeatable read: after a transaction starts, modifications made by other sessions to the database are invisible in the event service until the transaction is committed or rollback. The same result is returned when the select statement is repeated in a transaction, unless the database is updated in the event service.
SERIALIZABLE: isolation at the highest level, which only allows serial execution of transactions. To this end, the database locks the records read by each row. Other sessions cannot modify the data until the previous transaction ends. The lock is released only when the transaction is committed or canceled. You can use the following statement to set the MySQL session isolation level:

SQL codeCopy codeThe Code is as follows: set transaction isolation level {read uncommitted | read committed | repeatable read | serializable}
Set transaction isolation level {read uncommitted | read committed | repeatable read | serializable}

The default isolation level of MySQL is repeatable read. Be careful when setting the isolation level to read uncommitted or SERIALIZABLE. read uncommitted will cause serious data integrity problems, SERIALIZABLE causes performance problems and increases the chance of deadlock.
3) Transaction Management statement:
SQL codeCopy codeThe Code is as follows: start transaction. autocommit is set to 0. If a TRANSACTION is already running, a hidden COMMIT is triggered.
COMMIT: COMMIT transactions, save changes, and release locks
ROLLBACK: rolls back all changes made to the database by the transaction, ends the transaction, and releases the lock.
SAVEPOINT savepoint_name: Create a savepoint identifier TO ROLLBACK TO SAVEPOINT
Rollback to savepoint savepoint_name: rolls back TO all changes made TO the database starting from savepoint_name, so that part of the transaction can be rolled back TO ensure that a subset of changes is committed.
Set transaction: allows you to SET the TRANSACTION isolation level.
Lock tables: allows you to explicitly LOCK one or more TABLES, and implicitly closes the currently opened transactions. We recommend that you explicitly commit or rollback before executing the lock tables statement. We generally do not use lock tables in transaction code.
Start transaction: START the TRANSACTION. autocommit is set to 0. If a TRANSACTION is already running, a hidden COMMIT is triggered.
COMMIT: COMMIT transactions, save changes, and release locks
ROLLBACK: rolls back all changes made to the database by the transaction, ends the transaction, and releases the lock.
SAVEPOINT savepoint_name: Create a savepoint identifier TO ROLLBACK TO SAVEPOINT
Rollback to savepoint savepoint_name: rolls back TO all changes made TO the database starting from savepoint_name, so that part of the transaction can be rolled back TO ensure that a subset of changes is committed.
Set transaction: allows you to SET the TRANSACTION isolation level.
Lock tables: allows you to explicitly LOCK one or more TABLES, and implicitly closes the currently opened transactions. We recommend that you explicitly commit or rollback before executing the lock tables statement. We generally do not use lock tables in transaction code.

2. Define transactions
By default, MySQL runs a COMMIT statement after each SQL statement is executed to effectively separate each statement into a transaction.
In complex application scenarios, this method cannot meet the requirements.
To open a transaction and allow execution of multiple statements before COMMIT and ROLLBACK, we need to perform the following two steps (or manually ):
1. Set the autocommit attribute of MySQL to 0. The default value is 1.
2. Use the start transaction statement to explicitly open a TRANSACTION (then the autocommit attribute is automatically set to 0)
If a TRANSACTION has been opened, SET autocommit = 0 does not work, because start transaction implicitly commits all the current changes in the session, ends the existing TRANSACTION, and opens a new TRANSACTION.
Example of a stored procedure using the set autocommit statement:
SQL codeCopy codeThe Code is as follows: delimiter $
Use test $
Create procedure t_insert_table ()
Begin
/** Mark whether an error occurs */
Declare t_error int default 0;
/** If an SQL exception occurs, set t_error to 1 and continue the subsequent operation */
Declare continue handler for sqlexception set t_error = 1; -- error handling
/** The displayed start transaction. After the transaction is started, the autocommit value is automatically set to 0 */
Start transaction;
Insert into t_bom_test (parent_id, child_id) values ('C', 'xxxx ');
Insert into t_trigger_test (name, age) values ('hangsan', 34 );
/** The flag is changed, indicating that the transaction should be rolled back */
If t_error = 1 then
Rollback; -- transaction rollback
Else
Commit; -- transaction commit
End if;
End $
Delimiter;
Delimiter $
Use test $
Create procedure t_insert_table ()
Begin
/** Mark whether an error occurs */
Declare t_error int default 0;
/** If an SQL exception occurs, set t_error to 1 and continue the subsequent operation */
Declare continue handler for sqlexception set t_error = 1; -- error handling
/** The displayed start transaction. After the transaction is started, the autocommit value is automatically set to 0 */
Start transaction;
Insert into t_bom_test (parent_id, child_id) values ('C', 'xxxx ');
Insert into t_trigger_test (name, age) values ('hangsan', 34 );
/** The flag is changed, indicating that the transaction should be rolled back */
If t_error = 1 then
Rollback; -- transaction rollback
Else
Commit; -- transaction commit
End if;
End $
Delimiter;

Generally, a transaction is completed only when the COMMIT or ROLLBACK statement is executed. However, some DDL statements may implicitly trigger the COMMIT. Therefore, you should use as few or pay attention to the following in the transaction:
SQL codeCopy codeThe Code is as follows: ALTER FUNCTION
ALTER PROCEDURE
ALTER TABLE
BEGIN
CREATE DATABASE
CREATE FUNCTION
CREATE INDEX
CREATE PROCEDURE
CREATE TABLE
DROP DATABASE
DROP FUNCTION
DROP INDEX
DROP PROCEDURE
DROP TABLE
UNLOCK TABLES
LOAD MASTER DATA
LOCK TABLES
RENAME TABLE
TRUNCATE TABLE
Set autocommit = 1
START TRANSACTION
ALTER FUNCTION
ALTER PROCEDURE
ALTER TABLE
BEGIN
CREATE DATABASE
CREATE FUNCTION
CREATE INDEX
CREATE PROCEDURE
CREATE TABLE
DROP DATABASE
DROP FUNCTION
DROP INDEX
DROP PROCEDURE
DROP TABLE
UNLOCK TABLES
LOAD MASTER DATA
LOCK TABLES
RENAME TABLE
TRUNCATE TABLE
Set autocommit = 1
START TRANSACTION

3. Use Savepoint
The use of savepoint rollback inevitably consumes some performance, and can be rewritten with IF
One of the good scenarios of savepoint is "nested transactions". You may want the program to execute a small transaction, but do not want to roll back a larger transaction:
SQL codeCopy codeThe Code is as follows: create procedure nested_tfer_funds
(In_from_acct INTEGER,
In_to_acct INTEGER,
In_tfer_amount DECIMAL (8, 2 ))
BEGIN
DECLARE txn_error integer default 0;
DECLARE CONTINUE HANDLER FOR SQLEXCEPTION BEGIN
SET txn_error = 1;
END
SAVEPINT savepint_tfer;
UPDATE account_balance
SET balance = balance-in_tfer_amount
WHERE account_id = in_from_acct;
IF txn_error THEN
Rollback to savepoint_tfer;
SELECT 'transferaborted ';
ELSE
UPDATE account_balance
SET balance = balance + in_tfer_amount
WHERE account_id = in_to_acct;
IF txn_error THEN
Rollback to savepoint_tfer;
SELECT 'transferaborted ';
End if:
End if;
END;
Create procedure nested_tfer_funds
(In_from_acct INTEGER,
In_to_acct INTEGER,
In_tfer_amount DECIMAL (8, 2 ))
BEGIN
DECLARE txn_error integer default 0;
DECLARE CONTINUE HANDLER FOR SQLEXCEPTION BEGIN
SET txn_error = 1;
END
SAVEPINT savepint_tfer;
UPDATE account_balance
SET balance = balance-in_tfer_amount
WHERE account_id = in_from_acct;
IF txn_error THEN
Rollback to savepoint_tfer;
SELECT 'transferaborted ';
ELSE
UPDATE account_balance
SET balance = balance + in_tfer_amount
WHERE account_id = in_to_acct;
IF txn_error THEN
Rollback to savepoint_tfer;
SELECT 'transferaborted ';
End if:
End if;
END;

4. Transactions and locks
The ACID attribute of a transaction can only be achieved by limiting the synchronous changes to the database, and thus by locking the modified data.
The lock is released only when the transaction triggers a COMMIT or ROLLBACK statement.
The disadvantage is that the subsequent transactions must wait until the previous transactions are completed before execution can begin, and the throughput decreases as the wait time for the lock to be released increases.
MySQL/InnoDB minimizes lock competition through row-level locks. In this way, there is no limit to modifying the data of other rows in the same table, and the read data can never be waited.
You can use the for update or lock in share mode statement IN the SELECT statement to add row-level locks.
SQL codeCopy codeThe Code is as follows: SELECT select_statement options [for update | lock in share mode]
SELECT select_statement options [for update | lock in share mode]

For update locks the rows returned by the SELECT statement. Other SELECT and DML statements must wait until the transaction of the SELECT statement is completed.
The lock in share mode is the same as for update, but the SELECT statement of other sessions can be executed and the share mode lock can be obtained.
Deadlock:
A deadlock occurs when two transactions wait for each other to release the lock.
When MySQL/InnoDB detects a deadlock, it forces a transaction rollback and triggers an error message.
For InnoDB, the selected rollback transaction is the transaction with the least work done (with the least modified rows)
Java codeCopy codeThe Code is as follows: mysql> CALL tfer_funds (300 );
ERROR 1213 (40001): Deadlock found when trying to get lock; try restarting transaction

Deadlock may occur in any database system, but it is less likely for Row-Level Lock databases such as MySQL and InnoDB.
You can use consistent sequence to lock the row or table and keep the transaction as short as possible to reduce the deadlock frequency.
If the deadlock is not easy to debug, you can add some logic to your program to handle the deadlock and retry the transaction, but it is difficult to maintain this part of the code after it is too much.
Therefore, the better way to avoid deadlocks is to add row-level locks in a certain order before making any modifications, so as to avoid deadlocks:
Java codeCopy codeThe Code is as follows: 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
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;

Set the deadlock ttl: innodb_lock_wait_timeout. The default value is 50 seconds.
If you use both InnoDB and non-InnoDB tables in a transaction, MySQL cannot detect a deadlock and throws the "lock wait timeuot" 1205 error.
Optimistic and pessimistic locking strategies:
Pessimistic lock: these rows are locked when data is read, and other updates to these rows can continue until the pessimistic lock ends.
Optimistic: No lock is applied when reading data. Check whether the data has been updated when updating. If yes, cancel the current update.
We usually choose an optimistic lock when the waiting time of the pessimistic lock is too long and it cannot be accepted.
Pessimistic lock example:
Java codeCopy codeThe Code is as follows: create procedure tfer_funds
(From_account INT, to_account INT, tfer_amount NUMERIC (10, 2 ),
OUT status INT, OUT message VARCHAR (30 ))
BEGIN
DECLARE from_account_balance NUMERIC (10, 2 );
Start transaction;
SELECT balance
INTO from_account_balance
FROM account_balance
WHERE account_id = from_account
For update;
IF from_account_balance> = tfer_amount THEN
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;
COMMIT;
SET status = 0;
SET message = 'OK ';
ELSE
ROLLBACK;
SET status =-1;
SET message = 'Insufficient funds ';
End if;
END;

Optimistic lock example:
Java codeCopy codeThe Code is as follows: create procedure tfer_funds
(From_account INT, to_account INT, tfer_amount NUMERIC (10, 2 ),
OUT status INT, OUT message VARCHAR (30 ))
BEGIN
DECLARE from_account_balance NUMERIC (8, 2 );
DECLARE from_account_balance2 NUMERIC (8, 2 );
DECLARE from_account_timestamp1 TIMESTAMP;
DECLARE from_account_timestamp2 TIMESTAMP;
SELECT account_timestamp, balance
INTO from_account_timestamp1, from_account_balance
FROM account_balance
WHERE account_id = from_account;
IF (from_account_balance> = tfer_amount) THEN
-- Here we perform some long running validation that
-- Might take a few minutes */
CALL long_running_validation (from_account );
Start transaction;
-- Make sure the account row has not been updated since
-- Our initial check
SELECT account_timestamp, balance
INTO from_account_timestamp2, from_account_balance2
FROM account_balance
WHERE account_id = from_account
For update;
IF (from_account_timestamp1 <> from_account_timestamp2 OR
From_account_balance <> from_account_balance2) THEN
ROLLBACK;
SET status =-1;
SET message = CONCAT ("Transaction canceled due to concurrent update ",
"Of account", from_account );
ELSE
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;
COMMIT;
SET status = 0;
SET message = "OK ";
End if;
ELSE
ROLLBACK;
SET status =-1;
SET message = "Insufficient funds ";
End if;
END $

5. Transaction Design Guide
1. Keep the transaction short
2. Avoid rollback in the transaction whenever possible
3. Try to avoid savepoint
4. By default, relying on the pessimistic lock
5. Optimistic Locking for throughput demanding transactions
6. display the statement to open the transaction
7. The fewer rows, the better. The shorter the lock time, the better.