MySQL Transaction handling issues

Today and students to discuss the issue of database transactions, feel the discussion in some understanding, some knowledge has been seen, but no actual use or do not understand.

A transaction is a sequence of operations as an atomic operation, or all of which succeeds, and the state of the execution period is retained if execution fails. Actions are implemented through the commit and rollback mechanisms, and if all execution succeeds by committing the execution of the commit result, it is logged to the database, and if execution fails rollback all errors prior to the error will be eliminated and rolled back to its original state.

Transactions should have acid characteristics. The so-called acid is atomic (atomicity), consistent (consistency), Isolated (isolation), durable (persistence) four words written in the first letter, the following "bank transfer" as an example to explain their meaning:

Atomicity: The statement that makes up a transaction forms a logical unit and cannot execute only part of it. In other words, a transaction is an indivisible smallest unit. For example: In the process of bank transfer, you must deduct the transfer amount from one account at the same time and add it to another account, it is unreasonable to change only one account.

Consistency: The database is consistent before and after transaction execution. In other words, the transaction should correctly transform the system state. For example: In the bank transfer process, either transfer amount from one account to another account, or two accounts are unchanged, there is no other situation.

Isolation: One transaction has no effect on another transaction processing. This means that no transaction can see a transaction in an incomplete state. For example, in the course of bank transfers, another Giro transaction can only wait until the transfer transaction has been committed.

Persistence: The effect of transaction processing can be permanently preserved. Conversely, a transaction should be able to withstand all failures, including server, process, communication, and media failures, and so on. For example: In the process of bank transfer, transfer account the state of the household to be able to be preserved.

Note that MySQL supports the storage engine, the default is MyISAM, is not supported transactions, generally have InnoDB, is a support transaction type.

(1) If you need transaction support for a table to operate, you need to configure the storage engine to support transaction type such as InnoDB.

CREATE Table XX () Engine=innodb;

(2) By default, MySQL is the autocommit mode (autocommit=1), at this time will be done after the completion of each statement will be made to submit the changes immediately, at this time the commit is equivalent to useless, rollback only to the previous sentence function, in fact, also useless, A MySQL statement is an atomic operation by default, not necessary.

If you set the default transaction, you need to set the autocommit mode close to Autocommit to 0.

Set autocommit=0; Set mode to OFF

SELECT @ @autocommit; See if the value has changed

Note that if the client is set up, it is set, then the connection is disconnected and then the default settings are restored. Each client can only set the customer's own.

(3) If the autocommit mode is open, use the statement:

Start transaction; Start transaction processing

XX1;

XX2;

Commit /rollback;

To start a transaction, and if it is set to off, the continuous statement directs rollback or commits for the transaction without using the start transaction.

(4) Note that creating, changing, deleting a database or the data definition language and the lock-related are not part of the transaction, as follows:

?

1 2 3 4 5

Start transaction; INSERT into test1 values ("8"); CREATE TABLE test2 (i int); INSERT into test1 values ("8"); Rollback

HTML "name=" code > performs a transaction, and when executed to create a table, MySQL automatically commits and then executes the creation statement. If Test1 I is the primary key, then the third statement fails, test1 or insert succeeds when the rollback is made, and the table test2 is created.

(5) Use of the database in Python, preferably in this form,

?

1 2 3 4 5 6 7 8 9

Import MySQLdb try:conn = MySQLdb.connect (host= "localhost", user= "root", passwd= "Your passwd", db= "dbname") except Qldb.error,e:print "Mysql Error%d:%s"% (E.args[0], e.args[1]) Else:pass #conn. Close ()

?

1 2 3 4 5 6 7 8 9 10 11 12 13-14

Try:cur=conn.cursor () Cur.execute (' Set autocommit=0 ') #cur. Execute (' Start transaction ') Cur.execute (' Insert I      Nto test1 VALUES ("8") ') Cur.execute (' INSERT into test1 values ("8") ') except Mysqldb.error,e:conn.rollback () Print "Mysql Error%d:%s"% (E.args[0], e.args[1]) else:conn.commit () Cur.close () Conn.close ()

(6) Parallel processing problems

MySQL is a multi-user system, there are multiple users at the same time access to the unified data table, Mysiam using a data table-level locking tag, to ensure that only one user access to the table at the same time; InnoDB uses the data row level access mechanism, That is, two users can modify the data that is not in the same table, and if the same row, the first user to lock the row, the operation ends the release lock, the next user can operate.

(7) The problem of isolation of transaction processing

InnoDB The default isolation level is repeatable read, if a user executes the same SELECT statement two times, the result is repeatable, and if a user operates on the data to be read during the transaction, there is no display, such as a table with a storage engine of InnoDB , if one client uses a transaction to select Read table data, and another user does an insert on the table at that time, the first user will then make the same select read and the data display is unchanged.

(8) Multi-statement operation non-atomic operation

If there is a problem in (6) above, if it is a transaction operation, after reading the data, want to manipulate the data, but there may be another person to do this, then the operation of this data is not correct.

At this point, you need to explicitly lock the table, prevent others from changing the data, after execution, release the lock.

Lock tables XX write;

XXXXXX;

Unlock tables;

You can also use relative updates instead of absolute updates, which are updated relative to the current value, and are not updated with an absolute value based on last values. This avoids the non-atomic operation of multiple statements.

Set a = A-3 xxxxxxxxxxx;