Mysql stored procedure thing Management

Mysql stored procedure thing management ACID: Atomic, Consistent, Isolated, Durable storage provides an excellent mechanism to define, encapsulate, and manage transactions. 1. MySQL transaction support is not bound to the MySQL server itself, but related to the storage engine: Java code MyISAM: does not support transactions, used for read-only programs to improve performance InnoDB: supports ACID transactions, row-level locks, and concurrent Berkeley DB: supports transaction isolation levels: 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 code read uncommitted: the lowest level of isolation, usually known as 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, the other sess Ion inserts a record at this time, and the newly added data is invisible to 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: Java code set transaction isolation level {read uncommitted | read committed | repeatable read | SERIALIZABLE} MySQL Default isolation level is repeatable read, be careful when setting the isolation level to read uncommitted or SERIALIZABLE. read uncommitted will cause serious data integrity problems, and SERIALIZABLE will cause performance problems and increase the chance of deadlock transaction management statement: java code 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 the changes made by this transaction to the database 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 TO the database starting from savepoint_name, so that part of the transaction can be rolled back, make sure that a subset of changes is submitted. set transaction: allows you to SET the isolation level of the TRANSACTION. lock tables: allows you to explicitly LOCK one or more TABLES and implicitly close the currently opened transactions, we recommend that you explicitly run the commit or rollback statement before executing the lock tables statement. Generally, we do not use lock tables 2 in the transaction code. The default action of defining the transaction MySQL is to execute a COMMIT statement after each SQL statement is executed, thus, each statement is effectively independent into a transaction. In complex application scenarios, this method cannot meet the requirements. To open a transaction and allow multiple statements to be executed before COMMIT and ROLLBACK, we need to do the following two steps: 1. Set the autocommit attribute of MySQL to 0 and the default value is 1 2, 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 submits all the current changes in the session, end an existing transaction and start a new transaction. Example of a stored PROCEDURE using the set autocommit statement: Java code create procedure tfer_funds (from_account int, to_account int, tfer_amount numeric () 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: java code create procedure tfer_funds (from _ Account int, to_account int, tfer_amount numeric () 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, but some DDL statements will trigger the COMMIT implicitly. Therefore, use as few or pay attention as possible in the transaction: java code ALTER FUNCTION ALTER PROCEDURE ALTER TABLE BEGIN CREATE D Atabase 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 to use savepoint to roll back will inevitably consume some performance. Generally, one of the good scenarios of using IF to rewrite savepoint is "nested transactions". You may want the program to execute a small transaction, but you do not want to roll back a larger transaction: Java code create procedure nested_tfer_funds (in_from_acct I NTEGER, 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 = balan Ce + 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, the ACID attribute of transactions and lock transactions 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 a row-Level LOCK. the Java code SELECT select_statement options [for update | lock in share mode] for update locks the return value of the SELECT statement. row, other SELECT and DML statements must wait FOR the transaction where the SELECT statement is located to complete the lock in share mode and for update, but allow the SELECT statements of other sessions to be executed and allow the share mode lock deadlock to be obtained: A deadlock occurs when two transactions wait for each other to release the lock. when MySQL/InnoDB checks the deadlock, it forces a transaction rollback and triggers an error message for InnoDB, the selected rollback transaction is the transaction that has completed the least work (with the least modified rows) Java code mysql> CALL tfer_fu Nds (300, 1213); ERROR 40001 (): Deadlock found when trying to get lock; try restarting transaction Deadlock may occur in any database system, however, row-level locks such as MySQL and InnoDB are less likely. 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. However, this part of code is difficult to maintain after it is too much, A 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 code create procedure tfer_funds3 (from_account INT, to_account INT, tfer_amount NUMERIC () begin declare partition 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 + balance WHERE account_id = to_account; CLOSE lock_cursor; COMMIT; END; SET ttl: innodb_lock_wait_timeout. The default value is 50 seconds. If you mix InnoDB and non-InnoDB tables in a transaction, MySQL cannot detect deadlocks, at this time, the system will throw the "lock wait timeuot" 1205 error. optimistic and pessimistic lock policies: Pessimistic lock: these rows are locked when reading data, other updates to these rows must wait until the end of the pessimistic lock to continue to be optimistic: when the data is read, it is not locked. When the update is updated, check whether the data has been updated, if yes, cancel the current update. Generally, when the waiting time of the pessimistic lock is too long to be accepted, we will select the optimistic lock. Example: 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 (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 code create procedure tfer_funds (from_account INT, to_account INT, tfer_amount NUMERIC (), OUT status INT, OUT message VARCHAR (30) begin declare from_account_balance NUMERIC (8, 2); DECLARE invalid NUMERIC (8, 2); DECLARE invalid TIMESTAMP; SELECT account_timestamp, balance INTO balance, 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 wait (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 <> balance) 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 Java code 1, keep the transaction short 2, try to avoid rollback 3 in the transaction, try to avoid savepoint 4, by default, rely on pessimistic lock 5, optimistic lock 6 is considered for transactions with demanding throughput requirements. It indicates that transaction 7 is opened. The fewer rows, the better. The shorter the lock time, the better.