Operating transactions is a very common process, and whenever there are interdependencies on database modifications, transactions should be used to resolve them. Pay attention to the following points when operating transactions:

1.Try to use Exception to catch the exception of MYSQL.
    So the first step: To set the exception mode of PDO to PDO::ERRMODE_EXCEPTION, PDO::ATTR_ERRMODE, there are the following three values:
    PDO::ERRMODE_SILENT: Default mode, do not actively report errors, need Take the initiative to obtain error information in the form of $pdo->errorInfo().
    PDO::ERRMODE_WARNING: E_WARNING error is raised, and the error is actively reported.
    PDO::ERRMODE_EXCEPTION: exceptions are actively thrown, and error information needs to be output with try{}cath(){}.

We use the following when connecting to MYSQL:

$pdo = new pdo ( "mysql:host=localhost;dbname=dbname" , "user" , "****" , array ( PDO :: ATTR_ERRMODE , PDO :: ERRMODE_EXCEPTION ));

Write the above code in try catch to catch the exception.Note that PDO::ATTR_ERRMODE, PDO::ERRMODE_EXCEPTION must be written in new pdo. Otherwise, the mysql connection error information will not be captured. The class in catch is limited to PDOException.If there is a namespace, please add the top-level namespace in front, otherwise the exception will not be caught.

2.Turn off AUTOCOMMIT before the MYSQL transaction starts

$pdo -> setAttribute ( PDO :: ATTR_AUTOCOMMIT , 0 ); $rs = $pdo -> beginTransaction (); 

Note that beginTransaction has a return value.It is best to judge the return value when calling the transaction beginTransaction.If it returns false, then there is no need to roll back later.

3.Operation transaction and rollback

Remember to call the rollBack method before all returns or before ending the transaction.The rollBack method also has a return value.It is best to judge the return value and retry the rollback twice to prevent an exception from a rollback and cause the transaction to be locked..

4.Execute automatic submission

$pdo -> setAttribute ( PDO :: ATTR_AUTOCOMMIT , 1 ); 

This step is very critical.You must perform this step whether you execute commit or rollBack.The meaning of automatic submission is whether each SQL statement you execute directly executes MYSQL.The default MYSQL is automatically submitted, that is, if you execute a SQL statement, it Just change to the database.If MYSQL is not submitted automatically, all MYSQL statements you execute are stuck there waiting to be executed.

The most common phenomenon when the transaction is abnormal is that the front-end browser requests that involve locked tables or rows are waiting, and finally may report an error mysql innodb Lock wait timeout exceeded; try restarting transaction problem, at this time:

First: You can query the error message through SHOW ENGINE INNODB STATUS\G;.The amount of information is too large and difficult to troubleshoot.

Second: Use select * from information_schema.innodb_trx \G; to query the list of running tasks.

mysql> select * from information_schema.innodb_trx \G;
*************************** 1.row ******************** *******
                    trx_id: 1429E713A
                 trx_state: RUNNING
               trx_started: 2018-04-02 16:25:31
     trx_requested_lock_id: NULL
          trx_wait_started: NULL
                trx_weight: 0
       trx_mysql_thread_id: 23698460
                 trx_query: NULL
       trx_operation_state: NULL
         trx_tables_in_use: 0
         trx_tables_locked: 0
          trx_lock_structs: 0
     trx_lock_memory_bytes: 376
           trx_rows_locked: 0
         trx_rows_modified: 0
   trx_concurrency_tickets: 0
       trx_isolation_level: REPEATABLE READ
         trx_unique_checks: 1
    trx_foreign_key_checks: 1
trx_last_foreign_key_error: NULL
 trx_adaptive_hash_latched: 0
 trx_adaptive_hash_timeout: 10000
mysql> select trx_id,trx_started,trx_state,trx_mysql_thread_id,trx_rows_locked from information_schema.innodb_trx;
+-----------+---------------------+-----------+--- ------------------+-----------------+
| trx_id | trx_started | trx_state | trx_mysql_thread_id | trx_rows_locked |
+-----------+---------------------+-----------+--- ------------------+-----------------+
| 1429E713A | 2018-04-02 16:25:31 | RUNNING | 23698460 | 0 |
| 1429E7138 | 2018-04-02 16:25:31 | RUNNING | 23698458 | 0 |
| 1429E7136 | 2018-04-02 16:25:31 | RUNNING | 23697458 | 0 |
| 1429E6734 | 2018-04-02 16:14:39 | RUNNING | 23698477 | 1 |
+-----------+---------------------+-----------+--- ------------------+-----------------+
4 rows in set (0.00 sec)
#You can query processlist by trx_mysql_thread_id value to view the mysql connection that caused the exception
select * from information_schema.processlist where db='dbname' and id =23698477;

Third, you can check the mysql bin log: MYSQL log file: show variables like general_log_file;

mysql>  show variables like 'general_log_file';
+------------------+-------------------------------+
| Variable_name    | Value                         |
+------------------+-------------------------------+
| general_log_file | /opt/data/mysql/localhost.log |
+------------------+-------------------------------+
1 row in set (0.00 sec)

Find the log file, directly view the binlog file, there may be garbled characters.

/ opt / modules / mysql / bin / mysqlbinlog - base64 - output = DECODE - ROWS mysql - bin.000098

But the above command can only decode and display the existing log files, without distinguishing the databases inside.If the log files are large, stop cooking. The following command can specify the database start time:

/ opt / modules / mysql / bin / mysqlbinlog - start - datetime = '2018-04-02 10:25:00' - d baotoutiao mysql - bin.000098 

But the above commands are not good enough, the following one is the best. The function of simulating tail -f can be realized. And can specify the database, very easy to use.

export D=$(date +"%Y-%m-%d %H:%M:%S" --date="1 minutes ago"); watch "/opt/modules/mysql/bin/mysqlbinlog - start-datetime=\"$D\" -d dbname --base64-output=decode-rows -v mysql-bin.000098|tail -n 50"