MySQL, the default is auto-commit, that is , autocommit = 1; But in this case, there are problems in some situations: for example:If you want to insert 1000 data at a time,MySQL will commit1000, If we turn off the autocommit [autocommit = 0], through the program to control, as long as a commit on it, so that it can better reflect the characteristics of the transaction! for the need to manipulate the value, such as amount, number and so on! remember a principle: a lock two to three update If you want to update the same form after select, it is best to use Select ... For UPDATE For example, suppose that the product list contains a quantity of the number of items in the product, and before the order is established, it is necessary to determine whether the quantity is sufficient (quantity>0) .then the quantity is updated to 1. Unsafe Practices:SELECT quantity from Products WHERE id=3; UPDATE products SET quantity = 1 WHERE id=3;Why isn't it safe?A small number of situations may not be a problem, but a lot of data access "definitely" will be problematic. if we need to quantity>0 in the case of inventory, assuming that the program in the first select read to the quantity is 2, it seems that the number is not wrong, but when MySQL is ready to update, there may have been the inventory has been deducted 0, But the program was unaware that will wrong's update went on. Therefore, the transaction mechanism must be through to ensure that the data read and submitted are correct. so we can test this in MySQL (make sure the database table engine is InnoDB instead of MyISAM): SET autocommit=0; BEGIN work; SELECT User_tel from users WHERE user_id= ' a ' for UPDATE;================================================================================= id=60 data in the users data is locked and other transactions must wait for the transaction to be committed before it can be executed! also open a window test,1. SELECT * from users where user_id= ' "for UPDATE; Because the above transaction has not been submitted, the data of ID =60 has been locked, and the database will report the error if it is executed here ! [ERR] 1205-lock wait timeout exceeded; Try restarting transaction2. The same error will be returned if the update operation on the table is not successful in another window. 3.SELECT * from Userswhere id=60 will not be affected! SELECT * from the users where id=60 for UPDATE ensures that the numbers User_tel read in other transactions are correct. ================================================================================The update operation must be performed in the same reply to the Lock table statement to release the lockUPDATE users SET user_tels= ' 111212122 ' WHERE id=60; COMMIT work;==================================================================================The above describes the select ... The use of for UPDATE, but the lock data is a discriminant, you have to pay attention to. because one of the Innodb[mysql database engine] presets is row-level lock, only the "explicit" specified primary key , MySQL executes row lock (only the selected data is locked), Otherwise, MySQL will execute table lock (lock the entire data form) [seriously affecting efficiency]. As an example:Suppose there is a form of products with ID and name two fields, ID is the primary key. Example 1: (explicitly specifying a primary key and having this data, row lock)SELECT * FROM Products WHERE id= ' 3 ' for UPDATE;Example 2: (explicitly specify the primary key, if the data is not found, no lock)SELECT * FROM Products WHERE id= '-1 ' for UPDATE;Example 2: (No primary key, table lock)SELECT * FROM Products WHERE name= ' Mouse ' for UPDATE;Example 3: (primary key ambiguous, table lock) SELECT * FROM Products WHERE id<> ' 3 ' for UPDATE;Example 4: (primary key ambiguous, table lock)SELECT * from the products WHERE ID like ' 3 ' for UPDATE;Note 1:for UPDATE applies only to InnoDB and must be in the transaction block (Begin/commit) to take effect. NOTE 2: To test the condition of the lock, you can use the command Mode of MySQL and open two windows to do the test. You can compare the for update http://blog.csdn.net/winy_lm/article/details/48175885 in Oracle above
"Reprint" MySQL transaction and select ... Use for update
