Re: Lock errors - your knee jerk opinions?

On Thu, 15 Jul 2004 11:27:59 -0400, sumGirl wrote: > I have a process that runs everyday, lots of short jobs usually back to > back, loading data into tables. This process has been around since our > business was tiny, but over the past few years we hacve grown alot and now > from time to time I have seen the following error messages. Each time I see > the error its always it seems like its when my data loads are larger than > normal and the process is very long running (12+ hours) and that seems to be > the only thing in common during the errors. > > I know from experieince that I can usually just resubmit the same exact job > and data and its likely to run fine on the second try. I know you dont know > my code and table structures, but whats your knee jerk opinion of whats > going on? I am a sysadmin and not a programmer, but my opinion is that the > application lacks any lock maangement logic and the fact that it runs as > well as it does normally might be the product of good luck/good timing? <SNIP> The -349 & -211 errors are related. Some process is creating or dropping objects (tables, indexes, etc) and another process wants to lookup something while the system catalog is locked. The -107 & -144 are also related. Records are being updated, deleted, inserted and another query is trying to access the locked record. SET LOCK MODE TO WAIT is the solution to all of these. The locks that arebeing described are normally transitory, lasting fractions of a second. Lock mode WAIT will wait for the locks to clear and only return a timeout error if the WAIT period expires. When you update load gets larger there's just more chance of interferring with other apps, so you do not always see the problem. Art S. Kagel