What is a Transaction Teradata

A transaction is the control applied within a database to guarantee data integrity. It relies on and monitors each SQL operation for a successful completion. The philosophy of a transaction is that all work completes or no work completes. It is normally considered an all or nothing proposition.

A transaction can be a single step or a multi-step operation. In Teradata, a single step implies a single SQL statement. Therefore, all SQL statements (INSERT, UPDATE or DELETE) are, by default in Teradata mode, considered individual and complete maintenance transactions. This simply means that a changed data block has been rewritten on disk. The change might be to include a new row (INSERT), a row with at least one column value modified from its original content (UPDATE), or one less row (DELETE). Once the write of a block completes successfully, all other block writes must work correctly, or the previous writes need to be undone or rolled back. Remember, transactions should be all rows are changed, or no rows are changed.

For example, if all employees in a company are to receive an annual increase of 4%, it is not sufficient to give the raise to one person. Everyone is supposed to be treated the same. Therefore, if the UPDATE multiplies everyone's salary by 1.04 successfully, every salary in every row must reflect the increase. However, if the UPDATE fails before everyone receives the raise, the database must go back and reverse out the raise from all the rows already changed and written to disk. This reverse process is called a rollback.

Teradata uses table or row level locks and the Transient Journal to guarantee the all or nothing aspect of a transaction. First, any changed data row is locked for WRITE so no one can READ it until all writes are completed with the new data. Second, the Transient Journal captures a copy of the original row in case a rollback is needed. Then, multiple SQL statements can finish with the assurance that all or none of the changes complete.

As a real world example of a multi-statement request: what if a bank customer needs to transfer money from their savings account to cover a check that is already written? This change requires two SQL UPDATE statements. The first UPDATE subtracts the amount of the transfer from the row in the savings account table. The second UPDATE adds that same amount to the corresponding row in the checking account table.

It is important that both changes work successfully for the operation to be complete satisfactorily. Otherwise, if the subtraction from the savings account works and the addition to the checking account fails, there is a problem. Imagine how unhappy you would be when the monthly statement arrives. There is money missing from your savings account and the check bounced due to insufficient funds.

Likewise, it is important to end a transaction. Since a transaction must be all or nothing, two things need to occur during the life of a transaction. First, to prevent access by other users, all changed rows are locked. This means that no user except the one issuing the locks can get to the resource rows.

Second, all updated rows have a copy of the original row (before image) stored in the Transient Journal. The Transient Journal stores these rows in the Permanent space of the DBC user (Data Dictionary). Once a transaction ends successful, the work is committed, all locks are released and the before images are deleted from the Transient Journal. However, if the transaction fails, all before images in the Transient Journal are put back into the table(s) to undo the effect of the changes made to these rows by the transaction. At that point, the locks can be released. This undo operation is called a rollback. It can take as long or longer to rollback the work as it did to do the work initially.

All rights reserved © 2018 Wisdom IT Services India Pvt. Ltd DMCA.com Protection Status

Teradata Topics