Why MySQL should avoid large transactions and how to solve them

What is a big deal?

Transactions that run for a long time and are not committed for a long time can be called large transactions.

The causes of big business

• There is more data to operate
• Lots of lock contention
• There are other non-DB time-consuming operations in the transaction
• . . .
  

The impact of big events

• In concurrent situations, the database connection pool is prone to bursting
• Locking too much data, causing a lot of blocking and lock timeouts
• Long execution time, which may cause master-slave delay
• Rollback takes a long time
• Undo log expansion
• . . .
  

How to query large transactions

**Note**: The SQL operations in this article are based on MySQL version 5.7

Take the query execution time of more than 10 seconds as an example:

select \* from information\_schema.innodb\_trx where TIME\_TO\_SEC(timediff(now(),trx\_started))>10

How to avoid big transactions

General solution

• Avoid processing too much data at once in a transaction
• In a transaction, try to avoid unnecessary queries
• In a transaction, avoid performing too many time-consuming operations that may cause transaction timeout. Try to put some non-DB operations, such as RPC calls and message queue operations, outside of transactions.
  

Solution based on mysql5.7

• In an InnoDB transaction, row locks are added only when needed, but they are not released immediately when they are no longer needed. Instead, they are released only when the transaction ends. **If you need to lock multiple rows in your transaction, put the locks that are most likely to cause lock conflicts and affect concurrency as far back as possible**
• Use the SETMAX_EXECUTION_TIME command to control the maximum query time for each statement to prevent a single statement from accidentally querying for too long.
• Monitor the information_schema.Innodb_trx table, set a long transaction threshold, and alarm/or kill when it exceeds
• During the business function testing phase, it is required to output all general_logs and analyze log behaviors to find problems in advance.
• Set the innodb_undo_tablespaces value to separate the undo log into a separate tablespace. If a large transaction causes the rollback segment to be too large, it will be easier to clean up after setting it up.
  

Appendix Query Transaction Related Statements

**Note**: SQL statements are based on MySQL 5.7 version

# Query all running transactions and their running time select t.\*,to\_seconds(now())-to\_seconds(t.trx\_started) idle\_time from INFORMATION\_SCHEMA.INNODB\_TRX t



# Query transaction details and executed SQL

select now(),(UNIX\_TIMESTAMP(now()) - UNIX\_TIMESTAMP(a.trx\_started)) diff\_sec,b.id,b.user,b.host,b.db,d.SQL\_TEXT from information\_schema.innodb\_trx a inner join information\_schema.PROCESSLIST b

on a.TRX\_MYSQL\_THREAD\_ID=b.id and b.command = 'Sleep'

inner join performance\_schema.threads c ON b.id = c.PROCESSLIST\_ID

inner join performance\_schema.events\_statements\_current d ON d.THREAD\_ID = c.THREAD\_ID;



# Query all historical SQL records executed by the transaction SELECT

 ps.id 'PROCESS ID',

 ps.USER,

 ps.HOST,

 esh.EVENT_ID,

 trx.trx_started,

 esh.event\_name 'EVENT NAME',

 esh.sql\_text 'SQL',

 ps.time 

FROM

 PERFORMANCE\_SCHEMA.events\_statements\_history esh

 JOIN PERFORMANCE\_SCHEMA.threads th ON esh.thread\_id = th.thread\_id

 JOIN information\_schema.PROCESSLIST ps ON ps.id = th.processlist\_id

 LEFT JOIN information\_schema.innodb\_trx trx ON trx.trx\_mysql\_thread\_id = ps.id 

WHERE

 trx.trx_id IS NOT NULL 

 AND ps.USER != 'SYSTEM\_USER' 

ORDER BY

 esh.EVENT_ID;

 

 # Simple query transaction lock select \* from sys.innodb\_lock\_waits

 

 #Query transaction lock detailsSELECT

 tmp.\*,

 c.SQL\_Text blocking\_sql\_text,

 p.HOST blocking\_host 

FROM

 (

 SELECT

 r.trx_state watching_trx_state,

 r.trx_id waiting_trx_id,

 r.trx\_mysql\_thread\_Id waiting\_thread,

 r.trx_query waiting_query,

 b.trx_state blocking_trx_state,

 b.trx_id blocking_trx_id,

 b.trx\_mysql\_thread\_id blocking\_thread,

 b.trx_query blocking_query 

 FROM

 information\_schema.innodb\_lock\_waits w

 INNER JOIN information\_schema.innodb\_trx b ON b.trx\_id = w.blocking\_trx\_id

 INNER JOIN information\_schema.innodb\_trx r ON r.trx\_id = w.requesting\_trx\_id 

 ) tmp,

 information\_schema.PROCESSLIST p,

 PERFORMANCE\_SCHEMA.events\_statements\_current c,

 PERFORMANCE\_SCHEMA.threads t 

WHERE

 tmp.blocking_thread = p.id 

 AND t.thread_id = c.THREAD_ID 

 AND t.PROCESSLIST_ID = p.id

The above is the details of how to avoid large transactions in MySQL and how to solve large transactions. For more information about MySQL large transactions, please pay attention to other related articles on 123WORDPRESS.COM!