SQL drip 9-sql transactional processing in server and built-in transactions in SSIS

Source: SQL Drip 9-sql transactions in server and built-in transactions in SSIS

We can include the entire package in SSIS in a single transaction, but what if a table needs to be locked in the process of the package execution? SSIS built-in transactions can solve this problem. Before you begin, familiarize yourself with the concepts of transactions in SQL Server.

Transaction

A transaction in SQL Server is a single unit of work. If a transaction succeeds, all data modifications made in the transaction are committed and become a permanent part 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 can result in unexpected results, specifically dirty reads, non-repeatable reads, and three of Phantom reads.

• Dirty Read : Dirty read refers to when a transaction is accessing the database, and the data is modified, and this modification has not been committed to the database, another transaction is accessing the data, and then used this data.
• non-repeatable reads : Reads the same data multiple times within a single thing. When this transaction is not finished, another thing is accessing the same data, and the first transaction two reads from the first transaction two reads, because of the modification of the second transaction, the data may be different. This occurs when the data that is read two times within a transaction is different, so it is called non-repeatable read.
• Phantom reading : a phenomenon that occurs when a transaction is not executed independently, such as when the first transaction modifies data in a table, which is designed to all rows of data in the table. The second transaction also modifies the data in the table, which inserts a new row of data into the same table, so that the user of the first transaction finds that there is no modification to the data, like a hallucination, so called a phantom read

In SQL Server, specify an isolation level for the transaction that defines the level at which the transaction is isolated from other transactions for resource or data changes. The transaction isolation level determines whether the SQL Server object is locked, and the following is the transaction isolation level in SQL Server.

• Rdad UNCOMMITTED: Read data does not need to wait to unlock, this way will read dirty data, because the data read may not have been updated data. This isolation level is the lowest, resulting in dirty reads, non-repeatable reads, and Phantom reads, with the highest concurrency.
• Read Committed: Reading data requires waiting to be unlocked, which will read the latest updated data. Read Committed does not cause dirty reads, but can cause non-repeatable and phantom-readable problems. Read committed is the default setting for SQL Server.
• Repeatable Read: Similar to read Committed, it locks all rows that are read, but no other connection can update or insert data, so that if the SELECT statement may select the new or inserted data, This data record does not appear in the select result. The data selected at the same time cannot be changed by other connections until the execution of the read action ends or the rollback is complete. This isolation level does not cause dirty reads and non-repeatable reads, but can cause phantom reads.
• Serializable: Similar to repeatable read, but no other connection can insert or update data, and if you use this transaction isolation level in the next query, you will get the same query results. This means that the updated or newly inserted data will not appear in the query results. This isolation level does not cause dirty reads, non-repeatable reads, or phantom reads.

There are two other types of transaction isolation levels that are newly added in SQL Server 2005

• A read COMMITTED level mutation, when you set the isolation level of the database to Read_committed_snaphot, any transactions that use the read COMMITTED level no longer require locking the data object. When the statement is executed, it gets all the latest results when the SELECT statement starts executing.
• SNAPSHOT: A new level of SNAPSHOT, when you set the isolation level of a transaction in any database object to Allow_snapshot_isolation, no other transaction encounters a shared lock, and the query results get all the updated rows. This isolation level does not cause dirty reads, non-updatable read and Phantom read results

All of the above transactions are performed automatically in a repository-like data object in the tempdb database, and when an update is not committed, the data engine retrieves the repository to get the appropriate submission results. The work of maintaining this repository is automated by SQL Server and does not require human intervention.

Transaction processing in SSIS

The transaction options can be set in a package in SSIS, a container (for example, Loop,foreach loop,sequence), or a separate task. The transaction options have some of the following values

• required-If there is a transaction, add it or add a new one
• supported-If there is a transaction to add one, no is not added, this is the default option +
• notsupported-do not add transaction processing

Built-in transactions to use the Distributed Transaction Coordinator (MSDTC) service, this service must be turned on. MSDTC allows the use of distributed transactions, such as simultaneous processing of SQL Server databases and Oracle databases in a single transaction. If this service is not turned on, you will get the following error message.

error:0xc001401a at Transaction:the SSIS Runtime have failed to start the distributed Transaction due to error 0x8004d01b "The Transaction Manager is not available." The DTC transaction failed to start. This could occur because, the MSDTC Service is not running.

Note the transaction isolation level for elements in the package in SSIS is serializable, which affects the duration of the lock. Let's use an example to show how to lock a table in the package

