Isolation level in cftransaction

[Guest]

Hi all,

We have been using cftransaction on our transaction process page where we need to do multiple insert queries to save transaction data.  If there is no error, we commit the transaction.  Once in a while though, we get the error message "Transaction was deadlocked on lock resources with another process and has been chosen as the deadlock victim. Rerun the transaction."  We are on SQL Server 2005.

What I understand from that error message is that there are multiple transactions trying to access the same tables at the same time.  Implicitly, one transaction has locked the tables while it is inserting data, and other transactions are not able to access them.

Now I don't fully understand cftransaction beyond its commit / rollback functionality.  I never quite understood the isolationLevel attribute of cftransaction.  I have read a number of descriptions, but the terms dirty read, phantom data, nonrepeatable reads are still confusing to me.  What I do understand is that unless we specify isolationLevel = serializable in a transaction, it is not locking the tables it is accessing.  In our use of cftransaction, we don't specify the isolationLevel attribute ( I believe the default is read committed .)

So here are my questions:

1.  If we don't specify any isolation level, why are we getting deadlock transactions?

2.  If we do want to lock the tables using isolationLevel = serializable, does a concurrent transaction trying to use the same tables automatically get deadlocked?  Or is there a mechanism to specify timeout ala cflock timeout attribute?

I'd appreciate someone clearing up my understanding of cftransaction.  Thanks!

[mrs_temple]

I know this is an old post, but I have had the same questions recently and based on my recent findings, have attempted to answer your questions below for anyone in the future:

1.  If we don't specify any isolation level, why are we getting deadlock transactions?

The reason this could be happening is because the cftransaction tag will use the default isolation level of your database. For SQL Server this is usually Read Committed. However, it is important to note that, "Choosing a transaction isolation level does not affect the locks acquired to protect data modifications. A transaction always gets an exclusive lock on any data it modifies, and holds that lock until the transaction completes, regardless of the isolation level set for that transaction. For read operations, transaction isolation levels primarily define the level of protection from the effects of modifications made by other transactions." This quote is taken directly from the MS SQL Server site. I understand this by stating that if you are doing a read, the isolation level determines the quality and/or quantity of the data returned from the read. If you are doing data modifications, the transaction will always get an exclusive lock on any data being modified. The cftransaction tag can control when it is committed based on its placement, but does not control the data modification isolation levels.

2.  If we do want to lock the tables using isolationLevel = serializable, does a concurrent transaction trying to use the same tables automatically get deadlocked?  Or is there a mechanism to specify timeout ala cflock timeout attribute?

The first question helps to answer this second one. The isolation level attribute (serializable or other) applies to the level of protection read operations will receive from other transactions' modifications. It does not effect on the locks aquired to protect data modifications. This is controlled by the database itself. The cflock tag only applies to CF meaning it ensures single thread access to that code (e.g. application, session or server variables), not to the database.

I hope this helps someone in the future and that I have not misstated anything. If anyone can provide better clarification please do so.