SSIS Transaction Management && Strange Deadlock When Using XLOCK, ROWLOCK

I have a SSIS package that is run from one job, nowhere else. The package has TransactionOption NotSupported.



In the SSIS package I first have a sequence container that has TransactionOption Required (Serializable). The sequence container contains several Execute SQL tasks that access the same SQL Server 2005 database. The Execute SQL tasks have TransactionOption supported / Serializable. The first SQL statement is:



select p.column_name

from table_name p with (XLOCK, ROWLOCK)

where p.second_column_name = 'COLUMN_VALUE'



After this there are a couple of SQL tasks, and finally a task containing the following SQL:



select p.third_column_name

from table_name p

where p.second_colomn_name = 'COLUNM_NAME'



The idea is that the first query exclusively locks the row in the table_name-table for mutual exclusion purposes, i.e. so that if for some reason the SSIS package would be executed simultaneously two or more times, only one package execution could lock the row and proceed and the other executions will have to wait. There's an index on column second_column_name in the table to avoid the select locking other rows in addition to the required row.



Some questions:

1) Is it so in my setup that when SSIS runtime executes the sequence container it creates a transaction in the beginning of the sequence container and commits the transaction in the end of the sequence container? And in my setup the Execute SQL tasks in the sequence containar are executed under the same transaction?

2) I have a problem that the second query sometimes gives this error:"ErrorCode: -1073548784. ErrorDescription: Executing the query "XXXXX" failed with the following error: "Transaction (Process ID 73) was deadlocked on lock resources with another process and has been chosen as the deadlock victim. Rerun the transaction.". Possible failure reasons: Problems with the query, "ResultSet" property not set correctly, parameters not set correctly, or connection not established correctly."

The 1st query has locked the row for the transaction. How can the second query be deadlocked, shouldn't the transaction have a lock on the row? I'd understand if the 1st query failed sometimes, but don't understand how the 2nd quey can fail.



r,

JM