March 3, 2009 at 6:40 am
Hello All,
I have been searching about the TSQL Transactions. I ll give a code example:
BEGIN TRANSACTION PlayGame
SELECT * FROM [Table1]
SELECT * FROM [Table2]
UPDATE [Table1]
INSERT INTO [Table2] ...
COMMIT TRANSACTION
ROLLBACK TRANSACTION
Ok, this is a simple example. My question is about if this can be done in parallel. I have a Windows Service application that each 0.5 second, runs a stored procedure that has the above code in it.
Lets say that the average time of the stored procedure is 2 seconds.
As I know the default isolation level of transactions is "Committed read". Am I correct on this?
When the sp is run for the first time, the second time (after 0.5 seconds) will wait for the first call to end first? And then continue to the second call? Or the sp are executed in parallel ?
I am a little confused on this.. Need some help.. Thank you
March 3, 2009 at 9:57 am
in your UPDATE, if you specify proper table hint (e.g. WITH(ROWLOCK)) AND SQL Server has enough resource to lock the affected rows, then another transaction will not get blocked.
if SQL Server does not have enough resource for locking, it will escalate from ROWLOCK to TABLOCK.
in the end, it will block other transactions that try to access table1 and table2.
March 3, 2009 at 11:09 am
it depends on the locks that the update and insert will demand.
if your update is fiddling with, say, a single row of data, then the update will get a shared lock on the table, so other items can also update the table, but an exclusive lock on a specific row of data.
so other transactions, as long as they are not trying to do the same row at the same time, can be processed in parallel.
if a transaction is trying to do the same row, then it will have to wait until the first transaciton is done...so THAT one wqould be sequentially.
similarly, if your update is affecting a suite of rows, say based on a date range or something, the compiler will at some point decide whether a collection of rowlocks is les "expensive" timewise than just grabbing the whole table and locking it...so the same situation occurs, if another update comes along and affects a row outside of the scope of updates, they would run side by side, if it's affected by the row or table lock, then it'll wait till the transaction completes.
Lowell
March 3, 2009 at 12:14 pm
Viewing 4 posts - 1 through 3 (of 3 total)
You must be logged in to reply to this topic. Login to reply