2. Create a new Sequencecontainer, named Test initialization.
3. This sequencecontainer is primarily used to create a test environment, create a single table Tranqueue,tranqueuehistory, add a record to the first table, and simulate a thing-handling process. We just use this sequencecontainer to create a test environment, so setting its transactionoption option for NotSupported adds three execute SQL in this sequencecontainer, Their settings in turn are as follows

   1/*naming*/                
   2CreateTranqueueTable    
   3/*sqlstatement Settings*/    
   4IF  not EXISTS ( SELECT *  fromsys.objectsWHERE object_id =
   5
   6object_id(N'dbo. Tranqueue')  andtypeinch(N'U') )
   7BEGIN
   8Execute('CREATE TABLE dbo. Tranqueue (Message nvarchar ())')
   9END
   Ten/*naming*/
    OnePopulate Tranqueue
    A/*sqlstatement Settings*/
    -INSERT  intodbo. TranqueueVALUES ('Test Message' + CONVERT
    -
    the(NVARCHAR( at), GETDATE(), 121))
    -/*naming*/
    -CreatetranqueuehistoryTable    
    -/*sqlstatement Settings*/
    +IF  not EXISTS ( SELECT *  fromsys.objectsWHERE object_id =
    -
    +object_id(N'dbo. Tranqueuehistory')  andtypeinch(N'U') )
    ABEGIN
    atExecute('CREATE TABLE dbo. Tranqueuehistory (Message nvarchar ())')
    -END

5. Create a second sequencecontainer with the name Process,transactionoption property set to supported so that the transaction is added.
6. In this sequencecontainer, add an Execute SQL named Processtranqueue, whose sqlstatement is set to the following statement. The purpose of this statement is to simulate transactions, delete the first 10 data in the Tranqueue table, insert the deleted data into the Tranqueuehistory table, simulate the processing end, update the history

   1 DELETE  TOP(ten) dbo. Tranqueue
   2 OUTPUT DELETED. *
   3  into dbo. Tranqueuehistory
   4  from   with (TABLOCKX)

8. Add an Execute SQL named placeholder for breakpoint. This task does nothing, just to set a breakpoint here and stop here so that we have time to verify that the table is locked.
10. Right-click the control flow interface to add a variable v_simulatefailure, type Int32, with a value of 1.
12. Add an Execute SQL named simulate Failure. Use it to simulate errors, set SQLStatement to select 1/0, and cause errors to be rolled back when Pacakage executes here.
14. Right-click the connection between placeholder for breakpoint and simulate failure, click Edit, set evaluation operation for expression and Constraint, Set expression to @[user::v_simulatefailure] = = 1, others remain default. This will not continue until the value of this custom variable is 1.
16. The package will be executed with 1 results and the package terminates at the breakpoint.

Figure 1

10. Open SQL Server Management studion, select the corresponding database, create a new query, execute the following statement, nolock the option to ignore the lock, the statement query to get a record message2011-04-10 14:22:31.043, but this record hasn't been submitted.

1 SELECT   * from with (NOLOCK )

11. Execute the following statement

1 SELECT  * from dbo. Tranqueue

Statement will be blocked here, the statement remains in the execution state and will not end. Because we use TABLOCKX in the Process tranqueue task, here we wait for the task to roll back or commit. Or it can be written like this, it still blocks

1 DELETE TOP(Ten) dbo. Tranqueue
2  INSERT  intodbo. TranqueuehistoryVALUES ('Test Message' + CONVERT(NVARCHAR( at), GETDATE(), 121))

12. Click the Continue button or the Debuge button to see that the package failed to execute the SELECT * FROM dbo. Tranqueuehistory

With (NOLOCK); Because a rollback is performed, no results are obtained. SELECT * FROM dbo. Tranqueue, there is still a record.

SELECT* from with (NOLOCK )

NOLOCK hint ignore lock, this statement query get a record message2011-04-10 14:22:31.043, but this record did not commit

13. Execute the following statement,

SELECT* from dbo. Tranqueue

The SQL statement is blocked here and the statement is executed all the time. Because we use TABLOCKX in the Process tranqueue task, here we wait for the task to roll back or commit. Or can be written like this

DELETE TOP () dbo. Tranqueue;insert into dbo. Tranqueuehistory VALUES (' Test Message ' + CONVERT (NVARCHAR, GETDATE (), 121)), it still blocks

14. Click the Continue button or the Debuge button to see that the package failed to execute the SELECT * FROM dbo. Tranqueuehistory with (NOLOCK); Because a rollback is performed, no results are obtained. Executes select * FROM dbo. Tranqueue, there is still a record.

If the value of the set variable user::v_simulatefailure is 0, the simulate failure task will not be executed, and the records in Tranqueue will not be rolled back to tranqueuehistory. Here's a very interesting statement:

DELETE TOP () dbo. Tranqueue
OUTPUT deleted.*
INTO dbo. Tranqueuehistory
FROM dbo. Tranqueue with (Tablockx)

If the structure of the two tables is part of the same, and now you want to import data from one table to another, you can use the DELETE sourcetable OUTPUT delete.*/delete. Column1,delete. Column2 ... into destinationtable from SourceTable so that the data in the first table is "clipped" to the second table.

　　　　

SQL drip 9-sql transactional processing in server and built-in transactions in SSIS