Subject : Table exists routine gives lock exception

Tim and others,

Previously I have given the code for determining whether a table exists.

Here is the code :

function TableExists(const ThisDatabase : TEDBDatabase;
                     const ThisTable    : String) : Boolean;
begin
  Result := False;
  IF ( ThisDatabase.Execute(
         'SELECT * FROM Information.Tables ' +
         'WHERE Name=' + Engine.QuotedSQLStr(ThisTable)
                            )) = 1 THEN
    Result := True;
end;

In most cases this works.

In one case I get the 'ElevateDB Error #300 Cannot lock the database
DBTempMem for shared access' error.

(In my source code I always check if the database DBTempMem exists
before calling TableExists)

It seems to me that there is a problem in the ElevateDB code.

This is the chain of calls on the Call Stack.
1) first my call to TableExists
2) unit Data.DB class TCustomConnection method SetConnected line 3159
3) unit edbcomps class TEDBDatabase method DoConnect
4) unit edblocal class TEDBLocalSessionManager method OpenDatabase
5) unit edblocal class TEDBLocalDatabaseManager method LockDatabase
6) unit edblockmgr class TEDBLockManager method LockObject
7) unit System.SysUils class Exception method RaisingException line 19283
8) unit EInject method GetExceptionStackInfoHook line 391
9) unit edbconfig class TEDBLockableConfigObject method LockError

Also the help for the function 'TEDBDatabase.Execute Method' does not
give any information under the heading 'Usage' that assists me.

So what do I need to code to get a bullet proof TableExists function?

Regards,
  Peter Evans

Peter


<<In one case I get the 'ElevateDB Error #300 Cannot lock the database
DBTempMem for shared access' error.>>

This says that the database is being held open for some operation that requires exclusive access. Do you have the database open in another session or something?

Roy Lambert [Team Elevate]

On 17/09/2013 5:32 PM, Roy Lambert wrote:
> Peter
>
>
> <<In one case I get the 'ElevateDB Error #300 Cannot lock the database
> DBTempMem for shared access' error.>>
>
> This says that the database is being held open for some operation that requires exclusive access. Do you have the database open in another session or something?

Not in another session.

Regards,
  Peter Evans

On 17/09/2013 6:57 PM, Peter Evans wrote:

>>
>> This says that the database is being held open for some operation that
>> requires exclusive access. Do you have the database open in another
>> session or something?
>
> Not in another session.

I also want to know how to find out who/what has the lock. How can I do
that?

This functionality would seem to be essential for debugging purposes.

Regards,
  Peter Evans

Peter

>> Not in another session.

