Timeout expired
in [Programming SQL]
|
Prev: database object hierarchy
Next: NOLOCK SQL Query Safety
From: SQLCodder on 16 Feb 2006 15:20 I'm having trouble finding the cause of the following error message: [Error#: -2147217871 Description: Timeout expired (Source: Microsoft OLE DB Provider for SQL Server)] The error is occuring when trying to execute a simple update query: UPDATE merchant_billing SET billing_last_billing_date = '2/15/2006' WHERE merchant_id = 135485700901 I was thinking that the table must be locked somehow and this is not allowing the update to occur. The table is included in a earlier complex SELECT query that returns over 5000 records. I found that if I included "TOP 5" in this select query the error does not occur. Would it be possible this SELECT has not completed executing or has the table locked somehow? I am using Visual Basic 6 and ADO 2.7 Any suggestions apreaciated, thanks!
From: Andrew J. Kelly on 16 Feb 2006 16:14 Yes it sounds like the query is taking out a table level shared lock that is preventing the update. Run sp_who2 and sp_lock to see if there is blocking and what kind of locks are being taken out. Do you have an index on Merchant_ID? How about for the select?. -- Andrew J. Kelly SQL MVP "SQLCodder" <SQLCodder(a)discussions.microsoft.com> wrote in message news:ADCB3891-B925-4628-9BF3-FADB234B9913(a)microsoft.com... > I'm having trouble finding the cause of the following error message: > > [Error#: -2147217871 Description: Timeout expired (Source: Microsoft OLE > DB > Provider for SQL Server)] > > The error is occuring when trying to execute a simple update query: > > UPDATE merchant_billing SET billing_last_billing_date = '2/15/2006' WHERE > merchant_id = 135485700901 > > I was thinking that the table must be locked somehow and this is not > allowing the update to occur. The table is included in a earlier complex > SELECT query that returns over 5000 records. I found that if I included > "TOP > 5" in this select query the error does not occur. Would it be possible > this > SELECT has not completed executing or has the table locked somehow? > > I am using Visual Basic 6 and ADO 2.7 > > Any suggestions apreaciated, thanks! > > >
From: SQLCodder on 16 Feb 2006 17:34 The merchant_id field is a primary key, but dones't have an index on it. There isn't much for indexes on the select either. There are seveal JOINs and sub queries in the select with some tables having indexes. Is there any way I can prevent any locking from occuring? "Andrew J. Kelly" wrote: > Yes it sounds like the query is taking out a table level shared lock that is > preventing the update. Run sp_who2 and sp_lock to see if there is blocking > and what kind of locks are being taken out. Do you have an index on > Merchant_ID? How about for the select?. > > -- > Andrew J. Kelly SQL MVP > > > "SQLCodder" <SQLCodder(a)discussions.microsoft.com> wrote in message > news:ADCB3891-B925-4628-9BF3-FADB234B9913(a)microsoft.com... > > I'm having trouble finding the cause of the following error message: > > > > [Error#: -2147217871 Description: Timeout expired (Source: Microsoft OLE > > DB > > Provider for SQL Server)] > > > > The error is occuring when trying to execute a simple update query: > > > > UPDATE merchant_billing SET billing_last_billing_date = '2/15/2006' WHERE > > merchant_id = 135485700901 > > > > I was thinking that the table must be locked somehow and this is not > > allowing the update to occur. The table is included in a earlier complex > > SELECT query that returns over 5000 records. I found that if I included > > "TOP > > 5" in this select query the error does not occur. Would it be possible > > this > > SELECT has not completed executing or has the table locked somehow? > > > > I am using Visual Basic 6 and ADO 2.7 > > > > Any suggestions apreaciated, thanks! > > > > > > > > >
From: Anith Sen on 16 Feb 2006 18:37 >> Is there any way I can prevent any locking from occuring? Locking in general is essential for the DBMS to support concurrency. Why would you want to prevent it? I think you are concerned about processes being blocked. Check the master..sysprocesses table to see if the process identifier ( spid ) of the UPDATE statement is being blocked by any other processes. A handy little tool for tracking the locks can be downloaded from Erland's site at: http://www.sommarskog.se/sqlutil/aba_lockinfo.html -- Anith
From: Andrew J. Kelly on 16 Feb 2006 19:25
Do you mean it has a PK constraint defined or that it is simply unique? When you define a PK constraint it will create an index to enforce it. Maybe if you show the table DDL including all the indexes and constraints we can see just what we are dealing with. If you don't have proper indexes then sql server has no choice but to scan the table. This not only causes much more work than needed but often will escalate the lock to a table lock. Indexes are essential to proper database performance. You can not prevent locking for things like Inserts, Updates or deletes but you can do dirty reads with NOLOCK hint. Just be careful in that since it does not lock the row when reading or honor other locks you can get dirty data. -- Andrew J. Kelly SQL MVP "SQLCodder" <SQLCodder(a)discussions.microsoft.com> wrote in message news:65532689-14C8-4D61-A467-EAF9DD239B9C(a)microsoft.com... > The merchant_id field is a primary key, but dones't have an index on it. > There isn't much for indexes on the select either. There are seveal JOINs > and sub queries in the select with some tables having indexes. > > Is there any way I can prevent any locking from occuring? > > > "Andrew J. Kelly" wrote: > >> Yes it sounds like the query is taking out a table level shared lock that >> is >> preventing the update. Run sp_who2 and sp_lock to see if there is >> blocking >> and what kind of locks are being taken out. Do you have an index on >> Merchant_ID? How about for the select?. >> >> -- >> Andrew J. Kelly SQL MVP >> >> >> "SQLCodder" <SQLCodder(a)discussions.microsoft.com> wrote in message >> news:ADCB3891-B925-4628-9BF3-FADB234B9913(a)microsoft.com... >> > I'm having trouble finding the cause of the following error message: >> > >> > [Error#: -2147217871 Description: Timeout expired (Source: Microsoft >> > OLE >> > DB >> > Provider for SQL Server)] >> > >> > The error is occuring when trying to execute a simple update query: >> > >> > UPDATE merchant_billing SET billing_last_billing_date = '2/15/2006' >> > WHERE >> > merchant_id = 135485700901 >> > >> > I was thinking that the table must be locked somehow and this is not >> > allowing the update to occur. The table is included in a earlier >> > complex >> > SELECT query that returns over 5000 records. I found that if I >> > included >> > "TOP >> > 5" in this select query the error does not occur. Would it be possible >> > this >> > SELECT has not completed executing or has the table locked somehow? >> > >> > I am using Visual Basic 6 and ADO 2.7 >> > >> > Any suggestions apreaciated, thanks! >> > >> > >> > >> >> >> |