Sql Transaction Problem

Question

0.00/5 (No votes)

See more:

SQL-Server-2005

, +

I am developing a project where I want to implement SQLTransaction. In form_Load Event I create and apply it. and In the SAVE_Click I want to Commit() or to RollBack() the transaction depending on a condition.

But until I close commit or rollback my other Database Commands are not executing and Request get Timeout.

Please help me. I am not getting the problem.

Posted 2-Jan-12 5:40am

Sourav Sarkar-SS

Updated 2-Jan-12 6:34am

Wonde Tadesse

v2

Add a Solution

3 solutions

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)

Espen Harlinn · Accepted Answer · 2012-01-02T05:45:00

Solution 1

I would rethink the design, you only need to execute the inserts/updates/deletes inside the transaction - so create your transaction inside the SAVE_Click method.

Best regards
Espen Harlinn

Posted 2-Jan-12 5:45am

Espen Harlinn

Comments

[no name] 2-Jan-12 11:59am

My need is little bit different, I need to write the command in Form_Load Only.and need to execute in Save_Click only. If possible please answer my question otherwise ....

Espen Harlinn 2-Jan-12 12:12pm

On form load you are only pulling information out of the database - and doing that inside a transaction is pointless. It's when you want to alter the information in the database that transactions make sense, as they allow you to group a set of operations together. A transaction guaranties that either all or none of the changes are made to the database.

Sridhar Patnayak 2-Jan-12 12:43pm

Good answer Espen. 5+

Espen Harlinn 2-Jan-12 12:44pm

Thank you, Sridhar!

Mehdi Gholam 2-Jan-12 12:07pm

Agree completely, 5'ed

Espen Harlinn 2-Jan-12 12:13pm

Thank you, Mehdi! - and a happy new year :)

thatraja 2-Jan-12 12:11pm

5!

Espen Harlinn 2-Jan-12 12:13pm

Thank you, thatraja! - and a happy new year :)

thatraja 2-Jan-12 12:23pm

Thanks for the wishes Espen & I wish you the same. BTW What about your family?
:)

Espen Harlinn 2-Jan-12 12:33pm

They're doing fine - thanks for asking - and yours?

thatraja 2-Jan-12 12:39pm

Also they are fine

Espen Harlinn 2-Jan-12 12:40pm

That's brilliant!

Wendelius 2-Jan-12 12:31pm

Exactly, my 5.

Espen Harlinn 2-Jan-12 12:34pm

Thanks Mika!

Mehdi Gholam · Accepted Answer · 2012-01-02T06:07:00

Solution 2

This is a very bad design.

Transactions should be as short as possible as they take up resources on the RDBM server.

You should absolutely not start on form load and commit/rollback on a button click which could be anything between 10 secs to 10 minutes in between.

Posted 2-Jan-12 6:07am

Mehdi Gholam

Comments

[no name] 2-Jan-12 12:13pm

I know this is a bad design but in between begin and commit transaction nothing is working.

Mehdi Gholam 2-Jan-12 12:24pm

It makes no difference, it could timeout, and it will lock up resources on the server anyway.

Espen Harlinn 2-Jan-12 12:14pm

Right - 5'ed!

Mehdi Gholam 2-Jan-12 12:23pm

Thanks

thatraja 2-Jan-12 12:24pm

I missed your answer(May be I didn't refresh the page), 5!

Mehdi Gholam 2-Jan-12 12:34pm

Cheers, and Happy new year!

thatraja 2-Jan-12 12:40pm

Oops, Forgot to wish you, Thanks. I wish you the same.

Wendelius 2-Jan-12 12:31pm

Totally agree, my 5.

Mehdi Gholam 2-Jan-12 12:34pm

Thanks

Sridhar Patnayak 2-Jan-12 12:37pm

Good suggestion

Wendelius · Accepted Answer · 2012-01-02T06:29:00

Solution 3

Agree with both good answers. A very important rule-of-thumb is: Never have user conversations inside a transaction! No questions, no notifications, just pure logic and executions of SQL statements. If you have to ask something from the user, do it before the transaction. If you encounter problems, rollback and then inform the user.

If you need to know that the situation has been unchanged while the user makes modifications, use optimistic locking. Pessimistic locking just isn't suitable for longer periods of time.

Posted 2-Jan-12 6:29am

Wendelius

Updated 2-Jan-12 6:30am

v2

Comments

Mehdi Gholam 2-Jan-12 12:35pm

5'ed and Happy new year Mika!

Wendelius 2-Jan-12 12:41pm

Thanks and Happy New Year for you too. [EDIT] Sorry the latter was for Espen :)

Espen Harlinn 2-Jan-12 12:35pm

5'ed! - I'd go for optimistic locking too - as it avoids a lot of hassle

Wendelius 2-Jan-12 12:41pm

Thanks. I hope that the mudslide isn't affecting you in any way!

Espen Harlinn 2-Jan-12 12:43pm

Nope - I live in Bergen - but thanks for the thought :)

Sridhar Patnayak 2-Jan-12 12:36pm

Agree 5

Wendelius 2-Jan-12 12:42pm

Thanks :)