MySQL DBA system Learning (22) database transaction detailed

Last week to interview the database manager's work, after the written test passed is the direct interview, he asked me a question, asked me to introduce Kazakhstan database of the views and understanding, but do not know the wrong without the chaos of the disorderly answer, alas, the foundation is not solid AH.

Here is a good study of the MYSQLINNODB engine's business-related knowledge

One, why do we need database transactions?

This question seems very simple, but really can be said to understand or to see if our basic skills are not solid

For example, user A gives user B a transfer of 1000 yuan through ATM, then a account will be less than 1000 yuan, and B users will be more than 1000 yuan

In this process, two links are associated. The first account set aside money must be guaranteed to be properly deposited into the second account, if the second link is not completed, the whole process should be canceled, otherwise there will be loss of money problems. The whole transaction process, can be regarded as a thing, success is all successful, failure will need to undo all, so as to avoid when the operation of the intermediate link problems, resulting in inconsistent data problems.

Second, the characteristics of database transactions

of atomic

The transaction must be an atomic unit of work, or all of its data modifications, or none of them executed.

Consistency

When a transaction completes, you must keep all the data in a consistent state. In the related database, all rules must be applied to the modification of the transaction to preserve the integrity of all data.

When a transaction ends, all internal data structures, such as a B-tree index or a two-way list, must be correct.

Isolation of

Modifications made by a postback must be isolated from modifications made by any other concurrent transaction. The state that the data is in when the transaction views the data, or if another concurrent transaction modifies its previous state.

Or another transaction modifies the state after it, the transaction does not view data in the middle state. This is called serializable because it can reload the start data and replay a series of transactions.

So that the state of the data at the end is the same as that of the original transaction.

Persistence of

After the transaction is completed, its impact on the system is permanent. This modification is maintained even if a system failure occurs.

Iii. How to use transactions in MySQL

1, MySQL what engine can use transactions

Only innodb/bdb Transaction_safe table can be supported.

First need your MySQL support InnoDB engine, view your MySQL supported engine syntax for:

Mysql> Show Engines \g; 1. Row *************************** engine:ndbcluster support:no comment:clustered, fault-tolerant tables T Ransactions:null xa:null savepoints:null *************************** 2. Row *************************** Engine:mrg_myisam support:yes comment:collection of identical MYISAM ta Bles transactions:no xa:no savepoints:no *************************** 3.  Row *************************** engine:blackhole support:yes Comment:/dev/null storage Engine (anything You are write to it disappears) Transactions:no Xa:no savepoints:no *************************** 4. Row *************************** engine:federated support:no comment:federated MySQL storage Engine Tran Sactions:null xa:null savepoints:null *************************** 5. Row *************************** engine:memory Support:yes Comment: Hash based, stored in memory, useful for temporary tables Transactions:no xa:no-Savepoints:no *********** 6. Row *************************** Engine:innodb support:yes comment:supports transactions, Row-level Lock ING, and foreign keys Transactions:yes xa:yes savepoints:yes *************************** 7. Row *************************** Engine:myisam Support:default comment:default Engine as of MySQL 3.23 W ITH great performance transactions:no Xa:no Savepoints:no *************************** 8.
          Row *************************** engine:csv support:yes comment:csv Storage Engine Xa:no Savepoints:no 8 rows in Set (0.00 sec) Error:no query specified