MySQL stored procedure's Affairs Chapter

MySQL stored procedure's Affairs Chapter

Four characteristics of the transaction:

Acid:atomic (atomicity), consistent (consistency), Isolated (Independence), durable (persistence)

MySQL's transactional support is not tied to the MySQL server itself, but is related to the storage Engine:

SQL Code  

1. MyISAM: Does not support transactions, for read-only programs to lift high performance

2. InnoDB: Supports acid transactions, row-level locks, concurrency

3. Berkeley DB: Support transaction

Transaction ISOLATION LEVEL criteria:

The ANSI (American National Standards Institute) standard defines 4 isolation levels, andMySQL InnoDB supports:

SQL code

1. READ UNCOMMITTED: The lowest level of isolation, often referred to as dirty Read, which allows a transaction to read data that is not yet commit, may improve performance, but dirty read may not be what we want   

2. Read COMMITTED: Allow only commit records to be visible in a transaction, if select is still in the query in the session, and the other session inserts a record at this time, The newly added data is not visible

3. repeatable READ: After a transaction has started, other session modifications to the database are not visible in this transaction until this transaction commits or rollback. Duplicate the result of a select in a transaction , unless the database is update in this transaction .

4. SERIALIZABLE: The highest level of isolation, allowing only transactions to be executed serially. To achieve this, the database locks the records that have been read for each row, and the other session cannot modify the data until the previous transaction ends and the lock is released when the transaction commits or cancels.   

The default isolation level for MySQL is:repeatable READ

READ uncommitted level causes serious problems with data integrity and requires you to control how data integrity is maintained

Serializable can cause performance problems and increase the chances of deadlocks

Mysql transaction Operation statement:

1. Start TRANSACTION: Start transaction,autocommit set to 0, If there is already a transaction running, it will trigger a hidden COMMIT

2. Commit: Commit the transaction, save the changes, release the lock

3. ROLLBACK: Roll back all changes to the database for this transaction, and then end the transaction , release the lock

4. savepoint savepoint_name: Create a savepoint identifier ROLLBACK to savepoint

5. ROLLBACK to SavePoint savepoint_name: Rollback to all changes to the database starting from Savepoint_name, allowing the rollback of part of the transaction to ensure that a subset of the changes is committed

6. Set TRANSACTION: Allows to set the isolation level of a transaction

7. Lock TABLES: Allows explicit locking 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

Defining transactions

The default behavior of MySQL is to execute a COMMIT statement after each SQL statement executes , effectively independently of each statement as a single transaction.
In a complex application scenario, this approach does not meet the requirements.
In order to open a transaction and allow more than one statement to be executed before commit and rollback, we need to do the following two steps:
1, set MySQL's autocommit property to 0, default to 1
2, open a transaction explicitly using the START transaction statement

As stated above, set autocommit=0 does not work when starting a thing with start transaction, because the start transaction implicitly commits All current changes in the session, end the existing transaction, and open a new transaction.

example of a stored procedure using the SET autocommit statement:

SQL Code  

1. CREATE PROCEDURE tfer_funds

2. (from_account int, to_account int, tfer_amount numeric (2< c20>))

3. BEGIN

4. SET autocommit=0;

5.

6. UPDATE account_balance SET balance=balance-tfer_amount WHERE account_id=from_account;

7.

8. UPDATE account_balance SET balance=balance+tfer_amount WHERE account_id=to_account;

9.

COMMIT;

END;

example of opening a transaction using START Transaciton :

SQL Code  

1. CREATE PROCEDURE tfer_funds

2. (from_account int, to_account int, tfer_amount Numeric (ten,2))

3. BEGIN

4. START TRANSACTION;

5.

6. UPDATE account_balance SET balance=balance-tfer_amount WHERE account_id=from_account;

7.

8. UPDATE account_balance SET balance=balance+tfer_amount WHERE account_id=to_account;

9.

COMMIT;

END;

Typically a commit or rollback statement is executed to complete a transaction, but some DDL statements, and so on, implicitly trigger a commit, so you should use as few or as little attention as possible in the transaction:

Java Code  

1. ALTER FUNCTION

2. ALTER PROCEDURE

3. ALTER TABLE

4. BEGIN

5. CREATE DATABASE

6. CREATE FUNCTION

7. CREATE INDEX

8. CREATE PROCEDURE

9. 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

About savepoint do not study at present

Transactions and Locks

The ACID properties of a transaction can only be implemented by restricting synchronization changes to the database, by locking the data.
The Lock is not released until a commit or rollback statement is triggered by the transaction.
The disadvantage of this is that the subsequent transaction must wait for the previous transaction to complete before it can begin execution, and the throughput decrements as the time to wait for the lock to release increases.

MySQL's InnoDB minimizes lock contention through row-level locks. there is no limit to the data that modifies other rows in the same table, and the read data can never wait.

You can use the for update or the lock in SHARE mode statement to add row-level locks in the SELECT statement

1. SELECT select_statement options [for update| LOCK in SHARE MODE]

The For update locks the rows returned by the SELECT statement, and other Select and DML statements must wait for the transaction that contains the SELECT statement to complete
Lock in SHARE mode with for UPDATE, but allows the SELECT statement of the other session to execute and allow the SHARE mode lock to be obtained

Here's a look at the deadlock, pessimistic lock, optimistic lock, but not in-depth grasp, currently only master the concept

SQL code

1. A deadlock occurs when two transactions wait for each other to release the lock
2. Pessimistic lock: Locks those lines when reading data, other updates to these lines need to wait until the pessimistic lock ends to continue
3. Optimistic: Read data is not locked, update check whether the data has been updated, if yes cancel the current update

In general, when the waiting time of pessimistic lock is too long to accept, we will choose optimistic lock

Transaction Design Guide

1, keep the transaction short

2, try to avoid rollback in the transaction

3, try to avoid savepoint

4, by default, relies on pessimistic locks

5, consider optimistic locking for throughput-demanding transactions

6, Show claims open transaction

7, the less the lock line, the better, the shorter the lock time better

MySQL stored procedure's Affairs Chapter