SQL Transaction --- BLOCKING INSERTS

Question

SQL Transaction --- BLOCKING INSERTS

Vinay Jamalpur-253105

SSC Rookie

Points: 47
More actions
August 25, 2010 at 3:24 pm

#228360

Hi...Can anyone help me with my question....
Ok... I have a SQL tranaction say T1 with default Isolation level.....
In that Transaction T1 ... I have say 5 insert statements inserting thousands of records into say tables TBL_1,TBL_2,TBL_3,TBL_4,TBL_5.
I have a seperate C# web page (method which has it's own transaction)... which tries to insert records into say TBL_3 when the
transaction T1 is running....
My problem is that the inserts from the C# Web Page are blocked until the transaction T1 is committed....
Is there any way.. I can do both inserts without one blocking the other by changing the Isolation level of the transaction....
Obviously... I do not want inserted records to be selectable until the Transaction is committed....
Regards,

Viewing 7 posts - 1 through 6 (of 6 total)

You must be logged in to reply to this topic. Login to reply

caladba SSCommitted Points: 1514 More actions · Answer 1

I believe the web page can't insert while T1 is still open because of index locks. T1 should only be doing page locks (depends on your existing row vs inserted row ratio)

You should change the transaction (T1) to insert less rows at a time. (especially if this is going on during high traffic hours)

Or, depending on the frequency and type of data being inserted, I would look into service broker (or some kind of queue) to manage your inserts.

Bhuvnesh SSC Guru Points: 59351 More actions · Answer 2

I would say, remove the transaction's logic from Sql (database side). because acc to your logic doesnt care about transaction as it is trying to insert data before the Database side's transaction goto complete.

So remove the transaction handling from Sql code and let sql server handle the insertions.

-------Bhuvnesh----------
I work only to learn Sql Server...though my company pays me for getting their stuff done;-)

frodriguez.im SSC Enthusiast Points: 184 More actions · Answer 3

You can try INSERT with the ROWLOCK hint, I'm not sure if that will work in your situation though.

Vinay Jamalpur-253105 SSC Rookie Points: 47 More actions · Answer 4

Thanks a lot! for your replies...

Here is a complete background for you to better understand.....

The transaction T1 is part of a daily data load....many tables are loaded from a file....Initially they are loaded into TEMP tables.. and then within the transaction T1 they are copied to the main tables....

This is all happening when the users are actually working (traffic varies)... they try to insert their own rows into the same tables (which are part of transaction)....

Here is a sample code...(FROM the FILE LOAD)

-----------------------------------------------------

transaction = connection.BeginTransaction()

INSERT INTO TBL_1

SELECT * FROM TBL_1_TEMP

INSERT INTO TBL_2

SELECT * FROM TBL_2_TEMP

INSERT INTO TBL_3

SELECT * FROM TBL_3_TEMP

INSERT INTO TBL_4

SELECT * FROM TBL_4_TEMP

INSERT INTO TBL_5

SELECT * FROM TBL_5_TEMP

transaction.Commit();

-----------------------------------------

Here is the sample code (C# Web Page)

******************************************

transaction = connection.BeginTransaction();

//code to insert into TBL_4 & TBL_5

transaction.Commit();

********************************************

My problem is that the C# code is blocked even when the transaction T1 is working on TBL_1 ,TBL_2 or TBL_3.....

I can understand the blocking if T1 is inserting into TBL_4 or TBL_5......

Bhuvnesh SSC Guru Points: 59351 More actions · Answer 5

i would say , remove the explicit transactions from INSERT code , i dont think it is required

-------Bhuvnesh----------
I work only to learn Sql Server...though my company pays me for getting their stuff done;-)

caladba SSCommitted Points: 1514 More actions · Answer 6

If you want any one of the failed insert to revert back other previous inserts, you do need to explicitly declare transactions.

Without using transactions...

You can have each insert lock one table at a time, yet still roll back all inserts by using nested try catch blocks.

eg (pseudo code)

TRY

INSERT INTO TBL_1

SELECT * FROM TBL_1_TEMP

TRY

INSERT INTO TBL_2

SELECT * FROM TBL_2_TEMP

CATCH

DELETE FROM TBL_1

WHERE (SELECT 1 FROM TBL_1_TEMP WHERE ...)

RAISEERROR()

CATCH

DELETE FROM TBL_1

WHERE EXISTS (SELECT 1 FROM TBL_1_TEMP WHERE ...)

You can also try using transations but insert lesser number of records at at time.

eg. INSERT INTO TBL_1

SELECT TOP 100 * FROM TBL_1_TEMP

Then delete the inserted row from TBL_1_TEMP, and run the job more frequently.