MYSQL Transaction Directory
- MySQL series (i): basic knowledge Summary
- MySQL series (ii): MySQL transaction
- MySQL Series (iii): Index
What is a transaction (transaction)
- Ensure that the batch operation is either fully executed or not executed at all, maintaining the integrity of the data. That means either success or failure.
- Transactions can be n-SQL statements (n>=0)
- Not all database engine support transactions, InnoDB engine supports transaction processing
Four major features of transactions
- Atomicity (Atomic): All operations of a transaction are either fully executed or not executed at all. If an error occurs midway, it does not stop, but rolls back to the state before the transaction
- Consistency (consistency): If a transaction is consistent before execution, it is consistent after execution, does not break the integrity of the relational data and the consistency of the business logic, and the transaction is implemented as expected.
- Isolation (Isolation): Isolation prevents data inconsistencies when multiple transactions are concurrent
- Persistence (Durability): Modifications to the database are permanent after the transaction executes successfully
Transaction concurrency without transaction isolation
- Dirty read: Transaction a reads data that is not committed by transaction B, and if transaction B fails to roll back at this point, then transaction A is reading dirty data. For example, transaction a modifies money, and transaction B reads the updated result of transaction A, but if transaction A is rolled back, then transaction B reads dirty data.
- Non-repeatable READ: In the same transaction, the result of reading the same data is inconsistent. Transaction a reads from transaction B before the data is updated, and transaction B updates the commit, and transaction a reads again, at which time the data is read differently two times.
- Phantom read: In the same transaction, the same query returns multiple times in different results. Transaction B queries the number of records in the table, then transaction a inserts a record into the table, and transaction B then queries the discovery record number differently.
Difference
- Dirty reads and non-repeatable reads: Dirty reads are updates that the transaction has read that has not yet committed the transaction. Non-repeatable reads are different from the data that is read several times in the same transaction.
- The difference between non-repeatable reads and Phantom reads: Both are in the same transaction, the former is different from reading the data several times, the latter is the whole difference of reading the data several times.
Isolation level
| Isolation Level |
function |
| Serializable (serialization) |
Avoid dirty reading, non-repeatable reading, Phantom reading |
| Repeatable (repeatable reading) |
Avoid dirty reads and non-repeatable reads |
| Read Committed (Reading submitted) |
Avoid dirty reading |
| READ UNCOMMITTED (not submitted) |
None |
- MySQL supports the above 4 isolation levels, the default is repeatable read
Use of transactions
start transcation;delete from user;savepoint dele;****rollback dele;commit;
- Transcation Transaction Start
- Rollback rollback to Transaction start
- SavePoint Keep rollback position
- Rollback Point_name rollback to the set location
Use note points
- If SQL is running correctly in a transaction and there is no commit after it, the result is not updated to the database, so you need to add the commit manually.
- If there is an error in a part of the SQL statement in the transaction, the error statement does not follow. We may think that the correct operation will rollback the undo, but in fact it does not undo the correct operation, and if there is no error in the case of a commit, the previous correct operation will take effect, the database will be updated.
I think sharing is a kind of spirit, sharing is my pleasure, not to say I think I said must be right, I said may be a lot of wrong, but I hope I said something is my life experience and thinking, is to give a lot of people to reflect on, maybe give you a second, half a second, even if a word a little bit of truth, triggered their This is my greatest value. (This is my favorite word, but also I write the original intention of the blog)
MySQL Series (ii)
