MySQL transaction rollback data rollback usage and problems

Source: Internet
Author: User
Tags commit flush mysql in rollback


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!

Contact Us

The content source of this page is from Internet, which doesn't represent Alibaba Cloud's opinion; products and services mentioned on that page don't have any relationship with Alibaba Cloud. If the content of the page makes you feel confusing, please write us an email, we will handle the problem within 5 days after receiving your email.

If you find any instances of plagiarism from the community, please send an email to: info-contact@alibabacloud.com and provide relevant evidence. A staff member will contact you within 5 working days.

A Free Trial That Lets You Build Big!

Start building with 50+ products and up to 12 months usage for Elastic Compute Service

  • Sales Support

    1 on 1 presale consultation

  • After-Sales Support

    24/7 Technical Support 6 Free Tickets per Quarter Faster Response

  • Alibaba Cloud offers highly flexible support services tailored to meet your exact needs.