Acid:atomic, consistent, Isolated, durable
The store program provides an excellent mechanism for defining, encapsulating, and managing transactions.
1,mysql Transaction Support
1 The MySQL transaction support is not tied to the MySQL server itself, but is related to the storage engine:
SQL code
Copy Code code as follows:
MyISAM: Transactions are not supported for read-only programs to improve performance
InnoDB: Supports acid transactions, row-level locks, concurrency
Berkeley DB: Supporting Transactions
MyISAM: Transactions are not supported for read-only programs to improve performance
InnoDB: Supports acid transactions, row-level locks, concurrency
Berkeley DB: Support transaction 2 Isolation Level:
The isolation level determines how transactions in one session may affect another session, the operation of concurrent sessions to the database, and the consistency of data seen in a session
The ANSI standard defines 4 isolation levels, which are supported by MySQL InnoDB:
Java code
Copy Code code as follows:
READ UNCOMMITTED: The lowest level of isolation, often called dirty Read, allows a transaction to read data that has not been commit, which may improve performance, but dirty read may not be what we want
READ committed: Only allowed records that have been commit are visible in a transaction, if the select is still in the query and another session inserts a record at that time, the newly added data is not visible
Repeatable READ: After a transaction begins, modifications to the database by other sessions are not visible in this transaction until this transaction commits or rollback. Duplicates the result of a select in a transaction, unless the update database is in this transaction.
SERIALIZABLE: The highest level of isolation, allowing only transactions to be executed serially. For this purpose, the database locks the records that have been read in each row, and the other sessions cannot modify the data until the previous transaction ends and the lock is released when a transaction commits or cancels.
READ UNCOMMITTED: The lowest level of isolation, often called dirty Read, allows a transaction to read data that has not been commit, which may improve performance, but dirty read may not be what we want
READ committed: Only allowed records that have been commit are visible in a transaction, if the select is still in the query and another session inserts a record at that time, the newly added data is not visible
Repeatable READ: After a transaction begins, modifications to the database by other sessions are not visible in this transaction until this transaction commits or rollback. Duplicates the result of a select in a transaction, unless the update database is in this transaction.
SERIALIZABLE: The highest level of isolation, allowing only transactions to be executed serially. For this purpose, the database locks the records that have been read in each row, and the other sessions cannot modify the data until the previous transaction ends and the lock is released when a transaction commits or cancels. You can set the session isolation level for MySQL using the following statement:
SQL code
Copy Code code 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 MySQL default isolation level is repeatable read, be careful when setting isolation levels of READ UNCOMMITTED or serializable, read UNCOMMITTED can cause serious data integrity problems, and serializable can cause performance problems and increase the likelihood of deadlocks.
3) Transaction Management statement:
SQL code
Copy Code code as follows:
Start TRANSACTION: Starting transaction, Autocommit set to 0, if a transaction is already running, a hidden commit is triggered
Commit: Commit TRANSACTION, save changes, release lock
ROLLBACK: Rolls back all changes to the database for this transaction, then ends the transaction, releasing the lock
SavePoint savepoint_name: Create a savepoint identifier rollback to savepoint
ROLLBACK to SavePoint savepoint_name: Rollback to all changes to the database starting from Savepoint_name, allowing a rollback of part of the transaction to ensure that a subset of the changes is committed
Set TRANSACTION: Allows setting the isolation level of a transaction
Lock tables: Allows an explicit lock of one or more table, implicitly closes the currently open transaction, and recommends an explicit commit or rollback before executing the lock tables statement. We generally do not use lock TABLES in the transaction code
Start TRANSACTION: Starting transaction, Autocommit set to 0, if a transaction is already running, a hidden commit is triggered
Commit: Commit TRANSACTION, save changes, release lock
ROLLBACK: Rolls back all changes to the database for this transaction, then ends the transaction, releasing the lock
SavePoint savepoint_name: Create a savepoint identifier rollback to savepoint
ROLLBACK to SavePoint savepoint_name: Rollback to all changes to the database starting from Savepoint_name, allowing a rollback of part of the transaction to ensure that a subset of the changes is committed
Set TRANSACTION: Allows setting the isolation level of a transaction
Lock tables: Allows an explicit lock of one or more table, implicitly closes the currently open transaction, and recommends an explicit commit or rollback before executing the lock tables statement. We generally do not use lock TABLES in the transaction code
2, defining a transaction
MySQL's default behavior is to execute a COMMIT statement after each SQL statement executes, effectively making each statement independent of one transaction.
In a complex application scenario, this approach is not enough to meet the demand.
In order to open a transaction, allowing multiple statements to be executed before commit and rollback, we need to do the following two steps (or in two ways):
1, set the MySQL autocommit property is 0, the default is 1
2, explicitly open a transaction using the START TRANSACTION statement (then the Autocommit property is automatically set to 0)
If a transaction is already open, the set autocommit=0 does not work because the start transaction implicitly submits 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 code
Copy Code code as follows:
Delimiter $$
Use test$$
CREATE PROCEDURE t_insert_table ()
Begin
/** Mark Error * *
DECLARE t_error int default 0;
/** If there is a SQL exception, set the T_error to 1 and continue with the following operation.
Declare continue handler for SqlException set t_error=1; --Error handling
/** shows the open transaction, the Autocommit value is automatically set to 0 when it is started.
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/** token is changed to indicate that the transaction should be rolled back/*
If T_error=1 Then
Rollback --Transaction rollback
Else
Commit --Transaction Submission
End If;
end$$
delimiter;
Delimiter $$
Use test$$
CREATE PROCEDURE t_insert_table ()
Begin
/** Mark Error * *
DECLARE t_error int default 0;
/** If there is a SQL exception, set the T_error to 1 and continue with the following operation.
Declare continue handler for SqlException set t_error=1; --Error handling
/** shows the open transaction, the Autocommit value is automatically set to 0 when it is started.
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/** token is changed to indicate that the transaction should be rolled back/*
If T_error=1 Then
Rollback --Transaction rollback
Else
Commit --Transaction Submission
End If;
end$$
delimiter;
A transaction is usually completed when a commit or ROLLBACK statement is executed, but some DDL statements can implicitly trigger a commit, so you should use as little or as much attention as possible in a transaction:
SQL code
Copy Code code 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, using SavePoint
Use of savepoint rollback unavoidably some performance consumption, generally can be rewritten with if
One of the good uses of SavePoint is "nested transactions", where you may want the program to perform a small transaction, but you do not want to roll back the larger transactions outside:
SQL code
Copy Code code 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 ' 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, Transaction and lock
The ACID properties of a transaction can be implemented only by restricting synchronization changes to the database, thereby by locking the modified data.
The lock is not released until the transaction triggers a 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 decreases as time waits for the lock to be released.
Mysql/innodb to minimize lock competition through row-level locks. This modifies the data of the other rows in the same table without restriction, and the read data can never wait.
You can use a for update or lock in SHARE mode statement in a SELECT statement to add a row-level lock
SQL code
Copy Code code 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, the other select and DML statements must wait for the transaction that contains the SELECT statement to complete
Lock in SHARE mode is with for UPDATE, but allows other session SELECT statements to execute and allow SHARE mode locks to be obtained
Deadlock:
Deadlocks occur when two of transactions wait for each other to release locks
When Mysql/innodb checks for a deadlock, it forces a transaction rollback and triggers an error message
For InnoDB, the selected rollback transaction is the least-completed transaction (the minimum number of rows modified)
Java code
Copy Code code as follows:
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 to be mysql/innodb such a row-level lock database.
You can reduce the frequency of deadlocks by locking the row or table in a consistent order and keeping the transaction as short as possible.
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 this part of the code is more difficult to maintain
So, the better way to avoid deadlocks is to add row-level locks in a certain order before making any changes, so you can avoid deadlocks:
Java code
Copy Code code 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 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, default to 50 seconds
If you mix InnoDB and InnoDB tables in one transaction, MySQL cannot detect deadlocks, and the lock wait Timeuot 1205 error is thrown
Optimistic and pessimistic locking strategies:
Pessimistic Lock: Lock the rows while reading the data, and other updates to these lines need to wait until the pessimistic lock is complete to continue
Optimistic: Do not lock when reading data, update to check whether the data has been updated, if yes, cancel the current update
Generally in the pessimistic lock waiting time is too long to accept, we will choose optimistic lock
Examples of pessimistic locks:
Java code
Copy Code code as follows:
CREATE PROCEDURE Tfer_funds
(From_account INT, To_account int,tfer_amount NUMERIC (10,2),
Out of 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;
Examples of optimistic locks:
Java code
Copy Code code as follows:
CREATE PROCEDURE Tfer_funds
(from_account int, to_account int, tfer_amount NUMERIC (10,2),
Out of 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
--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",
"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, Business Design Guide
1, keep the business short
2, try to avoid rollback in business
3, try to avoid savepoint
4, by default, depends on the pessimistic lock
5, optimistic locks are considered for throughput-demanding transactions
6, display declaration open transaction
7, the less the lock line the better, the less the lock time the better
