SQL Transaction with C#

Question

I have C# windows application.When 'Button 1' click in 'Form 1' I have a process that written data to few tables. That process normally taking 10 minutes. When one user click that 'Button 1' , another user who using same DB and program clicking another button in another form which inserting data to same tables.

When Button 1 clicking I'm starting the SQL Transaction and commiting that after finish the process.

Problem is when running that process, 2nd user getting a time out error and he can't complete his process.

What can I do for this? When SQL transaction starting is thet locking the tables which using for the process?

This means When SQL transaction starting is that locking the tables which using for the process? Other users can't access the tables until transaction committed? Is that the way transaction Working? — Tom, Apr 11 '14 at 05:54
Maybe , why not handle the exeption with doing until the other user is done — Poomrokc The 3years, Apr 11 '14 at 05:55
Yeah. That would be like deleting 400 million rows, loading a similar amount of simple rows or a lot of other things. Definitely a conceptual mistake or totally overloaded hardware or a "use an index, luke" case. — TomTom, Apr 11 '14 at 05:58
Who cares? That will hardly make a difference as the problem is neither winform nor console related. — TomTom, Apr 11 '14 at 06:01
No I'm writing a solution and it does need to output working state , so Should I write the output in console command or winform command. — Poomrokc The 3years, Apr 11 '14 at 06:05

score 4 · Accepted Answer · answered Apr 11 '14 at 05:57

4

What can I do for this?

Rework your transaction handling. Seriously.

That process normally taking 10 minutes

A 10 minutes transaction is not practical by any means. I fail to see any reason for this outside bad comceptionalization - and I am doing database work for maybe 20 years all in all. Never tolerated and had a real need for transactions that long, normally there is a way around it.

Yes, you could change a timeout, but that means user 2 staring at the screen for 15 minutes. Not a real solution.

Rework your transaction from a logical point of view. What the heck do you do there that takes 10 minutes to start with?

answered Apr 11 '14 at 05:57

TomTom

61,059
10
88
148

I have some import jobs that take 20+ minutes. I can't really help that, but I can ensure that other queries against the same table (but different data, or against the same data in non-critical reads) don't block .. – user2864740 Apr 11 '14 at 05:59
1

Well, what takes 20 minutes on such a job that needs to block the tx? Hint - I normally import bulk via SqlBulkCopy and into temporary tables then copy over to final destination once all data is uploaded. – TomTom Apr 11 '14 at 06:01
Tom, I got your point. But that means, If 1000 users working in same application and if they inserting data to same set of tables when I'm using Transactions until first user date inserting, 2 nd user have to wait know? That is the way transaction working know? – Tom Apr 11 '14 at 06:21
Yes, generally - a lot deoends on how you set it up, but as transaction give guarantees about data integrity, they have to - enfoce them and locks are the mechanism to do that. This is why transaction design (i.e. how you do things towards the database) is not always trivial. Outside of ETL work (that happens nightly in batches normally) I have never seen a need for super large tx - and even there they can typically be avoided. – TomTom Apr 11 '14 at 07:02

score 1 · Answer 2 · answered Apr 11 '14 at 06:03

Simple solution but may need user to wait for a long time.

If you are using a thread to accomplish this(winform) with label1 as the working state

   void transaction(data)
    { 
     try
     {
       thread.sleep(100);
       sqltransaction(data);//whatever your code is
     }
    catch(Exception)
     {
       transaction(data);
       label1.Invoke((MethodInvoker)(() => label1.Text = "Requested pending , do not close the program";
     }
       label1.Invoke((MethodInvoker)(() => label1.Text = "Working...";
    }

This is the way I usually handle error that I can't really fix it.

Thanks

score 0 · Answer 3 · answered Apr 11 '14 at 05:55

A transaction is by definition blocking other transactions access to the same resources because it's the way the SQL server guarantees that data is persisted at the commit and isn't changed by somebody else. It's the

So - what you can do is either make your transaction run faster, so it doesn't block and lock for long. Apply longer wait for second user. Or not use transactions at all. (or handle the situation "better" in your code layer, if it is 'expected behaviour')

You can also check your transactions that it doesn't block too much, but if the second process really needs to access the same resources it will have to wait.

However, If really wanting to get "dirty" you can change isolation levels for the server to allow reading of dirty data but that's absolutely not something I'd advice unless you know what you're doing. But it's something which can speed up read queries but it can have rather strange consequences reading dirty data and dirty data pages.

SQL Transaction with C#

3 Answers3