Hi All,

I am using a SQLTransaction to insert records into a table. At one
time, there are 5000 or more records to be inserted one by one. It
takes some 20-25 mins for the entire process to run.

Another application accesses the same table.

As long as the insert process within the transaction isn't completed,
the second application is not getting any response from the server. I
even tried to run a SELECT on the table in SQL Query Analyzer while the
insert process was running and it also did not respond till the time
the insert process finished!

Is this normal that a transaction is locking up a table? How do I
overcome it? I am using IsolationLevel.ReadUncommitted for the
transaction.

If I do not run the process within a SQLTransaction, the second process
or running the SELECT in Query Analyzer does not hang.

Thanks,
Sanjeev Mahajan

Your description sounds more or less normal - if you have your INSERTs
inside a transaction, then other processes will not be able to see that
data until the transaction commits. Transactions don't always lock the
whole table, but it depends on what you're doing, your volume of data,
your indexes etc.

In any case, 20-25 minutes to insert only 5000 rows sounds extremely
slow - can you insert all the rows at once in a single INSERT, rather
than one by one? You might want to give some more details of exactly
how you're inserting the data (does it come from a cursor, an external
file, a client application etc.), and the DDL for your table - with
more information, someone may be able to suggest an improvement to your
current process.

Simon

[posted and mailed, please reply in news]

(mahajan.sanjeev@gmail.com) writes:
> I am using a SQLTransaction to insert records into a table. At one
> time, there are 5000 or more records to be inserted one by one. It
> takes some 20-25 mins for the entire process to run.
>
> Another application accesses the same table.
>
> As long as the insert process within the transaction isn't completed,
> the second application is not getting any response from the server. I
> even tried to run a SELECT on the table in SQL Query Analyzer while the
> insert process was running and it also did not respond till the time
> the insert process finished!
>
> Is this normal that a transaction is locking up a table? How do I
> overcome it? I am using IsolationLevel.ReadUncommitted for the
> transaction.

Which isolation level you use for the INSERT, does not really matter here.
If another process says "SELECT * FROM tbl" when the INSERT process is
running, that process will be blocked, since it cannot read the newly
inserted but uncommitted. Unless, that is, the SELECT processes uses the
READ UNCOMMITTED isolation level. However, one should be very careful
with using dirty reads. If you don't understand the implications of
dirty reads, you should not play with them.

20-25 minutes for inserting 5000 rows is an awful lot of time. Is there
a trigger on the table? Inserting one-by-one is never effective, but
250 ms seconds per row is extreme.

One thing you could consider is to build an XML string from your
data, and then pass that string to a stored procedure which uses
OPENXML to shred this string. Then you can insert all 5000 rows in
one go. We have been using this in our application, to improve performance
in places where the GUI needs to store a lot of data in the database.

--
Erland Sommarskog, SQL Server MVP, esquel@sommarskog.se

Books Online for SQL Server SP3 at
http://www.microsoft.com/sql/techinf...2000/books.asp

The second process accessing the table is blocked on X locks held by the
forst process doing the insert. These can be row, page or table locks.
read uncommitted isolation doesn't take effect for modification like
insert/delete/update. You will have to make the second process to access
the table under read uncommitted so that it doesn't not take any lock, thus
not to be blocked by the insert.

--
Gang He
Software Design Engineer
Microsoft SQL Server Storage Engine

This posting is provided "AS IS" with no warranties, and confers no rights.
<mahajan.sanjeev@gmail.com> wrote in message
news:1117740482.583193.3520@g49g2000cwa.googlegrou ps.com...
> Hi All,
>
> I am using a SQLTransaction to insert records into a table. At one
> time, there are 5000 or more records to be inserted one by one. It
> takes some 20-25 mins for the entire process to run.
>
> Another application accesses the same table.
>
> As long as the insert process within the transaction isn't completed,
> the second application is not getting any response from the server. I
> even tried to run a SELECT on the table in SQL Query Analyzer while the
> insert process was running and it also did not respond till the time
> the insert process finished!
>
> Is this normal that a transaction is locking up a table? How do I
> overcome it? I am using IsolationLevel.ReadUncommitted for the
> transaction.
>
> If I do not run the process within a SQLTransaction, the second process
> or running the SELECT in Query Analyzer does not hang.
>
> Thanks,
> Sanjeev Mahajan
>