Currently in the development of ERP system, the database used is MySQL. Here's how to turn on transactions, and the mechanism for transaction isolation:
1. Check the storage engine used by the current database.
Show engines;
2. Modify the files in the previous My.ini as follows:
3. Modify the My.ini file as follows.
3.1 Modified the default storage engine
3.2 Increased the transaction isolation level of the MySQL database, if not added by default is Repeatable-read.
4. Just restart the MySQL service.
net stop MySQL
net start MySQL
5. Check again for show engines, shown below
6. Description of the transaction isolation mechanism, which is referenced in the following articles from others
- Unread (Read UNCOMMITTED). Another transaction modifies the data but has not yet committed, and select in this transaction reads the uncommitted data (dirty read).
- Submit Read (COMMITTED). This transaction reads the most up-to-date data (after other transactions have been committed). The problem is that in the same transaction, two times the same select will read different results (do not repeat). such as two sessions A and B, because it is read committed, so only after the transaction is committed to be visible to other transactions, when a executes the query B performs the INSERT, B executes the commit transaction, then a again query results are really different, a of the two queries belong to a transaction, that is, non-repeatable read.
- REPEATABLE READ (Repeatable read). In the same transaction, the result of select is the state of the point in time at which the transaction started, so the same select operation will read the same result. It is not possible to see any changes to the locks made by other transactions during the execution of this transaction until the transaction is committed. In MySQL, the InnoDB and XTRADB storage engines solved the problem of phantom reading through multi-version concurrency control (MVCC).
- Serialization (SERIALIZABLE). A read operation implicitly acquires a shared lock, which guarantees mutual exclusion between different transactions.
- SQL Transaction ISOLATION LEVEL
The default transaction isolation level in MySQL is repeatable read (repeatable READ). SQL statements can be queried:
View the transaction isolation level at the InnoDB system level:mysql> SELECT @ @global. tx_isolation;The default transaction isolation level in MySQL is repeatable read (repeatable READ). SQL statements can be queried:To view the transaction isolation level at the INNODB system level:mysql> SELECT @ @global. tx_isolation; Results:+-----------------------+
| @ @global. tx_isolation |
+-----------------------+
| Repeatable-read |
+-----------------------+
1 row in Set (0.00 sec) to view the transaction isolation level at the InnoDB session level: mysql> SELECT @ @tx_isolation; Results:
+-----------------+
| @ @tx_isolation |
+-----------------+
| Repeatable-read |
+-----------------+
1 row in Set (0.00 sec) To modify the transaction isolation level:
mysql> set GLOBAL transaction ISOLATION level Read Committed;
Query OK, 0 rows Affected (0.00 sec)
mysql> Set Session transaction isolation level read Committed;
Query OK, 0 rows Affected (0.00 sec) InnoDB REPEATABLE Read isolation level and other database repeatable read is differentiated, does not cause Phantom Read (Phantom Read), so-called Phantom Read, is the same transaction, multiple Select, can be read to other session Insert and already commit the data. The following is a small test that proves that InnoDB's repeatable read isolation level does not cause phantom reads. The test involves two sessions, session 1 and Session 2 respectively, and the isolation level is repeateable read, close autocommit mysql> SELECT @ @tx_isolation;
+-----------------+
| @ @tx_isolation |
+-----------------+
| Repeatable-read |
+-----------------+
1 row in Set (0.00 sec)
mysql> set Autocommit=off;
Query OK, 0 rows Affected (0.00 sec) Session 1 Creating a table and inserting test data
mysql> CREATE TABLE test (i int) engine=innodb;Query OK, 0 rows Affected (0.00 sec)
mysql> INSERT INTO test values (1);
Query OK, 1 row Affected (0.00 sec) Session 2 Query, no data, normal, Session1 not submitted, dirty read not allowed
mysql> SELECT * from test;
Empty Set (0.00 sec) Session 1 COMMIT TRANSACTION
mysql> commit;
Query OK, 0 rows Affected (0.00 sec) Session 2 query, or no data, no phantom read generated
mysql> SELECT * from test;
Empty Set (0.00 sec)
when the Session2 commits the transaction, it is not possible to see the session1 insert data;
above test version:
mysql> Select version ();
+-------------------------+
| version () |
+-------------------------+
| 5.0.37-community-nt-log |
+-------------------------+
1 row in Set (0.00 sec)
Transaction management for MySQL database
