Login ProductsSalesSupportDownloadsAbout |
Home » Technical Support » DBISAM Technical Support » Support Forums » DBISAM SQL » View Thread |
Messages 1 to 9 of 9 total |
Error 11013 caused by alter table |
Thu, Jul 26 2007 8:12 AM | Permanent Link |
Roy Lambert NLH Associates Team Elevate | I've just resolved a problem with a site where multiple users were unable to log in simultaneously. I chopped out the following code
with TDBISAMQuery.Create(nil) do begin DatabaseName := DataLocation; SQL.Add('ALTER TABLE Companies ADD COLUMN IF NOT EXISTS _CTPS BOOLEAN AT 10, NOBACKUP;'); ExecSQL; Free; end; On my network I could open 3 copies of the app from various PCs simultaneously with no problem. Since login failed with "11013 - unable to access table companies" and this is the ONLY place where exclusive access to Companies is asked for, and chopping it out resolves the problem I'm pretty sure its the cause. Extra confusion is caused by the fact that a I sent a new version of the app which is what highlighted the problem. The previous version, with this code in allowed them to open it from 3 PC's, but one apparently ran fairly slowly. If it was something that needed a few minutes exclusive access I could understand it, but this is as people are logging on and the speed with which this should run I can't see them being able to hit it sufficiently together to cause a problem. Anyone have ideas as to what was the problem so I can avoid it in future? Roy Lambert |
Thu, Jul 26 2007 11:45 AM | Permanent Link |
Eryk Bottomley | Roy
> Anyone have ideas as to what was the problem Operating system locks hanging around longer than they should. > so I can avoid it in future? Always wrap DDL statements up in try-except blocks and disregard irrelevant exceptions (applies to any operation that could ever result in a lock conflict really). Eryk |
Thu, Jul 26 2007 11:56 AM | Permanent Link |
Roy Lambert NLH Associates Team Elevate | Eryk
>Operating system locks hanging around longer than they should. > >> so I can avoid it in future? > >Always wrap DDL statements up in try-except blocks and disregard >irrelevant exceptions (applies to any operation that could ever result >in a lock conflict really). Good advice, but why would an OS lock hang about? That's the bit that's puzzling me. There must be something on their PC's that's different to my setup and a couple of other sites and I have no idea what. Roy Lambert |
Thu, Jul 26 2007 12:33 PM | Permanent Link |
Eryk Bottomley | Roy,
> Good advice, but why would an OS lock hang about? That's the bit that's puzzling me. There must be something on their PC's that's different to my setup and a couple of other sites and I have no idea what. > Possibly an oplock related issue (buffering an exclusively locked file in OS memory after the file handle has closed) - but I'm just guessing. Bottom line is I've seen this for years on "peer-to-peer" LANS starting with NetWare 2.12 and progressing via LANTastic to WfW to WinNT/XP. Eryk |
Thu, Jul 26 2007 1:19 PM | Permanent Link |
Tim Young [Elevate Software] Elevate Software, Inc. timyoung@elevatesoft.com | Roy,
<< On my network I could open 3 copies of the app from various PCs simultaneously with no problem. Since login failed with "11013 - unable to access table companies" and this is the ONLY place where exclusive access to Companies is asked for, and chopping it out resolves the problem I'm pretty sure its the cause. Extra confusion is caused by the fact that a I sent a new version of the app which is what highlighted the problem. The previous version, with this code in allowed them to open it from 3 PC's, but one apparently ran fairly slowly. If it was something that needed a few minutes exclusive access I could understand it, but this is as people are logging on and the speed with which this should run I can't see them being able to hit it sufficiently together to cause a problem. >> You're assuming incorrectly - it is always possible for an open conflict to take place with an exclusive open. Therefore, you should either avoid the exclusive open by trying to open the table and checking for the existence of the column beforehand, or you should prepare to deal with an exception like Eryk suggested. -- Tim Young Elevate Software www.elevatesoft.com |
Fri, Jul 27 2007 2:54 AM | Permanent Link |
Roy Lambert NLH Associates Team Elevate | Tim
>You're assuming incorrectly - it is always possible for an open conflict to >take place with an exclusive open. Therefore, you should either avoid the >exclusive open by trying to open the table and checking for the existence of >the column beforehand, or you should prepare to deal with an exception like >Eryk suggested. Don't quite understand this, it doesn't matter for this case, but I feel is important for me to understand for the future. The scenario is: 1. User 1 logs on 2. code runs through the bit where it tries to alter the table - either does it because the field doesn't exist or doesn't bother because the fields already there (which is the case now so time in query c0 sec) 3. the query component is freed 4. 5 or 10 mins later the second person comes along and tries to login but can't because companies table is locked Does this mean there is never any guarantee that an exclusive lock will be released or is there some other action I have to take to make sure its released? In this particular case the only way to deal with the exception is to prevent login - the companies table has to be accessible or the app won't work! Whilst its prettier to provide a nice error message and shut down the app the consequence is the same - it becomes unusable. I appreciate that again in this case I can check to see if the field is there before running the sql (strange I thought that was the logic behind IF NOT EXISTS - and yes I know I have to run the sql for that to work but why should I have to do things twice?) but if one operation which terminates successfully fails to release the exclusive lock why shouldn't others fail to do so? What, if anything, can be done? Roy Lambert |
Fri, Jul 27 2007 4:02 AM | Permanent Link |
Eryk Bottomley | Roy,
> 1. User 1 logs on > 2. code runs through the bit where it tries to alter the table - either does it because the field doesn't exist or doesn't bother because the fields already there (which is the case now so time in query c0 sec) > 3. the query component is freed > 4. 5 or 10 mins later the second person comes along and tries to login but can't because companies table is locked Has "User 1" logged out at this point? If not then ALTER TABLE statements are bound to fail because "User 1" probably has the table active in open/shared mode. > In this particular case the only way to deal with the exception is to prevent login - the companies table has to be accessible or the app won't work! Whilst its prettier to provide a nice error message and shut down the app the consequence is the same - it becomes unusable. There may be logic I'm not aware of in play, but it seems to me you should be able to handle this condition with code along the lines of (untested): try with TDBISAMQuery.Create(nil) do try DatabaseName := DataLocation; SQL.Add('ALTER TABLE Companies ADD COLUMN IF NOT EXISTS _CTPS BOOLEAN AT 10, NOBACKUP;'); try ExecSQL; except //Do nothing - table is probably already open which means //that the ALTER was already performed by the previous user //(or else we'll deal with the problem in a moment) end; finally Free; //<< Original version leaked memory. end; finally repeat try CompanyTable.Open; Break; except //OK, so either an ALTER is still running or it is something a bit //nastier. Parse the exception and either Sleep/Loop for a retry //or raise/Break if the problem is permanently fatal. end; until False; end; > I appreciate that again in this case I can check to see if the field is there before running the sql (strange I thought that was the logic behind IF NOT EXISTS - and yes I know I have to run the sql for that to work but why should I have to do things twice?) but if one operation which terminates successfully fails to release the exclusive lock why shouldn't others fail to do so? Not if the table is already opened Exclusively you can't - but in this case it probably isn't Eryk |
Fri, Jul 27 2007 5:57 AM | Permanent Link |
Roy Lambert NLH Associates Team Elevate | Eryk
>Has "User 1" logged out at this point? If not then ALTER TABLE >statements are bound to fail because "User 1" probably has the table >active in open/shared mode. No, and that is an incredibly good point. Looking at the code I don't understand how its ever worked unless the ALTER TABLE doesn't try and grab exclusive access because the IF NOT EXISTS kicks in and the query is "aborted". That means its got to be something else causing the problem and I'm voting for the good old standby - AV s/w. I think I'll go and ask a few more questions. Thanks for pointing out the "bleeding obvious" Roy Lambert |
Fri, Jul 27 2007 8:47 AM | Permanent Link |
Roy Lambert NLH Associates Team Elevate | Much srceaming, stamping of feet and threats later I am now told "Sophie's pc had the anti-virus upgraded last week".
Interestingly enough Sophie was also complaining about her pc running slow ..... Roy Lambert |
This web page was last updated on Sunday, May 19, 2024 at 08:46 AM | Privacy PolicySite Map © 2024 Elevate Software, Inc. All Rights Reserved Questions or comments ? E-mail us at info@elevatesoft.com |