SQL bit by bit 9-sql server transaction processing and built-in transactions in SSIs

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.
• Non-repeated 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. 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 need to wait for the unlock to read 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, no shared lock will be encountered in other transactions, the query results will get all 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.

Transaction Processing in SSIs

In SSIs, you can set transaction processing options in packages, containers (such as loop, foreach loop, and sequence), or a separate task. The transaction processing options have the following values:

• Required-if a transaction exists, add it. Otherwise, add a new one.
• Supported-if a transaction is added, it is not added. This is the default option +
• Notsupported-no transaction processing added

The built-in transaction processing must use the Distributed Transaction Coordinator (MSDTC) service, which must be enabled. MSDTC allows distributed transaction processing, such as processing SQL Server databases and Oracle databases simultaneously in a transaction. If this service is not enabled, the following error message is displayed.

Error: 0xc001401a at transaction: The SSIS runtime has failed to start the distributed transaction due to error 0x8004d01b "the Transaction Manager is not available. ". the DTC transaction failed to start. this coshould occur because the MSDTC Service is not running.

Note that the transaction isolation level of elements in SSIS is serializable, which affects the lock duration. The following example shows how to lock a table in a package.

1. Create a sequencecontainer named test initialization.
2. This sequencecontainer is mainly used to create a test environment, create a connection table tranqueue, tranqueuehistory, and add a record to the first table to simulate a transaction processing process. We only use this sequencecontainer to create a test environment. Therefore, set its transactionoption to notsupported and add three Execute SQL statements to this sequencecontainer. Their settings are as follows:

   1/* name */
   2 create tranqueue table
   3/* sqlstatement settings */
   4if not exists (select * From SYS. objects where object_id =
   5
   6object_id (N 'dbo. tranqueue ') and type in (N 'U '))
   7 begin
   8 execute ('create table DBO. tranqueue (Message nvarchar (256 ))')
   9end
   10/* name */
   11 populate tranqueue
   12/* sqlstatement settings */
   13 insert into DBO. tranqueue values ('test message' + convert
   14
   15 (nvarchar (23), getdate (), 121 ))
   16/* name */
   17 Create tranqueuehistory table
   18/* sqlstatement settings */
   19if not exists (select * From SYS. objects where object_id =
   20
   21object_id (N 'dbo. tranqueuehistory ') and type in (N 'U '))
   22 begin
   23 execute ('create table DBO. tranqueuehistory (Message nvarchar (256 ))')
   24end

3. Create the second sequencecontainer, name it process, and set the transactionoption attribute to supported to add transaction processing.
4. Add an execute SQL statement to this sequencecontainer and name it processtranqueue. Its sqlstatement is set to the following statement. This statement simulates transaction processing and deletes the first 10 data records in the tranqueue table. The output statement inserts the deleted data into the tranqueuehistory table, stops processing the simulation, and updates historical records.

   1DELETE TOP(10) dbo.TranQueue
   2OUTPUT DELETED.*
   3INTO dbo.TranQueueHistory
   4FROM dbo.TranQueue WITH (TABLOCKX)

5. Add an execute SQL statement named placeholder for breakpoint. This task does not perform any operations, just to set a breakpoint here and then stop here to let us have time to verify whether the table will be locked.
6. Right-click the control flow interface and add the variable v_simulatefailure. Its type is int32 and its value is 1.
7. Add an execute SQL statement named simulate failure. It is used to simulate errors and set sqlstatement to select 1/0. When pacakage is executed here, it will cause errors and roll back.
8. Right-click the line between placeholder for breakpoint and simulate failure, click Edit, set evaluation operation to expression and constraint, and set expression to @ [user: v_simulatefailure] = 1, keep others by default. In this way, when the value of the custom variable is 1, the execution will continue.
9. Run package. The result is 1. The package is terminated at the breakpoint.

Figure 1

10. open SQL Server Management studion, select the corresponding database, create a new query, execute the following statement, nolock option ignore lock, this statement query gets a record Message2011-04-10 14:22:31. 043, but this record is not submitted

1SELECT * FROM dbo.TranQueueHistory WITH (NOLOCK)

11. Execute the following statement

1SELECT * FROM dbo.TranQueue

The statement will be blocked here, and the statement will remain in the execution status and will not end. Because we use tablockx in the process tranqueue task, here we will wait for the task to roll back or submit. Or you can write it like this, and it will even block

1 DELETE TOP(10) dbo.TranQueue
2  INSERT INTO dbo.TranQueueHistory VALUES ('Test Message' + CONVERT(NVARCHAR(23), GETDATE(), 121))

12. Click "continue" or "debuge". The package execution fails and select * From DBO. tranqueuehistory is executed.

With (nolock); Because rollback is performed, no results will be obtained. Select * From DBO. tranqueue, either of which has a record.

SELECT * FROM dbo.TranQueueHistory WITH (NOLOCK)

Nolock prompts ignore lock, this statement query gets a record Message2011-04-10 14:22:31. 043, but this record is not submitted

13. Execute the following statement,

SELECT * FROM dbo.TranQueue

The SQL statement will be blocked here, and the statement will be executed all the time. Because we use tablockx in the process tranqueue task, here we will wait for the task to roll back or submit. Or you can write it like this.

Delete top (10) DBO. tranqueue; insert into DBO. tranqueuehistory values ('test message' + convert (nvarchar (23), getdate (), 121), which will block any

14. Click "continue" or "debuge". The package execution fails. Select * From DBO. tranqueuehistory with (nolock) is executed. If rollback is performed, no result is obtained. Execute select * From DBO. tranqueue, either of which has a record.

If the value of the variable User: v_simulatefailure is set to 0, the simulate failure task is not executed, and the rollback is not performed. The records in tranqueue are written to tranqueuehistory. Here is an interesting statement:

Delete top (10) DBO. tranqueue
Output deleted .*
Into DBO. tranqueuehistory
From DBO. tranqueue with (tablockx)

If some of the two tables have the same structure, you can use Delete sourcetable output Delete to import data from one table to another. */Delete. column1, delete. column2... into destinationtable from sourcetable, so that the data in the first table is "cut" to the second table.