|
database
newsgroups
|
|||||||||||||||||||||||
|
|||||||||||||||||||||||
Timeout expired[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! 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?. -- Show quoteAndrew J. Kelly SQL MVP "SQLCodder" <SQLCod***@discussions.microsoft.com> wrote in message news:ADCB3891-B925-4628-9BF3-FADB234B9913@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! > > > 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? Show quote "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" <SQLCod***@discussions.microsoft.com> wrote in message > news:ADCB3891-B925-4628-9BF3-FADB234B9913@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! > > > > > > > > > >> 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 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. -- Show quoteAndrew J. Kelly SQL MVP "SQLCodder" <SQLCod***@discussions.microsoft.com> wrote in message news:65532689-14C8-4D61-A467-EAF9DD239B9C@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" <SQLCod***@discussions.microsoft.com> wrote in message >> news:ADCB3891-B925-4628-9BF3-FADB234B9913@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! >> > >> > >> > >> >> >> Adding the NOLOCK hint did prevent the error from occuring and the
application seems to be executing correctly now. Because of the nature of the application I'm not concerned with "dirty reads" since they shouldn't occur. This application is the only one that modifies these tables and only runs twice a month on a scheduled task. One thing I didn't mention is that the application was originally using ODBC rather than ADO. This is the only change to the application that I have made and since then the timeout has occured. Do you know what the difference between ODBC and ADO would be to cause this? Do ODBC recordsets always basically perform a NOLOCK query? Thanks for the help here, much apreciated. Show quote "Andrew J. Kelly" wrote: > 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" <SQLCod***@discussions.microsoft.com> wrote in message > news:65532689-14C8-4D61-A467-EAF9DD239B9C@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" <SQLCod***@discussions.microsoft.com> wrote in message > >> news:ADCB3891-B925-4628-9BF3-FADB234B9913@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! > >> > > >> > > >> > > >> > >> > >> > > > You might check the transaction isolation level that you are using with ADO
connections. If you are defaulting to Serializable you will have an issue. It should be read committed. You can use profiler to see how you connect and what commands are being sent. Don't just assume that NOLOCK fixes your problem. If that got rid of the timeout it meant you were being blocked and you should find out why. Again it probably means you don't have the right indexes somewhere. -- Show quoteAndrew J. Kelly SQL MVP "SQLCodder" <SQLCod***@discussions.microsoft.com> wrote in message news:774971BF-9F47-42DC-8D07-DE34FEAB8BDD@microsoft.com... > Adding the NOLOCK hint did prevent the error from occuring and the > application seems to be executing correctly now. Because of the nature of > the application I'm not concerned with "dirty reads" since they shouldn't > occur. This application is the only one that modifies these tables and > only > runs twice a month on a scheduled task. > > One thing I didn't mention is that the application was originally using > ODBC > rather than ADO. This is the only change to the application that I have > made > and since then the timeout has occured. Do you know what the difference > between ODBC and ADO would be to cause this? Do ODBC recordsets always > basically perform a NOLOCK query? > > Thanks for the help here, much apreciated. > > "Andrew J. Kelly" wrote: > >> 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" <SQLCod***@discussions.microsoft.com> wrote in message >> news:65532689-14C8-4D61-A467-EAF9DD239B9C@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" <SQLCod***@discussions.microsoft.com> wrote in message >> >> news:ADCB3891-B925-4628-9BF3-FADB234B9913@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! >> >> > >> >> > >> >> > >> >> >> >> >> >> >> >> >>  |
|||||||||||||||||||||||
Other interesting topics