Sign in to follow this  
Thevenin

MSSQL, SQL Transactions

Recommended Posts

I cannot quite get my mind around SQL Transactions in MSSQL, so I'm just going to ask if this scenario is possible. Two users are posting on a forum: Adam , and Eve. Posting on said thread requires grabbing the LAST_TOPIC_ID, incrementing and saving it, and then of course, using the value in the POST that is to be entered into TABLE_POSTS. Now, Adam and Eve are posting at the EXACT same time. Their posts requests get sent to an MSSQL Stored Procedure that does the above, but encapsulated in a "BEGIN TRANSACTION {sql statements here} COMMIT". Without the TRANSACTION, it is apparent that there is a race-condition here, and that one post could overwrite the other (or worse). But it's not clear to me whether the TRANSACTION is going to prevent the race-condition. The point of transactions appear to be to reverse things when detectable errors occur, but detecting a race-condition is, in itself, subject to a race-condition. So, someone explain this to me... lol

Share this post


Link to post
Share on other sites
Transaction locks the database (depends, but it can). As such, entire transaction is atomic, and cannot be interrupted - there is no race condition, as long as the entire sequence of operations is performed as a transaction.

The fact that they are posting at the same time only leads to degraded performance, but does not affect integrity of the data.

Transactions roll-back is unrelated to this, it's merely an automated failure handling mechanism.

Share this post


Link to post
Share on other sites

Create an account or sign in to comment

You need to be a member in order to leave a comment

Create an account

Sign up for a new account in our community. It's easy!

Register a new account

Sign in

Already have an account? Sign in here.

Sign In Now

Sign in to follow this