A brief analysis of mysql stored procedure transaction management
- 2020-05-14 05:06:07
- OfStack
ACID:Atomic, Consistent, Isolated, Durable
The stored program provides an excellent mechanism for defining, encapsulating, and managing transactions.
1, transaction support for MySQL
1) MySQL's transaction support is not tied to the MySQL server itself, but to the storage engine:
Sql code
The isolation level determines the likely impact of a transaction in one session on another session, the operation of concurrent session on the database, and the 1-uniqueness of the data seen in one session
The ANSI standard defines four isolation levels, and InnoDB of MySQL supports:
Java code
Sql code
The default isolation level for MySQL is REPEATABLE READ. Be careful when setting the isolation level to READ UNCOMMITTED or SERIALIZABLE. READ UNCOMMITTED can cause serious data integrity issues, while SERIALIZABLE can cause performance issues and increase the chance of deadlocks
3) transaction management statement:
Sql code
2. Define transactions
The default behavior of MySQL is to execute one COMMIT statement after each SQL statement, effectively separating each statement into one transaction.
In complex application scenarios this approach will not satisfy the requirements.
In order to open the transaction and allow multiple statements to be executed before COMMIT and ROLLBACK, we need to do the following two steps (or both) :
1. Set autocommit property of MySQL to 0, and default to 1
2. Open 1 transaction explicitly using START TRANSACTION statement (then autocommit property will be automatically set to 0)
If one transaction is already open, SET autocommit=0 will not work, because START TRANSACTION will implicitly commit all current changes in session, close the existing transaction, and open a new one.
Examples of stored procedures using the SET AUTOCOMMIT statement:
Sql code
Usually a transaction is completed when the COMMIT or ROLLBACK statements are executed, but some DDL statements implicitly trigger COMMIT, so use as little or as little as possible in the transaction:
Sql code
3. Use Savepoint
Rollback with savepoint is a bit of a performance drain, but 1 can be overwritten with IF
Scenario 1, a good use of savepoint, is "nested transactions." you may want your program to execute a small transaction, but you don't want to roll back a larger transaction:
Sql code
4. Transactions and locks
The ACID property of a transaction can only be implemented by limiting synchronous changes to the database and thus by locking the modified data.
The lock is not released until the transaction triggers an COMMIT or ROLLBACK statement.
The disadvantage is that subsequent transactions must wait for the previous transaction to complete before they can begin execution, and throughput declines as the time waiting for the lock to be released grows.
MySQL/InnoDB minimizes lock contention with row level locks. There is no limit to modifying the data of other rows in 1table, and the data can be read without waiting.
You can use the FOR UPDATE or LOCK IN SHARE MODE statements in SELECT statements to add row-level locks
Sql code
FOR UPDATE locks the rows returned by the SELECT statement, and other SELECT and DML statements must wait for the transaction in which the SELECT statement is located to complete
LOCK IN SHARE MODE is the same as FOR UPDATE, but allows other session SELECT statements to be executed and allows the SHARE MODE lock to be acquired
Deadlock:
A deadlock occurs when two transactions are waiting for each other to release the lock
When MySQL/InnoDB checks into a deadlock, it forces a transaction rollback and triggers an error message
For InnoDB, the selected rollback transaction is the one that does the least amount of work (the least number of rows modified)
Java code
Deadlocks can occur in any database system, but are less likely for a row-level locked database like MySQL/InnoDB.
You can reduce the frequency of deadlocks by locking row or table in the order of 1 cause and keeping transactions as short as possible.
If the deadlock is not easy debug, you can add some logic to your program to handle the deadlock and retry the transaction, but this part of the code is difficult to maintain
Therefore, a better way to avoid deadlocks is to add row-level locks in the order of 1 before making any changes to avoid deadlocks:
Java code
Set deadlock ttl: innodb_lock_wait_timeout, default is 50 seconds
If you use a mixture of InnoDB and non-InnoDB tables in a single transaction, MySQL cannot detect the deadlock and will throw an "lock wait timeuot" 1205 error
Optimistic and pessimistic locking strategies:
Pessimistic lock: the rows are locked while the data is being read, and other updates to the rows are not allowed to continue until the pessimistic lock ends
Optimistic: the data is not locked when it is read. When it is updated, check whether the data has been updated. If so, cancel the current update
Generally, we only choose optimistic lock when the waiting time of pessimistic lock is too long to be acceptable
Examples of pessimistic locks:
Java code
Examples of optimistic locks:
Java code
5. Transaction design guidelines
1. Keep your transactions short
2. Try to avoid rollback in transactions
3. Try to avoid savepoint
4. By default, it relies on pessimistic locks
Consider optimistic locking for throughput critical transactions
6, shows the declaration to open the transaction
7. The fewer rows of locks, the better, and the shorter the lock time, the better
The stored program provides an excellent mechanism for defining, encapsulating, and managing transactions.
1, transaction support for MySQL
1) MySQL's transaction support is not tied to the MySQL server itself, but to the storage engine:
Sql code
MyISAM : does not support transactions, used for read-only programs to improve performance
InnoDB Support: ACID Transactions, row-level locking, concurrency
Berkeley DB : support transactions
MyISAM : does not support transactions, used for read-only programs to improve performance
InnoDB Support: ACID Transactions, row-level locking, concurrency
Berkeley DB : support transactions 2 ) Isolation level:
The isolation level determines the likely impact of a transaction in one session on another session, the operation of concurrent session on the database, and the 1-uniqueness of the data seen in one session
The ANSI standard defines four isolation levels, and InnoDB of MySQL supports:
Java code
READ UNCOMMITTED : the lowest level of isolation, also known as isolation dirty read That allows the 1 1 transaction read not yet commit The data so may improve performance, however dirty read Maybe not what we want
READ COMMITTED In: 1 Only already is allowed in each transaction commit The record is visible if session In the select It's still in the query 1session At this time insert1 , the newly added data is not visible
REPEATABLE READ In: 1 After the transaction starts, the others session Changes to the database are not visible in this transaction until this transaction commit or rollback . in 1 This is repeated in each transaction select The results of the 1 Unless in this transaction update The database.
SERIALIZABLE : the highest level of isolation, allowing only serial execution of transactions. To do this, the database locks the records that have been read for each row, and the rest session Cannot modify data up to the front 1 Transaction ends, transaction commit Or when the lock is cancelled.
READ UNCOMMITTED : the lowest level of isolation, also known as isolation dirty read That allows the 1 1 transaction read not yet commit The data so may improve performance, however dirty read Maybe not what we want
READ COMMITTED In: 1 Only already is allowed in each transaction commit The record is visible if session In the select It's still in the query 1session At this time insert1 , the newly added data is not visible
REPEATABLE READ In: 1 After the transaction starts, the others session Changes to the database are not visible in this transaction until this transaction commit or rollback . in 1 This is repeated in each transaction select The results of the 1 Unless in this transaction update The database.
SERIALIZABLE : the highest level of isolation, allowing only serial execution of transactions. To do this, the database locks the records that have been read for each row, and the rest session Cannot modify data up to the front 1 Transaction ends, transaction commit Or when the lock is cancelled. You can set this using the following statement MySQL the session Isolation level:
Sql code
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 for MySQL is REPEATABLE READ. Be careful when setting the isolation level to READ UNCOMMITTED or SERIALIZABLE. READ UNCOMMITTED can cause serious data integrity issues, while SERIALIZABLE can cause performance issues and increase the chance of deadlocks
3) transaction management statement:
Sql code
START TRANSACTION : start the transaction, autocommit Set to 0 If there are already 1 When a transaction is running, it is triggered 1 A hidden COMMIT
COMMIT : commit the transaction, save the changes, and release the lock
ROLLBACK : rolls back all changes made to the database by this transaction, then terminates the transaction and releases the lock
SAVEPOINT savepoint_name : create 1 a savepoint Identifier to ROLLBACK TO SAVEPOINT
ROLLBACK TO SAVEPOINT savepoint_name : rollback to from savepoint_name Start all changes to the database so that you can roll back the transaction 1 The part guaranteed to be changed 1 Subsets are submitted
SET TRANSACTION : allows you to set the isolation level of a transaction
LOCK TABLES : allows explicit locking 1 One or more table , will implicitly close the currently open transaction and is recommended for execution LOCK TABLES Explicit before statement commit or rollback . we 1 So as a 1 Generally not used in transaction code LOCK TABLES
START TRANSACTION : start the transaction, autocommit Set to 0 If there are already 1 When a transaction is running, it is triggered 1 A hidden COMMIT
COMMIT : commit the transaction, save the changes, and release the lock
ROLLBACK : rolls back all changes made to the database by this transaction, then terminates the transaction and releases the lock
SAVEPOINT savepoint_name : create 1 a savepoint Identifier to ROLLBACK TO SAVEPOINT
ROLLBACK TO SAVEPOINT savepoint_name : rollback to from savepoint_name Start all changes to the database so that you can roll back the transaction 1 The part guaranteed to be changed 1 Subsets are submitted
SET TRANSACTION : allows you to set the isolation level of a transaction
LOCK TABLES : allows explicit locking 1 One or more table , will implicitly close the currently open transaction and is recommended for execution LOCK TABLES Explicit before statement commit or rollback . we 1 So as a 1 Generally not used in transaction code LOCK TABLES
2. Define transactions
The default behavior of MySQL is to execute one COMMIT statement after each SQL statement, effectively separating each statement into one transaction.
In complex application scenarios this approach will not satisfy the requirements.
In order to open the transaction and allow multiple statements to be executed before COMMIT and ROLLBACK, we need to do the following two steps (or both) :
1. Set autocommit property of MySQL to 0, and default to 1
2. Open 1 transaction explicitly using START TRANSACTION statement (then autocommit property will be automatically set to 0)
If one transaction is already open, SET autocommit=0 will not work, because START TRANSACTION will implicitly commit all current changes in session, close the existing transaction, and open a new one.
Examples of stored procedures using the SET AUTOCOMMIT statement:
Sql code
delimiter $$
use test$$
create procedure t_insert_table()
begin
/** Whether the mark is wrong or not */
declare t_error int default 0;
/** If there is a sql Exception, will t_error Set to 1 Then continue to perform the following operations */
declare continue handler for sqlexception set t_error=1; -- Error handling
/** Displays the start of the transaction, and after starting it, autocommit The 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('zhangsan',34);
/** The marker is changed , Indicates 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
/** Whether the mark is wrong or not */
declare t_error int default 0;
/** If there is a sql Exception, will t_error Set to 1 Then continue to perform the following operations */
declare continue handler for sqlexception set t_error=1; -- Error handling
/** Displays the start of the transaction, and after starting it, autocommit The 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('zhangsan',34);
/** The marker is changed , Indicates that the transaction should be rolled back */
if t_error=1 then
rollback; -- Transaction rollback
else
commit; -- Transaction commit
end if;
end$$
delimiter ;
Usually a transaction is completed when the COMMIT or ROLLBACK statements are executed, but some DDL statements implicitly trigger COMMIT, so use as little or as little as possible in the transaction:
Sql code
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
Rollback with savepoint is a bit of a performance drain, but 1 can be overwritten with IF
Scenario 1, a good use of savepoint, is "nested transactions." you may want your program to execute a small transaction, but you don't want to roll back a larger transaction:
Sql code
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;
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 property of a transaction can only be implemented by limiting synchronous changes to the database and thus by locking the modified data.
The lock is not released until the transaction triggers an COMMIT or ROLLBACK statement.
The disadvantage is that subsequent transactions must wait for the previous transaction to complete before they can begin execution, and throughput declines as the time waiting for the lock to be released grows.
MySQL/InnoDB minimizes lock contention with row level locks. There is no limit to modifying the data of other rows in 1table, and the data can be read without waiting.
You can use the FOR UPDATE or LOCK IN SHARE MODE statements in SELECT statements to add row-level locks
Sql code
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, and other SELECT and DML statements must wait for the transaction in which the SELECT statement is located to complete
LOCK IN SHARE MODE is the same as FOR UPDATE, but allows other session SELECT statements to be executed and allows the SHARE MODE lock to be acquired
Deadlock:
A deadlock occurs when two transactions are waiting for each other to release the lock
When MySQL/InnoDB checks into a deadlock, it forces a transaction rollback and triggers an error message
For InnoDB, the selected rollback transaction is the one that does the least amount of work (the least number of rows modified)
Java code
mysql > CALL tfer_funds(1,2,300);
ERROR 1213 (40001): Deadlock found when trying to get lock; try restarting transaction
Deadlocks can occur in any database system, but are less likely for a row-level locked database like MySQL/InnoDB.
You can reduce the frequency of deadlocks by locking row or table in the order of 1 cause and keeping transactions as short as possible.
If the deadlock is not easy debug, you can add some logic to your program to handle the deadlock and retry the transaction, but this part of the code is difficult to maintain
Therefore, a better way to avoid deadlocks is to add row-level locks in the order of 1 before making any changes to avoid deadlocks:
Java code
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 deadlock ttl: innodb_lock_wait_timeout, default is 50 seconds
If you use a mixture of InnoDB and non-InnoDB tables in a single transaction, MySQL cannot detect the deadlock and will throw an "lock wait timeuot" 1205 error
Optimistic and pessimistic locking strategies:
Pessimistic lock: the rows are locked while the data is being read, and other updates to the rows are not allowed to continue until the pessimistic lock ends
Optimistic: the data is not locked when it is read. When it is updated, check whether the data has been updated. If so, cancel the current update
Generally, we only choose optimistic lock when the waiting time of pessimistic lock is too long to be acceptable
Examples of pessimistic locks:
Java code
READ UNCOMMITTED : the lowest level of isolation, also known as isolation dirty read That allows the 1 1 transaction read not yet commit The data so may improve performance, however dirty read Maybe not what we want
READ COMMITTED In: 1 Only already is allowed in each transaction commit The record is visible if session In the select It's still in the query 1session At this time insert1 , the newly added data is not visible
REPEATABLE READ In: 1 After the transaction starts, the others session Changes to the database are not visible in this transaction until this transaction commit or rollback . in 1 This is repeated in each transaction select The results of the 1 Unless in this transaction update The database.
SERIALIZABLE : the highest level of isolation, allowing only serial execution of transactions. To do this, the database locks the records that have been read for each row, and the rest session Cannot modify data up to the front 1 Transaction ends, transaction commit Or when the lock is cancelled.
READ UNCOMMITTED : the lowest level of isolation, also known as isolation dirty read That allows the 1 1 transaction read not yet commit The data so may improve performance, however dirty read Maybe not what we want
READ COMMITTED In: 1 Only already is allowed in each transaction commit The record is visible if session In the select It's still in the query 1session At this time insert1 , the newly added data is not visible
REPEATABLE READ In: 1 After the transaction starts, the others session Changes to the database are not visible in this transaction until this transaction commit or rollback . in 1 This is repeated in each transaction select The results of the 1 Unless in this transaction update The database.
SERIALIZABLE : the highest level of isolation, allowing only serial execution of transactions. To do this, the database locks the records that have been read for each row, and the rest session Cannot modify data up to the front 1 Transaction ends, transaction commit Or when the lock is cancelled. You can set this using the following statement MySQL the session Isolation level:
Examples of optimistic locks:
Java code
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 guidelines
1. Keep your transactions short
2. Try to avoid rollback in transactions
3. Try to avoid savepoint
4. By default, it relies on pessimistic locks
Consider optimistic locking for throughput critical transactions
6, shows the declaration to open the transaction
7. The fewer rows of locks, the better, and the shorter the lock time, the better