SQL Clone
SQLServerCentral is supported by Redgate
 
Log in  ::  Register  ::  Not logged in
 
 
 


All About Transactions - Part 1


All About Transactions - Part 1

Author
Message
DCPeterson
DCPeterson
SSCertifiable
SSCertifiable (5.6K reputation)SSCertifiable (5.6K reputation)SSCertifiable (5.6K reputation)SSCertifiable (5.6K reputation)SSCertifiable (5.6K reputation)SSCertifiable (5.6K reputation)SSCertifiable (5.6K reputation)SSCertifiable (5.6K reputation)

Group: General Forum Members
Points: 5579 Visits: 432

It is generally very bad practice to wait for user input inside a transaction. But the scenario you lay out would be a worst-case. Not only would you have to wait for user input within the transaction, you would also have to allow dirty reads. As you point out, this is a recipie for disaster both from a performance as well as a security standpoint. I would say that any developer and/or DBA that wrote an application that badly should most certainly be fired, no questions asked.

Without knowing all the details, I would say that the temp table would mitigate the security risk somewhat, but poorly concieved transactions are the root problem and it doesn't help at all with that.



/*****************

If most people are not willing to see the difficulty, this is mainly because, consciously or unconsciously, they assume that it will be they who will settle these questions for the others, and because they are convinced of their own capacity to do this. -Friedrich August von Hayek



*****************/
Lashams
Lashams
SSC-Addicted
SSC-Addicted (472 reputation)SSC-Addicted (472 reputation)SSC-Addicted (472 reputation)SSC-Addicted (472 reputation)SSC-Addicted (472 reputation)SSC-Addicted (472 reputation)SSC-Addicted (472 reputation)SSC-Addicted (472 reputation)

Group: General Forum Members
Points: 472 Visits: 48
I've seen this in off the shelf software and fully agree with you.

Cheers

Stephen



Razvan Socol
Razvan Socol
SSCrazy
SSCrazy (2.1K reputation)SSCrazy (2.1K reputation)SSCrazy (2.1K reputation)SSCrazy (2.1K reputation)SSCrazy (2.1K reputation)SSCrazy (2.1K reputation)SSCrazy (2.1K reputation)SSCrazy (2.1K reputation)

Group: General Forum Members
Points: 2087 Visits: 129

Don, you wrote in the article:

"It doesn't matter where the BEGIN TRAN and COMMIT statements reside between the two procedures. [...] So where you place your BEGIN...COMMIT is pretty much a matter of preference, not function."

This is true, as long as you put both the BEGIN TRAN and the COMMIT in the SAME procedure. For example, the following code would result an error:

CREATE PROCEDURE test 
AS
BEGIN TRAN
-- and do some work here
GO
EXEC test 
COMMIT

And here is the result:

Server: Msg 266, Level 16, State 2, Procedure test, Line 4
Transaction count after EXECUTE indicates that a COMMIT or ROLLBACK TRANSACTION statement is missing. Previous count = 0, current count = 1.

Razvan





DCPeterson
DCPeterson
SSCertifiable
SSCertifiable (5.6K reputation)SSCertifiable (5.6K reputation)SSCertifiable (5.6K reputation)SSCertifiable (5.6K reputation)SSCertifiable (5.6K reputation)SSCertifiable (5.6K reputation)SSCertifiable (5.6K reputation)SSCertifiable (5.6K reputation)

Group: General Forum Members
Points: 5579 Visits: 432
Thanks for the clarification. I had not thought of that interpretation of what I said...

/*****************

If most people are not willing to see the difficulty, this is mainly because, consciously or unconsciously, they assume that it will be they who will settle these questions for the others, and because they are convinced of their own capacity to do this. -Friedrich August von Hayek



*****************/
Del Murray
Del Murray
SSC Veteran
SSC Veteran (269 reputation)SSC Veteran (269 reputation)SSC Veteran (269 reputation)SSC Veteran (269 reputation)SSC Veteran (269 reputation)SSC Veteran (269 reputation)SSC Veteran (269 reputation)SSC Veteran (269 reputation)

Group: General Forum Members
Points: 269 Visits: 12

I think this is one of the best articles that I have read here. It is clear, stays focused, go straight to the point, has no grammatical errors (well, I did spot one tiny missing comma), no spelling errors and does not contain acronyms that only an old and seasoned DBA would understand. Your hired

Del


Raj Shekar
Raj Shekar
SSC Eights!
SSC Eights! (838 reputation)SSC Eights! (838 reputation)SSC Eights! (838 reputation)SSC Eights! (838 reputation)SSC Eights! (838 reputation)SSC Eights! (838 reputation)SSC Eights! (838 reputation)SSC Eights! (838 reputation)

Group: General Forum Members
Points: 838 Visits: 15
Hi,

I have never new that there is so much about transactions. Its really a very good article.

Raj
Go


Permissions

You can't post new topics.
You can't post topic replies.
You can't post new polls.
You can't post replies to polls.
You can't edit your own topics.
You can't delete your own topics.
You can't edit other topics.
You can't delete other topics.
You can't edit your own posts.
You can't edit other posts.
You can't delete your own posts.
You can't delete other posts.
You can't post events.
You can't edit your own events.
You can't edit other events.
You can't delete your own events.
You can't delete other events.
You can't send private messages.
You can't send emails.
You can read topics.
You can't vote in polls.
You can't upload attachments.
You can download attachments.
You can't post HTML code.
You can't edit HTML code.
You can't post IFCode.
You can't post JavaScript.
You can post emoticons.
You can't post or upload images.

Select a forum







































































































































































SQLServerCentral


Search