Long .NET transaction causing problem

Question

4.00/5 (1 vote)

See more:

Hi,

ASP.Net 4.0 website, SQL Server 2008

I have some code in a website that reads, updates and inserts records into various tables with a transaction around it using .Net code. I am doing it this way as all the DLLs we have at a DAL level are already done for the calls to the database, and the whole updating can happen in a loop many times.

So I begin the transaction, loop through a bunch of data, selecting, updating and inserting records. Once the loop finishes, I either commit the transaction or roll it back if I discover that there was a problem along the way.

The problem is that if another user is logged on to the website, and accesses the same tables I get errors from either user and either user will have an exception occur which might be:
This SqlTransaction has completed; it is no longer usable.
or
Transaction (Process ID 57) was deadlocked on lock resources with another process ...

My guess is that it seems as though one user is somehow accessing another connection or something.

If my open transaction could potentially take a long time, should it all work if set up properly do you think? Or is this not a good way to go anyway (not the preferred answer ;) ).

I know you haven't seen any code, but there's quite a bit to it, and I just wanted a general opinion, as long as what I've said makes sense.

Thanks

Julian

What I have tried:

google searches, colleagues, different environments

Posted 11-May-16 7:27am

julian@giant

Updated 25-May-16 4:31am

Add a Solution

Comments

ZurdoDev 11-May-16 13:33pm

You can add WITH (NOLOCK) to your select statements so the user accessing the same tables won't care that you have them locked during your updates.

julian@giant 11-May-16 14:01pm

Thanks, I will give that a try.

Richard Deeming 11-May-16 14:11pm

NOLOCK is generally a very bad idea:
SQL Server NOLOCK Hint & other poor ideas[^]

Where possible, you should try to use snapshot isolation[^] instead.

CHill60 11-May-16 13:45pm

Does your transaction have to be open for so long? Could it be broken down into other smaller transactions? I.e. is it essential that all of the data is either committed or rolled back or can some units be committed even though others have failed.
Without seeing some sample code it is difficult to suggest solutions.

julian@giant 11-May-16 14:05pm

Unfortunately, I can see no other way other that having it open for a period of time. Good thought though.

I am reading an uploaded XML file, validating the contents, updating that data into the database inside the transaction (the XML doesn't match a table in the DB, so I am extracting the XML data and calling various procedures), then validating the data uploaded by recreating the XML file based on the data in the db to confirm all OK (this part is quick), and if a match, commit.

CHill60 11-May-16 14:09pm

Without further info all I can suggest is loading the data to a dummy table up to the point of verification and then loading the verified data to the real table (which should be quite quick). At least then no-one should be dead-locked

Herman<T>.Instance 12-May-16 5:38am

has your query both inserts and selects?

julian@giant 16-May-16 4:42am

Hi. Sorry for not getting back to you sooner. Yes it does issue inserts, updates and selects.

I'm working on the theory at the moment that the problem is caused by a Private Boolean in a VB.Net Module, that is set to True, and IIS sees this as True from another person's session, when it should be False. Unbelievable I know, but I have very specifically seen this occur before.

Herman<T>.Instance 16-May-16 5:41am

You might have a read-write lock problem when 1 query does both insert/update/delete and select in one query. (RXWX locking). Put the query in a transaction.

1 solution

Add a Solution

Add your solution here

Treat my content as plain text, not as HTML

Preview 0

…

Existing Members

Sign in to your account

...or Join us

Download, Vote, Comment, Publish.

Your Email
Password
Forgot your password?

Your Email
This email is in use. Do you need your password?
Optional Password

I have read and agree to the Terms of Service and Privacy Policy
Please subscribe me to the CodeProject newsletters

When answering a question please:

Read the question carefully.
Understand that English isn't everyone's first language so be lenient of bad spelling and grammar.
If a question is poorly phrased then either ask for clarification, ignore it, or edit the question and fix the problem. Insults are not welcome.
Don't tell someone to read the manual. Chances are they have and don't get it. Provide an answer or move on to the next question.

Let's work to help developers, not make them feel stupid.

This content, along with any associated source code and files, is licensed under The Code Project Open License (CPOL)

julian@giant · Accepted Answer · 2016-05-25T04:31:00

Hi all,

Thank you all for your suggestions. It is always useful to have input of any kind I find, even if it just rules things out.

I believe I have solved the issue.

The first issue was table locking during the transaction, this has been resolved by applying suitable indexes on tables to make sure a table scan isn't happening and hence locking the table. Some uses of FORCESEEK were also required in stored procedures.

When this was resolved, as suspected, there was a 'sharing' of a parameter that was set in one session, and then shared across other sessions. This parameter being an indicator to the code that there was a transaction occurring (a Boolean). Other sessions picked up this Boolean as 'True' when they should have been 'False'.

That was completely my fault. It has caught me out before, and I should have realised sooner. But it is worth noting here in case anyone else experiences this.

We are stuck using VB.Net and in the DLL is a Module (not everyone's favorite way of coding) with a Private Boolean, that once set by one session can potentially be picked up by another session (!). Everything else in the Module is 'contained' in black box functions. Unless there is a setting I don't know about in IIS to override this sharing parameter feature.

Anyway, didn't want to leave this case open as I believe I have this resolved. Am currently Load Testing and all seems good.

Julian