SQL bit by bit Transaction Processing

We can include the entire package in SSIS in a transaction. But what if a table needs to be locked during package execution? The built-in transaction processing of SSIS can solve this problem. Before that, familiarize yourself with the concept of transactions in SQL Server.

Transactions

Transactions in SQL Server are a single unit of work. If a transaction succeeds, all data modifications made in the transaction will be committed and become a permanent component of the database. If the transaction encounters an error and must be canceled or rolled back, all data modifications are cleared.

Using transactions in SQL Server may cause unexpected results. Specifically, there are three results: Dirty read, non-repeated read, and phantom read.

Dirty read:Dirty read means that when a transaction is accessing the database and modifying the data, the modification has not been committed to the database, and another transaction is also accessing the data, then the data is used.

Repeatable read:Read the same data multiple times in a transaction. At the end of the transaction, another transaction is also accessing the same data. Therefore, due to the modification of the second transaction, the data read by the first transaction may be different. In this way, the data read twice in a transaction is different, so it is called non-repeated read.

Phantom read:Phantom read refers to a phenomenon that occurs when a transaction is not executed independently. For example, the first transaction modifies the data in a table, which is designed to all the data rows in the table. At the same time, the second transaction also modifies the data in this table. When this modification is performed, a new row of data is inserted into the same table, so that the user of the first transaction finds that there is still one data record that has not been modified, like an illusion, it is called phantom read.

Specify an isolation level for the transaction in SQL Server, which defines the level at which the transaction is isolated from other transactions for resource or data changes. The transaction isolation level determines whether to lock SQL Server objects. The following is the transaction isolation level in SQL Server.

Rdad Uncommitted:You do not need to wait for unlocking to read data. In this way, dirty data is read, because the data to be read may not be updated yet. The lowest isolation level will lead to dirty reads, non-repeated reads, and Phantom reads, with the highest concurrency.
 
Read Committed:To read data, you must wait for the unlock to obtain the latest data. Read Committed will not cause dirty Read, but will cause non-repeated and phantom Read problems. Read Committed is the default setting of SQL Server.

Repeatable Read:Similar to Read Committed, it locks all rows Read, but no other connections can update or insert data, in this way, if the select statement may select the new and new data, this data record will not appear in the select result. The selected data cannot be changed by other connections until the read operation is completed or the rollback is completed. This isolation level will not cause dirty reads and non-repeated reads, but will cause Phantom reads.

Serializable:Similar to Repeatable Read, but no other connections can insert or update data. If you use this transaction isolation level in the next query, you will get the same query result, that is to say, the updated or newly inserted data will not appear in the query results. This isolation level does not cause dirty reads and cannot be repeated or Phantom reads.

There are two new transaction isolation levels added in SQL Server 2005.

A variation at the Read Committed level. When you set the database isolation level to READ_COMMITTED_SNAPHOT, any transaction at the Read Committed level no longer needs to lock the data object. When the statement is executed, all the latest results will be obtained when the select statement starts to be executed.

SNAPSHOT:A new level of SNAPSHOT. When you set the transaction isolation level to ALLOW_SNAPSHOT_ISOLATION in any database object, other transactions will not encounter a shared lock, and the query results will get all the updated rows. This isolation level does not cause dirty reads, and the results of read and Phantom reads cannot be updated.
All the above transactions are automatically processed in a data object similar to a version database in the tempdb database. When an update is not submitted, the Data Engine will retrieve this version library for appropriate submission results. SQL Server automatically maintains the database version without human intervention.