Rolling back segment bloat caused by big transactions: a nightmare of database performance and how to escape

Many developers have experienced this pain: database performance suddenly drops, query slows down, and even goes down directly. The culprit is often those huge affairs, which burst the rollback segment, making the database breathless. In this article, let’s discuss this issue in depth and see how to solve this headache-increasing “expansion”.

The purpose of the article is to help you understand the root causes of rollback segment swelling due to large transactions and provide some effective solutions. After reading, you will be able to manage database transactions more effectively, avoid performance bottlenecks, and improve the stability and reliability of the database.

Start with the basics

The rollback segment is where the database uses to store transaction rollback information. When a transaction fails and needs to be rolled back, the database will restore the database to its state before the transaction is executed based on the information in the rollback segment. Imagine a super-large transaction that modifies thousands of records. If this transaction fails, the rollback segment needs to store all these modified information, which can be imagined. If the rollback segment space is insufficient, the database will be in trouble. It's like a bucket where water flow (transactions) keep pouring in, but the bucket (rollback segment) is too small and eventually water overflows (database crash).

Oracle databases, as well as many relational databases, usually use UNDO tablespaces to manage rollback segments. The size of UNDO tablespace and the configuration of the database directly affect the database's ability to handle large transactions. Don't forget that UNDO tablespace management strategies, such as automatic scaling mechanism, will also affect overall performance. Improper configuration may lead to frequent tablespace expansion, which is itself a performance killer.

Core issue: The nature and harm of big affairs

The harm of big affairs is not just rollback segment expansion. Holding locks for a long time will affect concurrent performance and is also a serious problem. Imagine that a big transaction takes up resources for a long time and other transactions can only be waited. Can this be efficient? Therefore, solving large transaction problems is not only to solve the expansion of the rollback segment, but also the key to improving the overall database performance.

Code example (taking Oracle as an example, for reference only, the actual situation needs to be adjusted according to the specific database)

Suppose we have a large batch update operation:

 <code class="sql">-- 錯誤示范：一個巨大的事務<br>BEGIN<br> FOR i IN 1..100000 LOOP</code><pre class='brush:php;toolbar:false;'> UPDATE my_table SET column1 = i WHERE id = i;
COMMIT; -- Error: Frequent submissions, increasing overhead

END LOOP;
END;
/

The problem with this code is that it handles a lot of update operations in a transaction. Worse, it is constantly committing in a loop, which is actually inefficient.

Improvement plan: Split transactions

 <code class="sql">-- 正確示范：拆分事務<br>DECLARE<br> v_batch_size CONSTANT NUMBER := 1000; -- 批處理大小<br>BEGIN<br> FOR i IN 1..100000 LOOP</code><pre class='brush:php;toolbar:false;'> IF MOD(i, v_batch_size) = 0 OR i = 100000 THEN
  COMMIT;
END IF;
UPDATE my_table SET column1 = i WHERE id = i;

END LOOP;
COMMIT;
END;
/

This improved version splits large transactions into multiple small transactions, each transaction handling a certain number of update operations. This significantly reduces the pressure on the rollback segment and also improves concurrency performance. It is crucial to choose the right batch size ( v_batch_size ) which requires testing and adjustments based on actual conditions.

More advanced tips: Use the batch processing function of databases

Many database systems provide batch processing functions, such as Oracle's FORALL statements. Use these features to process large batches of data more efficiently, further reducing transaction size and rollback segment pressure.

FAQs and Solutions

• Alarms with insufficient space for rollback segments: This means that your rollback segments are not enough. It is necessary to increase the size of UNDO tablespace or optimize the transaction processing logic.
• Transaction timeout: This is usually because the transaction is executed for too long. Transactions need to be split or SQL statements optimized.
• Deadlock: This is usually because multiple transactions are waiting for each other to release the lock. Lock conflicts need to be analyzed and database design or transaction processing logic is optimized.

Performance optimization and best practices

• Reasonably set the size of UNDO tablespace: make reasonable plans based on database load and transaction characteristics.
• Use the appropriate database connection pool: Reduce the overhead of connection creation and destruction.
• Optimize SQL statements: Use indexes to reduce the number of data scans.
• Use the batch processing functions provided by the database: improve data processing efficiency.
• Regularly monitor database performance: timely discover and resolve potential problems.

Remember, solving the problem of rollback segment expansion is a system project that requires starting from multiple aspects such as database configuration, transaction processing logic, and SQL statement optimization. There is no one-time solution. Only continuous monitoring and optimization can ensure the stability and high performance of the database. This requires accumulation of experience and a deep understanding of the underlying mechanism of the database. Don't forget to carefully analyze your business scenario and choose the solution that suits you best.

The above is the detailed content of Solution to the rollback segment inflation problem caused by large transactions. For more information, please follow other related articles on the PHP Chinese website!