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

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

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

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

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

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

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

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

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