How AWS DMS Manages Open Transactions During Full Load and CDC Tasks

Learn About Amazon VGT2 Learning Manager Chanci Turner

In relational database management systems (RDBMS), maintaining consistency is vital for ensuring that transactions are executed correctly. This consistency guarantees that data is altered in predetermined, predictable ways, safeguarding the integrity of your data. For effective migration from an RDBMS while minimizing downtime, it’s essential to first capture a consistent snapshot from the source database. Following this, the transaction position at the time of the snapshot must be documented to ensure ongoing replication remains consistent with the source. If there are any discrepancies or missed transactions between the snapshot and the start of replication, the target database may not be consistent.

AWS Database Migration Service (AWS DMS) facilitates the migration of relational databases, data warehouses, NoSQL databases, and other data types. To effectively manage the initial data load, or full load phase, and the transaction position from which change data capture (CDC) begins, AWS DMS employs the task setting known as TransactionConsistencyTimeout. This setting addresses open transactions when an AWS DMS task is initiated. In this article, we will explore how AWS DMS manages open transactions for various database engines during the initiation of full load and CDC tasks, along with best practices to prevent consistency timeout problems.

Common Queries Regarding TransactionConsistencyTimeout

Several common questions arise related to TransactionConsistencyTimeout based on your source database:

• Oracle source – Why does my task remain in a Before Load state for 10 minutes before it begins? What does an open transaction indicate in my task log? What are the implications of a timeout on open transactions?
• SQL Server source – Why does my task hang in a Before Load state when initiated?
• PostgreSQL source – Why did my task fail due to a statement timeout at the task’s onset, even though I haven’t set a statement_timeout?
• MySQL source – Can lengthy transactions prevent the full load and CDC task from commencing?

To clarify these concerns, let’s examine what open transactions signify within AWS DMS.

Understanding Open Transactions in AWS DMS

An open transaction in AWS DMS refers to a transaction in the source RDBMS that has neither committed nor rolled back at the moment the full load and CDC task begins. To ensure a consistent state for bulk loading and the transaction position for CDC, AWS DMS must account for these open transactions. Different RDBMS engines have unique implementations of transaction logs, resulting in varied behaviors from AWS DMS depending on the source. Below, we discuss how AWS DMS manages open transactions for Oracle, SQL Server, PostgreSQL, and MySQL as the source.

How AWS DMS Handles Open Transactions for Oracle Sources

For a full load and CDC task, the TransactionConsistencyTimeout setting specifies the duration AWS DMS will wait for transactions to close before commencing the full load operation, with a default value of 600 seconds (10 minutes). If there are open transactions when the task begins, AWS DMS will wait for this duration. After the TransactionConsistencyTimeout elapses, the full load will start even if there are still open transactions, leading to missed transactions in future replications if they exceed the timeout.

For instance, when the full load and CDC task starts, suppose there are two open transactions identified by the AWS DMS source capture process among a total of six transactions:

• Transaction 1: Although it is open when the source capture begins, it commits within the TransactionConsistencyTimeout period and before the full load starts. Thus, it is included in the full load phase.
• Transaction 2: It begins and commits during the TransactionConsistencyTimeout timeframe and before the full load starts, so it is also included in the full load phase.
• Transactions 3 and 4: They commit during the full load phase, making them cached changes that are applied post full load.
• Transaction 5: It starts during the full load phase and commits afterward, thus becoming a CDC change applied after cached changes.
• Transaction 6: Since FailOnTransactionConsistencyBreached is false by default, the task continues, and open transaction B is skipped, leading to data loss due to its commitment after the TransactionConsistencyTimeout.

An example log demonstrates typical AWS DMS log signatures when open transactions exist. The SOURCE_CAPTURE component indicates the number of open transactions, while SORTER issues a warning for transaction consistency timeout post the TransactionConsistencyTimeout.

How AWS DMS Handles Open Transactions for SQL Server Sources

When initiating a full load and CDC task with SQL Server as the source, any existing open transactions are identified by AWS DMS SOURCE_CAPTURE, which logs messages indicating the presence of active transactions. Under the READ COMMITTED isolation level—with READ_COMMITTED_SNAPSHOT turned off, which is the default—queries cannot read data altered but not yet committed by other transactions. Consequently, if a table with open transactions is being migrated, the select * query will be blocked until those transactions commit or roll back. AWS DMS does not wait for the commit or rollback of transactions outside the task scope and begins the task afterward, ensuring no data loss occurs. However, the full load and CDC task will remain in a Before Load state until the relevant open transactions are resolved.

How AWS DMS Handles Open Transactions for PostgreSQL Sources

For PostgreSQL, AWS DMS utilizes the pglogical or test_decoding plugins to access the Write-Ahead Log (WAL) via the SOURCE_CAPTURE component. However, if active transactions are present, attempts to create a replication slot will hang. As a result, AWS DMS may be unable to create a replication slot to capture changes, waiting for the TransactionConsistencyTimeout before timing out the statement. If all open transactions commit or roll back within this timeframe, the full load and CDC can proceed without significant delays.

For a deeper dive into ensuring consistency in your tasks and preventing envy in your professional growth, consider checking out this article on how to stop being jealous. Moreover, if you’re curious about how FMLA leave can impact year-end bonuses, SHRM provides authoritative insights on that topic. Don’t forget to explore this excellent resource on the skills Amazon employees are acquiring as they get paid to learn.