SQL Transaction... Blocking inserts....

[vjamalpur]

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,

[ck9663]

The transaction, by default, will block any further changes on the data. This is because an INSERT, in sql server, is an atomic operation. It does not insert one record at a time and when it reaches an error on Nth record, you'll have a table with N-1 records. It's either successful or not.

The transaction holds the state of the table until it's committed. This is also because all underlying FK, PK, INDEXES needs to be enforced and updated accordingly as you insert the data. Not to mention identities and other computed columns.

The server does not know at the time of T1 if it will be committed or rolled back. Once committed, then the table is now available for use.

Regardless of isolation level, the transaction will lock your tables.

Happy Coding!!!

~~ CK