Understanding Large Transactions, Risks, and Mitigation Strategies in Databases

Definition A "large transaction" refers to a database transaction that runs for a long time and processes a large amount of data, such as executions exceeding 5 seconds, 10 seconds, or even a minute.

Risks of Large Transactions Locking too many rows can cause extensive blocking and lock time‑outs, lead to long rollback periods, create replication lag, and cause undo‑log bloat.

How to Avoid Large Transactions Avoid handling excessive data in a single transaction, move unnecessary SELECT statements out of the transaction, and limit each operation to fewer than 5,000 rows and result sets smaller than 2 MB (as per the author’s company policy).

Example The following MySQL statements illustrate a large transaction scenario:

CREATE TABLE `apple_test` ( `id` int(11) NOT NULL AUTO_INCREMENT, `a` int(11) NOT NULL DEFAULT '0' COMMENT 'a', `b` int(11) NOT NULL DEFAULT '0' COMMENT 'b', `updated_ts` timestamp(6) NOT NULL DEFAULT '0000-00-00 00:00:00.000000' ON UPDATE CURRENT_TIMESTAMP(6), `created_ts` timestamp(6) NOT NULL DEFAULT CURRENT_TIMESTAMP(6) COMMENT '创建时间', PRIMARY KEY (`id`) ) ENGINE=InnoDB DEFAULT CHARSET=utf8;

INSERT INTO apple_test(`a`, `b`) VALUES(1,1);

INSERT INTO apple_test(a, b) SELECT a,b FROM apple_test;

Repeated execution of the INSERT‑SELECT statement rapidly increases the table size; when the row count reaches 8,388,608, a simple SELECT COUNT(*) FROM apple_test; shows the large volume, and subsequent inserts take dozens of seconds to minutes, demonstrating the impact of a large transaction.

Problems Caused by Large Tables A "large table" is defined as a single table with over ten million rows or a file size exceeding 10 GB. Such tables lead to slow queries, low selectivity, heavy disk I/O, long index‑building times, prolonged schema‑change locks, and overall degradation of normal data operations. Mitigation strategies include sharding (splitting a table into multiple smaller tables), archiving historical data, and careful selection of partition keys.

Database Server Considerations During High‑Traffic Events Typical architectures involve master‑slave replication, read/write separation, and clustering. Key performance metrics include TPS (transactions per second), QPS (queries per second), concurrency, and connection count. High concurrency and many idle connections indicate a busy CPU, while excessive disk I/O can degrade performance. It is advisable not to perform backups on the primary server to avoid additional disk‑read pressure.

Call to Action If you found this article helpful, please like, view, and share it; your support encourages the author to continue creating quality content.