473,420 Members | 1,588 Online
Bytes | Software Development & Data Engineering Community
Post Job

Home Posts Topics Members FAQ

Join Bytes to post your question to a community of 473,420 software developers and data experts.

SQL Transaction... Blocking inserts....

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,
Aug 25 '10 #1
1 2020
ck9663
2,878 Expert 2GB
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
Aug 25 '10 #2

Sign in to post your reply or Sign up for a free account.

Similar topics

4
by: Tony | last post by:
Is there any known SQL Server bug whereby a record can be successfully inserted and committed, but then later be found not to be in the database? For example, if there was a server crash just...
6
by: Lauren Quantrell | last post by:
Is it possible the existence of a return parameter on an SP running insert statements would cause blocking to happen (any removing it might help eliminate it?) The SPs in question have three...
3
by: Chris Ochs | last post by:
First of all, we are still running sapdb at the moment but are in the process of moving to postgresql, so it seemed a good idea to post this type of question here. In our environment we have...
7
by: Steven D.Arnold | last post by:
How good is Postgres' performance for massive simultaneous insertions into the same heavily-indexed table? Are there any studies or benchmarks I can look at for that? I understand Postgres uses...
6
by: Chris Ochs | last post by:
I want to do a series of inserts within a single transaction block, but with postgresql if one insert fails, the whole block is aborted. Is there any way to get around this behavior so that...
1
by: REB | last post by:
If I create a transaction object for rolling back a insert query in the on click event of a form button can the transaction be tracked across other functions? For example: private void...
2
by: simonZ | last post by:
I create a transaction: sqlTran=sqlConn.BeginTransaction(IsolationLevel.Serializable); Then, I insert some data into report table with sqlCommand object: oCmd = new...
1
by: cricketweb | last post by:
I have a stored procedure that calls another stored procedure with the first stored procedure opening a transaction: BEGIN SET XACT_ABORT ON BEGIN TRANSACTION does various updates/inserts
4
by: quincy451 | last post by:
I am running MS SQL 2000 server. The table involved is only about 10,000 records. But this is the behavior I am seeing. The local machine is querying the table looking for a particular record....
18
by: Charles Law | last post by:
I have a sproc that returns data from a table using a simple SELECT. There are quite a few rows returned, e.g. ~150,000. In my first application, I use a reader on the sproc and iterate through...
0
by: emmanuelkatto | last post by:
Hi All, I am Emmanuel katto from Uganda. I want to ask what challenges you've faced while migrating a website to cloud. Please let me know. Thanks! Emmanuel
1
by: Sonnysonu | last post by:
This is the data of csv file 1 2 3 1 2 3 1 2 3 1 2 3 2 3 2 3 3 the lengths should be different i have to store the data by column-wise with in the specific length. suppose the i have to...
0
marktang
by: marktang | last post by:
ONU (Optical Network Unit) is one of the key components for providing high-speed Internet services. Its primary function is to act as an endpoint device located at the user's premises. However,...
0
by: Hystou | last post by:
Most computers default to English, but sometimes we require a different language, especially when relocating. Forgot to request a specific language before your computer shipped? No problem! You can...
0
Oralloy
by: Oralloy | last post by:
Hello folks, I am unable to find appropriate documentation on the type promotion of bit-fields when using the generalised comparison operator "<=>". The problem is that using the GNU compilers,...
1
by: Hystou | last post by:
Overview: Windows 11 and 10 have less user interface control over operating system update behaviour than previous versions of Windows. In Windows 11 and 10, there is no way to turn off the Windows...
0
tracyyun
by: tracyyun | last post by:
Dear forum friends, With the development of smart home technology, a variety of wireless communication protocols have appeared on the market, such as Zigbee, Z-Wave, Wi-Fi, Bluetooth, etc. Each...
0
agi2029
by: agi2029 | last post by:
Let's talk about the concept of autonomous AI software engineers and no-code agents. These AIs are designed to manage the entire lifecycle of a software development project—planning, coding, testing,...
0
isladogs
by: isladogs | last post by:
The next Access Europe User Group meeting will be on Wednesday 1 May 2024 starting at 18:00 UK time (6PM UTC+1) and finishing by 19:30 (7.30PM). In this session, we are pleased to welcome a new...

By using Bytes.com and it's services, you agree to our Privacy Policy and Terms of Use.

To disable or enable advertisements and analytics tracking please visit the manage ads & tracking page.