MySQL Stored Procedure Transaction Management and mysql Stored Procedure transaction

MySQL Stored Procedure Transaction Management and mysql Stored Procedure transaction

 

ACID: Atomic, Consistent, Isolated, Durable
A storage program provides an excellent mechanism to define, encapsulate, and manage transactions.

1. MySQL transaction support 
MySQL transaction support is not bound to the MySQL server itself, but related to the storage engine:

MyISAM: transactions are not supported. It is used by read-only programs to improve performance. InnoDB: supports ACID transactions, row-level locks, and concurrent Berkeley DB. transactions are supported.

 
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:

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 the read committed we want: in a transaction, only commit records are allowed to be visible. If the select statement in the session is still being queried, another session will insert a record at this time, the newly added data is invisible to the repeatable read: after a transaction starts, the changes made by other sessions to the database are invisible to the competent service until the transaction commit 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:

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.

Transaction Management statement:

Start transaction: START the TRANSACTION. autocommit is set to 0. If a TRANSACTION is already running, a hidden COMMIT is triggered: COMMIT the TRANSACTION, save the changes, and release the lock ROLLBACK: roll back all changes made TO the database by the transaction, and end the transaction. Release the SAVEPOINT savepoint_name lock: Create a savepoint identifier to rollback to savepoint savepoint_name: roll back to all changes made to the database starting from savepoint_name. In this way, part of the TRANSACTION can be rolled back to ensure that a subset of the changes is committed. set transaction: allow setting the isolation level of the transaction lock tables: allow explicit locking of one or more TABLES to implicitly close the currently opened transactions. We recommend that you explicitly commit or rollback the transactions 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 do the following two steps:

Set the autocommit attribute of MySQL to 0. The default value is 1. Use the start transaction statement to explicitly open a TRANSACTION.

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:

CREATE PROCEDURE tfer_funds      (from_account int, to_account int, tfer_amount numeric(10,2))  BEGIN      SET autocommit=0;        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;  END;  

Example of opening a transaction using start transaciton:

CREATE PROCEDURE tfer_funds      (from_account int, to_account int, tfer_amount numeric(10,2))  BEGIN      START TRANSACTION;        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;  END;  

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:

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:

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 'Transfer aborted';      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 'Transfer aborted';            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.

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)

mysql > CALL tfer_funds(1,2,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:

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;  

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:

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:

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

Keep transactions short as much as possible to avoid rollback in transactions as much as possible to avoid savepoint by default, depending on the pessimistic lock for throughput demanding transactions consider optimistic lock shows that the less rows declared to open the transaction lock, the better, the shorter the lock time, the better.