This thread looks to be a little on the old side and therefore may no longer be relevant. Please see if there is a newer thread on the subject and ensure you're using the most recent build of any software if your question regards a particular product.
This thread has been locked and is no longer accepting new posts, if you have a question regarding this topic please email us at support@mindscape.co.nz
|
I have some business logic that requires using TransactionScope to commit changes on serveral tables at the same time. My code is below. The first method ApproveTransaction is called first, this in turn calls UpdateBalances. If I remove the TransactionScope line from both methods, both methods work correctly. However, if I return the TransactionScope, the program always freezes on the line indicated with <------ Here --->. After a while the transaction will timeout and report the underlying transaction has been terminated. If I move all the relevant code from UpdateBalances into ApproveTransaction, whilst still keeping the TransactionScope, again it works correctly. I can only assum that LightSpeed doesn't like nested TransactionScopes? FYI, I am using SQL Server 2005. And I have tested the MSDTC processing using another application that uses SQLCLient and COM+ Services against the same database, and all the DTC transactions work correctly. So I don;t believe this is an MSDTC or Firewall connection problem.
public static void ApproveTransaction(Int32 transId) UpdateBalances(trans); trans.IsPosted = true; if(trans.IsValid) public static void UpdateBalances(TransactionHeader trans) //Update period balances foreach (var tr in trans.TransactionRows) var balances = coa.AccountBalances.Find(ab => ab.FinancialYear == periodYear); if (balances == null) } //finally save alll the changes. |
|
|
I've tested a simplified version of your code and I don't see the freeze in the nested transaction scope -- it all seems to be working fine. Another customer has reported a difference in transaction behaviour between Windows 7 and Windows XP, and they also appear to be using nested transactions. What OS (and bitness) are you using, on both the client and the SQL Server machines? Can you provide us with a complete console project (plus SQL tables) that reproduces the freeze? Thanks! |
|
|
Hi Ivan, All our clients are running Vista Business 32 bit, and our Servers are 2003 64 bit. Hope this helps. Regards. |
|
|
Thanks. I don't have that configuration to hand right now, so I'll have to see if I can assemble the necessary virtual machines tomorrow. In the meantime, is it possible for you to try this with a different configuration -- specifically with the LightSpeed code talking to a local SQL Server? Also, according to your post, the code successfully accesses trans.TransactionRows, and freezes only on the FindOne<ChartOfAccount>. Is that correct? Can you tell me if trans.TransactionRows is eager or lazy loaded? |
|
|
Hi Ivan The TansactionRows are eager loaded from TransactionHeader. None of our computers have SQL installed locally, will try and test that later today. Is there a thought of a possible reason here, other than DTC not being configured correctly? As I mentioned previously, I have proven, with another app, that DTC is configured correctly on both the client and server, as we can perform nested transactions correctly. Thanks Ivan |
|
|
I'm trying to identify a difference between your environment where the freeze is happening, and my environment where it isn't. At the moment all I know of is OS differences (7 vs. Vista/2003) and local vs. remote SQL Server. Until I can reproduce the problem, I'm keeping an open mind about the possible cause; and I can't reproduce the problem until I can create an environment that causes it to manifest. That said, it might be worth looking in the DTC transaction logs to see if that provides any clues about what is trying to get into which transaction and why they're timing out. SQL Server Profiler might provide clues here as well. I'll see if our SQL Server guru has any further ideas on diagnostics. |
|
|
Ok, now I understand, I will also try a few different scenarios here, and see if I can pin this down. |
|
|
Hi Mark, Does the issue also occur in a single connection situation or only when multiple users are using the system? If its the latter you might want to check that this isnt more of a blocking related issue rather than something wrong with DTC configuration.
Jeremy |
|
|
Occurs with single user. |
|
|
Some additional information that may help. I installed SQL 2008 Express on my local computer, copied the databases to it, left the LS Context to be SQL Server 2005, and tried the nested transactions again, and they worked perfectly. Hope this will help narrow down the problem. |
|
|
Hello Mark, We've continued to investigate this and have not been able to reproduce the problem. I have been running your code (again, a simplified version) across a pair of machines and I am able to execute loads within the second transaction scope and UOW. The only time I have seen delays has been when I have misconfigured the DTC and even then it gave me an exception within a few seconds rather than just hanging as you are seeing. We suspect there may be some sort of locking issue within SQL Server -- Jeremy has some ideas on diagnosing this and will be following up with you shortly. |
|
|
Hi Mark, Just to follow up on this, I suspect the reason we may not be able to repro the problem may be down to the specific data being worked on and possibly some locking as looking at the way in which the code executes, it does generates two separate connections so lock contention may be a problem here. Is it possible to get a database backup of the test set you are using, or if that isn't feasible, would it be possible to get a profiler trace recorded? Let us know if either of those options are a runner for you and we can liaise further from there.
Jeremy |
|
|
Hi Jeremy, Ivan, Thanks for your continued support with this issue. I am currently out of the office, will be back in a couple of days. Will get back to you asap with the info you have requested.
|
|
|
Hi Guys, Please find attached a trace file. The trace begins when I open the form, to after the transaction failed, with a "Communications with the underlying transaction manager has failed." error. Hope this helps. Regards Mark |
|
|
Hi Mark, Is this still proving a problem for you or have you managed to resolve this or find a workaround? Unfortunately I cant really glean anything useful from the trace as it only contains the SQL statements being executed. If this is still an issue would you be able to recapture the trace with all "Locks" and "Transactions" events being captured as part of the trace and if you could provide some annotation as to what is occuring from the systems perspective during the time period which the log file is operating over that would help give us some context in interpreting what we are looking at.
Thanks! Jeremy |
|
|
Hi Jeremy, Since since raising this issue I have not revisited it, I was waiting for some feedback from yourselves. I will take another look at it today, and get back to you later. Thanks Mark |
|