SQLTransaction Timeout

  • Thread starter Thread starter Mel Grubb II
  • Start date Start date
M

Mel Grubb II

To keep this as general as possible, I'll start with the following
assumptions.

1) I have a piece of code "A", which does not have any support for
transactions because it only ever does a simple read from the database.
2) I have another piece of code "B", which requires and supports
transactions.

I have now run into a situation in which code B is causing code A to run.
This always results in a timeout error because I apparantly cannot perform
any operations outside of the transaction while it is open. This would make
sense except that code A is opening its own connection to the database.
Shouldn't this allow it to run? Is there some trick I can use to allow me
to perform operations "on the side" of an open transaction?


If you want more detail, here it is.
Code A is a lazy-filled property on Object B. It has no need of
transactions at all in order to do its work. It opens a private connection,
performs the read, and closes the connection on its own.

Code B is an update which must perform updates on sub-objects as well if
they exist. "A" is the collection of sub-objects. The second Code B
references A.Count, A tries to fill itself and hangs.

It is impractical to teach every lazy-filled property in our application to
support transactions since they will almost never be used that way. Any
ideas?

MG2
 
It's more like if A does a ready from anything that B just updated, it will
cause a deadlock. In cases where A runs in a situation like this, it needs
to do the reads under the same transaction.
 
That is not the situation here. A is reading something unrelated to
what B has done, but it is timing out. Basically, once I've opened a
Transaction it is preventing me from doing anything that doesn't
participate in that transaction.
 
Are you sure this is the case? I have never had a problem having other
connections read data while another connection was in a transaction, as long
as the data was not anything the transaction was working with.

Did you check the server for any blocking threads? This is usually very
helpful, as it keeps track of the last query issued, and helps identify what
the problem is.
 
What is the isolation level of the transaction? You could be experiencing
blocking of data read by the transactional connection B, even though updates
are not applied by B to rows read by A. This will occur at isolation level
Read Serializable / Repeatable.
 
I am currently running a Transaction with an IsolationLevel of
ReadUncommitted.
 
Back
Top