MySQL transaction processing with lock table

Transaction processing of a database guarantees the correctness of a set of processing results. Only data tables for INNODB and BDB engines in MySQL support transactions, and for MyISAM engine databases that do not support transactions, the same functionality can be achieved using table locking methods.

There are two main ways to implement MySQL's transaction processing.

1, with Begin,rollback,commit to achieve.

Begin a transaction

ROLLBACK TRANSACTION Rollback

Commit TRANSACTION Acknowledgement

PHP code

$conn = mysql_connect (' localhost ', ' root ', ' root ') or Die ("Data connection error!!!");

mysql_select_db (' Test ', $conn);

mysql_query ("Set names ' UTF8 '");

Start a transaction

mysql_query ("BEGIN"); or mysql_query ("START TRANSACTION");

$sql = "INSERT into ' t_user ' (' id ', ' username ', ' sex ') VALUES (NULL, ' test1 ', ' 0 ')";

$sql 2 = "INSERT into ' t_user ' (' Do ', ' username ', ' sex ') VALUES (NULL, ' test1 ', ' 0 ')";//This one was deliberately written wrong.

$res = mysql_query ($sql);

$res 1 = mysql_query ($sql 2);

if ($res && $res 1) {

mysql_query ("COMMIT");

Echo ' submitted successfully. ‘;

}else{

mysql_query ("ROLLBACK");

echo ' data rollback. ‘;

}

mysql_query ("END");

2, directly with set to change the MySQL automatic submission mode

MySQL is automatically submitted by default, that is, you submit a query, it is executed directly! We can pass

Set autocommit=0 prohibit auto-commit

Set autocommit=1 turn on auto-commit

PHP code

$conn = mysql_connect (' localhost ', ' root ', ' root ') or Die ("Data connection error!!!");

mysql_select_db (' Test ', $conn);

mysql_query ("Set names ' UTF8 '");

mysql_query ("SET autocommit=0"); Set MySQL not to submit automatically, you need to commit the commit statement by itself

$sql = "INSERT into ' t_user ' (' id ', ' username ', ' sex ') VALUES (NULL, ' test1 ', ' 0 ')";

$sql 2 = "INSERT into ' t_user ' (' Do ', ' username ', ' sex ') VALUES (NULL, ' test1 ', ' 0 ')";//This one was deliberately written wrong

$res = mysql_query ($sql);

$res 1 = mysql_query ($sql 2);

if ($res && $res 1) {

mysql_query ("COMMIT");

Echo ' submitted successfully. ‘;

}else{

mysql_query ("ROLLBACK");

echo ' data rollback. ‘;

}

mysql_query ("END"); Don't forget mysql_query ("SET autocommit=1") when the transaction is finished; autocommit

For MyISAM engine databases that do not support transactions, you can use table locking methods to implement

MyISAM & InnoDB All support, lock tables can lock the table for the current thread. If the table is locked by another thread, it will clog until all locks can be obtained. UNLOCK tables can release any locks held by the current thread. When a thread publishes another lock tables, or when the connection to the server is closed, all tables locked by the current thread are implicitly unlocked.

PHP code

mysql_query ("Lock TABLES ' T_user ' WRITE");//Lock ' user ' table

$sql = "INSERT into ' t_user ' (' id ', ' username ', ' sex ') VALUES (NULL, ' test1 ', ' 0 ')";

$res = mysql_query ($sql);

if ($res) {

Echo ' submitted successfully.! ';

}else{

echo ' Failure! ';

}

mysql_query ("UNLOCK TABLES");//Unlocked

If the action table is more, when debugging with breakpoints, before the transaction is complete (COMMIT) view the database related table is not see the data in the table changes.

The following is an example of using transaction processing in a MySQL stored procedure.

Java code

CREATE PROCEDURE transtest (in P1 varchar), p2 varchar (50))

BEGIN

DECLARE err int default 0;

/* If a SQL exception occurs, set ERR to 1 and continue with the following action */

Declare continue handler for SqlException set err=1; --Error handling

Set autocommit = 0;

Insert into Sy_queryconfig (syq_id) VALUES (p1);

Insert into Sy_queryconfig (syq_id) values (p2);

If Err=1 Then

ROLLBACK;

ELSE

COMMIT;

End If;

END

PS: Attach a MySQL database when delete table data, cannot use alias operation, for example:

Delete from T_user the where ID in (for each);//This is spelled correctly by the delete from T_user T where t.id in (;

MySQL is so capricious!

Technology sharing: www.kaige123.com

MySQL transaction processing with lock table