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

  • Thread starter Thread starter Manoj K
  • Start date Start date
M

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.
 
Consider running all of the statements in a single stored procedure and let
SQL Server handle the transaction rather than roundtrip. You can also set
the CommandTimeout on the Command object(s), ConnectionTimeout on the
Connection object.

Even faster, consider using XML for your INSERTS, UPDATES and cascaded
DELETEs.

--
Gregory A. Beamer
MVP; MCP: +I, SE, SD, DBA

**********************************************************************
Think Outside the Box!
**********************************************************************
 
I'm not sure I understand what you mean by "Even faster, consider using XML
for ..."

Could you clarify it?

Thanks,
José
 
Gregory,

I think the transaction seems to be working fine because
it is doing the ROLLBACK. I still think it is the LOCK
that is causing the problem. Any ideas?
 
Hi,

"SELECT (on TL2) works and then I'm able to do an INSERT
into TL2."

I think what is happening is that once the insert occurs, your future
selects are locked.

Maybe you could change your transaction level, from Table to Rows.

Hope this helps.
 
Back
Top