Mysql transaction processing problems

Today, I have discussed the problem of Database Transaction Processing with my colleagues. I feel that the discussion is clear and I have read some knowledge, but I still don't understand it if I haven't actually used it. Transaction processing is to treat a series of operations as an atomic operation, or all the operations are successfully executed. If the execution fails, the execution period is retained. You can perform operations by submitting and rolling back the machine.

Today, I have discussed the problem of Database Transaction Processing with my colleagues. I feel that the discussion is clear and I have read some knowledge, but I still don't understand it if I haven't actually used it. Transaction processing is to treat a series of operations as an atomic operation, or all the operations are successfully executed. If the execution fails, the execution period is retained. You can perform operations by submitting and rolling back the machine.

Today, I have discussed the problem of Database Transaction Processing with my colleagues. I feel that the discussion is clear and I have read some knowledge, but I still don't understand it if I haven't actually used it.

Transaction processing is to treat a series of operations as an atomic operation, or all the operations are successfully executed. If the execution fails, the execution period is retained. Operations are performed by submitting and rolling back the machine. If all the operations are successfully performed, the results of the committed operations are recorded in the database, if the execution fails, roll back all the errors before the error occurs and roll back to the original status.

Transactions should all have ACID features. ACID is the first letter of four words: Atomic (Atomicity), Consistent (consistency), Isolated (isolation), and Durable (continuity, the following uses bank transfers as an example to describe their meanings:

Atomicity: the statements that make up transaction processing form a logical unit and cannot only execute a part of it. In other words, transactions are the smallest units that are inseparable. For example, in the bank transfer process, it is unreasonable to change only one account by subtracting the transfer amount from one account and adding it to another account.

Consistency: the database is consistent before and after transaction processing. That is to say, the transaction should correctly switch the system status. For example, in the bank transfer process, either the transfer amount is transferred from one account to another, or both accounts remain unchanged.

Isolation: one transaction has no impact on the processing of another transaction. That is to say, no transaction can see a transaction in an incomplete state. For example, in the bank transfer process, another transfer transaction can only be in the waiting state before the transfer transaction is committed.

Continuity: the effect of transaction processing can be permanently saved. Conversely, transactions should be able to withstand all failures, including server, process, communication, and media failures. For example, in the bank transfer process, the account status must be saved after the transfer.

Note: In the storage engine supported by Mysql, the default value is MyISAM, which does not support transaction processing. Generally, InnoDB is available and supports transaction type.

(1) If transaction support is required for operations on a table, you need to configure the storage engine to InnoDB and other support transaction-type.

Create table XX () engine = InnoDB;

(2) by default, mysql uses the autocommit mode (autocommit = 1). After each statement is executed, the modifications are submitted immediately. At this time, the commit mode is useless, rollback only applies to the previous statement. In fact, it is useless. A mysql statement is also an atomic operation by default and is unnecessary.

If you set the default transaction processing, you need to disable the automatic commit mode to set autocommit to 0.

Set autocommit = 0; set mode to Disabled

Select @ autocommit; check whether the value has changed

Note: If the settings are completed on the client, disconnect the connection and re-connect again. Each client can only set its own.

(3) If the automatic submission mode is enabled, use the following statement:

Start transaction; start transaction Processing

XX1;

XX2;

Commit;/rollback;

To start transaction processing. If it is set to disabled, you do not need to use start transaction, and the continuous statement guides the transaction to rollback or commit.

(4) Note that creating, changing, or deleting a database, or the Data Definition Language and lock related to the database cannot be part of the transaction, as shown below:

?

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 "> execute a transaction. When a table is to be created, mysql automatically submits the statement and then runs the statement. If I of test1 is the primary key, the third statement fails. When the rollback is performed, test1 is still inserted successfully and the table test2.

(5) It is best to use this form to use databases in python,

?

1

2

3

4

5

6

7

8

9

Import MySQLdb

Try:

Conn = MySQLdb. connect (host = "localhost", user = "root", passwd = "your passwd", db = "dbName ")

Counter t MySQLdb. 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.exe cute ('set autocommit = 0') Export cur.exe cute ('start transaction ')

Cur.exe cute ('insert into test1 values ("8 ")')

Cur.exe cute ('insert into test1 values ("8 ")')

Counter t 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

Mysql is a multi-user system with multiple users accessing a unified data table at the same time. MySIAM uses a data table-Level Lock mark to ensure that only one user accesses the table at the same time; innodb uses a row-level data access mechanism, that is, two users can modify the data of different rows in the same table at the same time. If the data is the same row, the first user locks the row first, the next user can release the lock after the operation ends.

(7) Isolation of Transaction Processing

The default isolation level of InnoDB is repeatable read. If a user executes the same select statement twice, the results can be repeated, if a user performs operations on the data to be read during the transaction, the data will not be displayed, for example, a table with the storage engine of innodb, if one customer uses a transaction to read table data in the select statement, another user performs an insert operation on the table at this time. When the first user reads the same select statement, the data displayed is unchanged.

(8) Multi-statement operations non-atomic operations

For example, there will be a problem in the above (6). If it is a transaction operation, you want to operate the data after reading the data, but another person may have done this operation, then the operation on the data is incorrect.

In this case, you need to explicitly lock the table to prevent others from changing the data and release the lock after the execution ends.

Lock tables XX write;

XXXXXX;

Unlock tables;

You can also use relative update instead of absolute update to update relative to the current value, instead of calculating an absolute value based on the previous value. This avoids non-atomic operations on multiple statements.

Set a = a-3 XXXXXXXXXXX; too many characters