MySQL transaction rollback data rollback usage and problems

You can actually see the inserted record in the current transaction. At last it was only deleted. However, Auto_increment should not delete and change the value.

1, why auto_increament did not roll back?

Because the current value of the InnoDB's auto_increament counter record is stored in memory, not on disk, and when the MySQL server is running, the count will only grow with the insert change and will not decrease with the delete. And when MySQL server starts, when we need to query the auto_increment count value, MySQL will automatically execute: SELECT MAX (ID) from table name for UPDATE; statement to get current auto_ Increment the maximum value of the column, and then place the value in the Auto_increment counter. So even Rollback MySQL's auto_increament counter does not make negative calculations.

2, MySQL transactions on the table when the operation is a physical operation?

MySQL transactions are redo and undo, and all information about redo operations is recorded in Redo_log, which means that when a transaction commits, it is necessary to write the operation of the transaction to the Redo_log and then flush the operation to disk, when In the case of a failure, you only need to read the Redo_log and then flush to disk again.
And for Undo is more trouble, MySQL in the processing of transactions, will be in the Data sharing table space in the application of a paragraph called segment paragraph, with the Save undo information, when processing rollback, not completely physical undo, but the logical undo, which means that will be Operation, but these shared tablespaces are not recycled. The recovery of these tablespace needs to be reclaimed by the MySQL master thread process.

First, MySQL needs to determine the storage engine as InnoDB before using the transaction.

Transaction

The code is as follows	Copy Code
Start transaction Rollback Commit

modifying table Types

The code is as follows	Copy Code
ALTER TABLE ' goods ' ENGINE = InnoDB; Engine ALTER TABLE ' goods ' ENGINE = MYISAM Show CREATE TABLE Goods G

The following is the code for the stored procedure:

The code is as follows	Copy Code
BEGIN DECLARE t_error INTEGER DEFAULT 0; DECLARE CONTINUE HANDLER for SQLEXCEPTION SET t_error=1; #set autocommit = 0; START TRANSACTION; INSERT into ' mytest ' Test2 ' VALUES (1, ' 22′, ' 33′); INSERT into ' mytest '. ' Test2 ' VALUES (NULL, ' 22′, ' 33′); IF t_error = 1 THEN ROLLBACK; ELSE COMMIT; End IF; End

Where the red code is the key code to control rollback!

It's just mysql things rolled back, let me introduce an example of a combination with PHP

Example

code is as follows

copy code

$lnk = mysql_connect ("localhost", "root", ""); mysql_select_db ("test"); mysql_query ("BEGIN"); $query = mysql_query ("INSERT into Test VALUES (1, ' Yangjun ')"); $q 1 = mysql_error (); mysql_query ("INSERT into Test VALUES (1, ' Yangjun ')"); $q 2 = mysql_error (); mysql_query ("INSERT into Test VALUES (2, ' Yang ')"); $q 3 = mysql_error (); if (! $q 1 && $q 2 &&! $q 3) { mysql_query ("COMMIT"); All successful, submit execution results } else { mysql_query ("ROLLBACK"); Any errors occur, rollback, and cancel execution results } Var_dump ($q 1, $q 2, $q 3);

Example:

/

The code is as follows

Copy Code

** * Order Warehousing * 1, Generate date + random number order number, and ensure not repeat * 2, insert order Order_info table * 3, insert the order corresponding to the merchandise table Order_goods * 4, modify the product table, reduce inventory * 5, with transaction to ensure the consistency of the three tables */ /* Temporary need a function to generate the order * * function Create_sn () { $SN = Date (' Ymdhis '). Rand (100,999); $sql = "SELECT count (*) from order_info where order_sn = ' $sn '"; return $GLOBALS [' DB ']->getone ($sql) create_sn (): $SN; This number is reborn into a } Start---------Transaction---------------------- $db->query ("Start transaction"); This method can be used to insert $db->autoexecute (' table name ', $data, ' insert/update ', ' condition ') $order _sn = CREATE_SN (); $user _id = $_session[' user ' [' user_id ']; $add _time = time (); $goods _amount = $cart->getprice (); $sql = "INSERT into Order_info (order_sn,user_id,goods_amount,add_time) VALUES (' $order _sn ', ' $user _id ', ' $goods _amount ', ' $add _time ') "; $db->query ($sql); Execute INSERT Order SQL statement (Order_info table) /* Operation Order_goods Table * * $order _id = $db->insert_id (); Get the primary key that just inserted the table $cartlist = $cart->enumitems (); Get an array of shopping cart items foreach ($cartlist as $k => $v) { Identify the product SN and other information added to the shopping cart product array $sql = "Select Goods_id,goods_sn,goods_name,shop_price from goods where goods_id = ' $k '"; $cartlist [$k] = $cartlist [$k] + $db->getrow ($sql); } * * According to the goods in the cart, add all the items to be bought into the Order_goods table, and update the goods inventory. $sqls =array (); foreach ($cartlist as $k => $v) { $goods _id = $k; $goods _name = $v [' Goods_name ']; $goods _sn = $v [' goods_sn ']; $goods _number = $v [' num ']; $goods _price = $v [' shop_price '] * $v [' num ']; $SQLS [] = INSERT INTO order_goods (Order_id,goods_id,goods_name,goods_sn,goods_number,goods_price) VALUES (' $order _ Id ', ' $goods _id ', ' $goods _name ', ' $goods _sn ', ' $goods _number ', ' $goods _price '); $sqls [] = "UPDATE goods set goods_number = Goods_number-' $goods _number ' where goods_id = ' $goods _id '"; } Print_r ($SQLS); $res = true; foreach ($sqls as $sql) { $res = $res && $db->query ($sql); If there is an error in the SQL statement, the $res is not true } ---------if $res commits the transaction, the transaction is rolled back---------- if (! $res) { $db->query (' rollback '); Show_message (' order creation failed ', ' return to cart ', ' flow.php?act=cartinfo '); Exit (); } Else { $db->query (' commit '); $smarty->assign (' order_sn ', $order _sn); $smarty->assign (' Order_amount ', $cart->getprice ()); $cart->emptyitems (); $smarty->display (' my_gwc3.html '); } }

Attention:

Only InnoDB and BDB types of data tables in MySQL can support transaction processing! Other types are not supported!