How about in EDBManager - an incompatible (and don't ask me to define that) session open in there will also give the 300 error

>I also want to know how to find out who/what has the lock. How can I do
>that?
>
>This functionality would seem to be essential for debugging purposes.

Looking at Configuration.LogEvents simply gives you the error. If, as I strongly suspect, its another session holding it open then there is no way of getting that error. All ElevateDB knows is it can't access the configuration database. If its a bug, which is unlikely since its just this one, again there is no way it can tell you where the lock came from.

Reading the manual I found

Exclusive File Access
You can specify that the engine open all configuration, database catalog, and table files exclusively by
setting the TEDBEngine ExclusiveFileAccess property to True. The default value for this property is False.
Setting this property to True will prevent any other process from opening or sharing any of the files
accessed by the engine, and will provide some performance benefits. This feature is its infancy, and will
be continually improved in order to further increase its performance.

Could this be the problem?

[Team Elevate]

Peter,

<< In one case I get the 'ElevateDB Error #300 Cannot lock the database
DBTempMem for shared access' error. >>

There is a current bug with in-memory configuration files that is similar to
this - are you setting the TEDBEngine.ConfigMemory property to True ?

If not, can you send me a project that reproduces this ?

Thanks,

Tim Young
Elevate Software
www.elevatesoft.com

On 18/09/2013 3:17 AM, Tim Young [Elevate Software] wrote:

>
> There is a current bug with in-memory configuration files that is
> similar to this - are you setting the TEDBEngine.ConfigMemory property
> to True ?
>

Just a quick reply for now until I investigate further.

The database is in-memory. Or, at least it should be!

The in-memory database is set up in the same Data Module as another disk
database. They share the same Session.

When I use ElevateDB Manager to look at the disk database I sometimes
see in the catalogue the in-memory database.

When I use a low level system tool to look at Windows locking a lock is
put on the disk config file.

>>If not, can you send me a project that reproduces this ?

Sorry I can't, as it is too complex.

Regards,
  Peter Evans

On 18/09/2013 3:17 AM, Tim Young [Elevate Software] wrote:
> Peter,
>
> << In one case I get the 'ElevateDB Error #300 Cannot lock the database
> DBTempMem for shared access' error. >>
>

I coded the following routine to assist in debugging. (You will have to
make subtle changes for it to work on your system.) Reading this routine
will assist in understanding the following diagnostic output.

I usually have in any one Session both disk based and in-memory databases.

Interface

function GetEngineInfo(const ToClipboard : Boolean) : String;
  {Goes to the Engine component, of which there can be only one in an
  application, and returns various information formatted in a string}

Implementation

function GetEngineInfo(const ToClipboard : Boolean) : String;
const
  sCRLF = Char(13) + Char(10);
var
  iIterate : Integer;
  jIterate : Integer;
  MySession: TEDBSession;
  Str      : String;
  StrL     : TStringList;
  StrDBL   : TStringList;
  StrTemp  : String;
begin
  StrL   := Nil;
  StrDBL := Nil;
  TRY
    Str := 'Engine version str = ' + edbcomps.ENGINE_VERSION_STR + ','
+ sCRLF +
           'Default session name = ' + edbcomps.DEFAULT_SESSION_NAME +
',' + sCRLF +
           'Default session desc = ' + edbcomps.DEFAULT_SESSION_DESC +
',' + sCRLF;
    Str := Str + ' Session count = ' +
IntToStr(edbcomps.Engine.SessionCount);
    StrL := TStringList.Create;
    edbcomps.Engine.GetSessionNames(StrL);
    StrTemp := '';
    for iIterate := 0 to (StrL.Count - 1) do begin
      if iIterate = 0 then
        StrTemp := StrL.Strings[iIterate]
      else
        StrTemp := StrTemp + ', ' + StrL.Strings[iIterate];
    end;
    Str := Str + ', Session Names = ' + StrTemp + ',' + sCRLF;
    Str := Str + 'Is config memory = ' +
             IsTrueStr(edbcomps.Engine.ConfigMemory) + ',' + sCRLF +
           'Is exclusive file access = ' +
             IsTrueStr(edbcomps.Engine.ExclusiveFileAccess) + ',' + sCRLF +
           'Is use local session engine settings = ' +
             IsTrueStr(edbcomps.Engine.UseLocalSessionEngineSettings);

    for iIterate := 0 to (StrL.Count - 1) do begin
      StrTemp := '';
      MySession := edbcomps.Engine.SessionList[StrL.Strings[iIterate]];
      StrTemp := StrL.Strings[iIterate] + ',' +
                 ' Database count = ' +
IntToStr(MySession.DatabaseCount) + ',' + sCRLF;
      FreeAndNil(StrDBL);
      StrDBL := TStringList.Create;
      MySession.GetDatabaseNames(StrDBL);

      for jIterate := 0 to (StrDBL.Count - 1) do begin
        if jIterate = 0 then
          StrTemp := StrTemp + StrDBL.Strings[jIterate]
        else
          StrTemp := StrTemp + ', ' + StrDBL.Strings[jIterate]
      end;
      Str := Str + ',' + sCRLF + 'Session = ' + StrTemp;
    end;

    if ToClipboard then
      ClipBrdManip.StrToClipBrd(Str);

    Result := Str;
  FINALLY
    FreeAndNil(StrL);
    FreeAndNil(StrDBL);
  END;
end;

Here is what is shown in a ShowMessage command after calling
GetEngineInfo :-

Engine version str = 2.13 Build 3,
Default session name = Default,
Default session desc = Auto-Created Default Session,
 Session count = 6, Session Names = Default, EDBSession1_1,
EDBSession1_2, EDBSession1_3, EDBSession1_4, EDBSession1_5,
Is config memory = F,
Is exclusive file access = F,
Is use local session engine settings = T,
Session = Default, Database count = 0,
,
Session = EDBSession1_1, Database count = 1,
SysTables,
Session = EDBSession1_2, Database count = 3,
Configuration, dummyGBTasks, dummyGBTasksMem,
Session = EDBSession1_3, Database count = 4,
Configuration, dummyGB, dummyGBMem, TEST1200100,
Session = EDBSession1_4, Database count = 4,
Configuration, dummyGBTemp, dummyGBTempMem, TEST1200100DEFAULT,
Session = EDBSession1_5, Database count = 4,
Configuration, dummyGBTemp, dummyGBTempMem, USERBASEDEFAULT


Regards,
  Peter Evans

Peter

>The database is in-memory. Or, at least it should be!
>
>The in-memory database is set up in the same Data Module as another disk
>database. They share the same Session.
>
>When I use ElevateDB Manager to look at the disk database I sometimes
>see in the catalogue the in-memory database.

That last statement suggests you are creating and destroying the in-memory database on an ad-hoc basis. There should be no need for that. Simply create it and leave it there. The tables etc need recreating each time but not the database itself.

Roy Lambert [Team Elevate]

On 18/09/2013 6:03 PM, Roy Lambert wrote:

>
> That last statement suggests you are creating and destroying the in-memory database on an ad-hoc basis. There should be no need for that. Simply create it and leave it there. The tables etc need recreating each time but not the database itself.
>

I take your point. However, what I do should in theory work.

Regards,
  Peter Evans