Timeout expired. The timeout period elapsed prior to completion of the operatio

[Manoj K]

Environment:-
Framework 1.1
DB: SQL Server 2000 (SQL Provider)
OS: Windows 2000
Language: VB.NET

I'm trying to do mass inserts/updates. Using Transactions.
The table hierarchy contains 3 levels (TL1, TL2, and TL3):
Here is what I'm trying to accomplish:

1) Start Begin Transaction (in desperation, I've tried all
the isolation levels- I was hoping ReadUncommitted would
work!)
2) Do an Insert/Update in table TL1 (this works).
3) Grab the ID from step 2 and do a SELECT on table TL2 to
determine if I need to an UPDATE or INSERT.

Since I'd be inserting 3 rows into table TL2 (for a
particular parent ID with different types), the first
SELECT (on TL2) works and then I'm able to do an INSERT
into TL2. The transaction is still alive. Now, I try to do
a SELECT on table TL2 to see if a record exists for this
type and this is when I get the exception "Timeout
expired. The timeout period elapsed prior to completion
of the operation or the server is not responding."

My guess is that I have a LOCK on the table TL2 (from the
first SELECT), which seems to be causing the problem.

Note:
The INSERTs and UPDATEs are done by executing stored procs.
For the SELECTS, I'm using a different connection (from
the one which the transaction is using) and close it after
the SELECT is executed.

 

[Miha Markic]

Hi Manoj,

Note:
The INSERTs and UPDATEs are done by executing stored procs.
For the SELECTS, I'm using a different connection (from
the one which the transaction is using) and close it after
the SELECT is executed.

Why? If you use the same connection no lock will occur.

 

[William Ryan]

In addition to Miha's comment, I'd really look to speeding up the inserts ,
I'd have to see the procs to offer any advice, but there's a Timeout
property of the command object that you can bump upward if you need more
time to get things done.

 

[Mahesh ChandraMouli[MVP]]

Hi Manoj,

There are 2 options u can do, but these two are temporary
stuff.
1. Increase the command timeout
2. Keep ur queries with explicit no lock keyword.

I hope doing this will solve ur problem temporarily.

To Identify where is this problem occuring
1. Write a trace in each and every method ur exceution is
going on, print the time before ur method execution
starts and after ur execution n ends. By this way u can
identify which method is taking a longer time to execute.
2. In the SQL profilier create a trace and check ur
queries.
3. Check ur Isolation level.

If you still have any queries, shoot a mail.

Regards
Mahesh ChandraMouli
Microsoft .NET MVP|MCAD(Charter Member)

 

[William \(Bill\) Vaughn]

I doubt if any amount of additional command time will solve your problem. If
this works it should happen in the blink of an eye. If it's locking itself
out, it will never finish. I expect that one of the INSERTs is locking an
index and that prevents the other operations from completing.
I suggest using a stored procedure that takes all of the data as parameters
and execute a series of operations that perform the updates.
I would not use client-managed transactions either. Do your transaction
management from within the SP.
hth

--
____________________________________
William (Bill) Vaughn
Author, Mentor, Consultant
MVP, hRD
www.betav.com
Please reply only to the newsgroup so that others can benefit.
This posting is provided "AS IS" with no warranties, and confers no rights.
__________________________________

 

[Guest]

I changed the code to use the same connection for the SELECT queries within the context of the transaction. Also, I had to set the Transaction property of the Command Object. It was kinda frustrating but finally got it to work. I agree with your suggestions though... (write a stored proc and let SQL Server take care of the transaction).