I, an overview of the transaction
- A transaction is an execution unit of a program
- Transactions consist of a set of SQL (1 or more).
A transaction can have multiple SQL components. TPS is a transaction per second. It is not very accurate to judge the high and low volume of business only through TPS. If there is a lot of SQL in the thing, even if TPS is lower, the actual QPS is relatively high.
II, the characteristics of a transaction
As we all know, the characteristics of transactions are called ACID. We describe these four characteristics in very popular language, and concretely implement the following analysis.
All SQL operations in a transaction are atomic or do or do not.
Atomicity is guaranteed by redo
The state before and after the transaction is consistent, without breaking the data structure and constraints.
Consistency is guaranteed by Undo
The modification made by an office is not visible to other transactions, like serial execution (and transaction isolation level is the two concept).
Isolation is guaranteed by lock
Once written, the data will not be lost
Persistence is guaranteed by redo and undo
Transaction type Explain flat transactions Flat transaction, the most common transaction flat transactions with savepoints Transaction with a save point chained transactions Chain transaction, automatic begin after a transaction commit, meaningless. nested transactions Nested transactions, currently without database support distributed transactions Flat transactions in distributed environment, each node supports acid, which is a higher standard of transaction acid. long transactions A very long run of transactions, such as the bank interest service, which will be delayed and dismantled as small as possible (and online DDL, like Pt, not for acceleration, but slower to reduce the delay of master and slave).
Distributed transaction case: bank transfer 1W yuan to Agricultural Bank
In fact, this is not done with distributed transactions, deceiving and deceiving people. All banking networks are connected. No, this is the question in detail.
Master-slave delay is a relatively troublesome problem faced by MySQL in recent years. The mechanism of master-slave replication is to use binlog instead of Oracle.
innodbMyISAM does not support transactions except nested transactions.
IV, transaction operation
4.1 open / close transaction
Law 1:Begin;XXX...;Commit/rollback;Law 2:Start transaction;XXX...;Commit/rollback;Why are there second ways?Because BEgin is a keyword in the storage process, heh heh...
- A good habit
1、No matter what you do, first play a begin, secure, do not play begin will automatically submit auto commit, Oracle will not automatically submit.
2、show variables like ‘autocommit’; The default value is 1, which is set to 0, so that delete can be rollback directly.
4.2 another game
begin; aaa savepoint s1; Setting up a save pointBBBRollback to S1; rollback to AAA, AAA has not been submittedIt's time to open a new session. You can't see AAA in it.Finally, commit or rollback, of course, can continue to perform.Other operations go to commit or rollback
4.3 online common problems”
Online services often encounter no commit in the code, resulting in no release of all the locks that correspond to the transaction, causing other threads to use the corresponding resources to not submit them.
How do you find out?
show processlist;The following 4 conditions are met:
1、The user is a developed account2, Command is sleep3, Time is very big4, Info is NULL
There are many threads on line sleep, but this time is not very high.
We usually use the thread pool to automatically maintain the active thread and maintain it every other time (select 1 to maintain connectivity, so this time should be consistent with the maintenance cycle), and we find that the wrong process can be manually dropped by kill.
select * from information_schema.innodb_trx\GYou can see it, but it’s more convenient