Technical Information Database

TI265B.txt   Sharing Violation Error with Paradox Tables
Category   :General Programming
Platform   :All
Product    :BDE  All


Description:

This error is most commonly caused by a "Lock File
Contention".  A lock file contention occurs in this 
situation:

   One user, User "A", is accessing one or more Paradox
tables in a directory.  "A" is closing the last table 
in the directory.  The BDE detects that "A" is the last 
program accessing the tables in the directory.  Since 
"A" is the last user on the tables, the .LCK files are 
going to be deleted.  At this time, User "B" attempts 
to open a table in the same directory as "A" (who is 
about to delete the .LCK files).  "B" opens the .LCK 
files to write an entry to.  This is when "A" tries to 
delete the .LCK files that "B" has now opened.  This 
will cause a sharing violation on the .LCK files.  The 
share violation usually occurs on the last client to 
close the table.

Solutions:

   One solution is to override Windows error routines 
and ignore the error.  In all test cases, then a 
sharing violation occurs, the table is still opened or 
closed.
   Another solution is to keep the .LCK files in the 
table's directories.  If the BDE detects that more 
than one Application (or Session) is using the tables, 
it will not attempt to delete the .LCK files therefore 
solving the problem.
  If the BDE and your Paradox table all reside on the
same CPU another solution is turn off Local share in BDE
Adminstrator. If Local Share is off .LCK files will not
be created in the first place, so a sharing violation
should not occur. Note that if tables are on a different
CPU than the BDE setting Local Share has no significance
because .LCK files are always created on remote drives.

1)  C / C++ / Delphi / Paradox:
Use the Windows API SetErrorMode passing the 
SEM_FAILCRITICALERRORS constant.  You would only need 
to do this before opening and closing a table and 
then check to see if the table has actually been open.  
Please see Routine 1a and 1b below for a basic Delphi 
(any version) routine or Routine 2a and 2b for 
C / C++ routines.  Paradox users will need to call
into the appropriate DLL where SetErrorMode resides.
 

2)  C / C++ / Delphi / Paradox:
Create a "Dummy" Paradox table in each table 
directory that the application uses. Example:  
executable is in directory C:MyProg.  This executable 
opens tables in two different directories - C:MyTables 
and D:TempTbl.  Create a "Dummy" table with the
Database Desktop, or any other Paradox table creation 
utility, in both the C:MyTables and D:TempTbl 
directories.  Once the tables are created, create an 
application that opens each of the "Dummy" programs at 
startup.  Leave this program running at all times.  If 
the executable can be placed on a server machine, like 
Windows NT, that is optimal.  In this scenario,  
whenever applications are accessing the data, the 
server also has the "Dummy" tables open.  


3)  C / C++ / Delphi / Paradox:
Leave at least one table during the entire 
application's run.  Open one table during startup and 
close the table at exit.  You could even open a 
"Dummy" table described above.


4)  C / C++ / Delphi:
Use DbiAcqPersistTableLock BDE function to create 
a lock on a table that does not exist (Delphi or C++ 
only).  

Syntax: 
(Delphi) Check(DbiAcqPersistTableLock(Database1.Handle, 
'NoTable1.DB', szPARADOX));  

(C++) rslt = DbiAcqPersistTableLock(hDb, 'NoTable2.DB', 
szPARADOX);

NOTE: Each instance of the application must have a 
unique, non-existent table name or an attempt to place 
a lock in the directory will fail.  If this method is 
used, an algorithm must be used to guarantee a unique 
table name.  If a user can only run one instance of 
the program at a time, the network user name can be 
used as a table name.


5)  Paradox ObjectPAL:
If your application is apt to cause .LCK files to be 
created and deleted often, the easiest (and least 
resource-intensive) way to prevent this is to place a 
read lock on a non-existent table within the directory 
when your application starts up.  (Paradox allows us 
to place semaphore locks on tables that don't exist.)  
Since read locks don't conflict with one another, all
users can do this, and the net result will be that the 
.LCK file will not be deleted until the last user 
exits the system.

Syntax: 
At program startup:
Table.attach("DummyTbl.db")
Table.lock(Read)

At program shut-down:
Table.unlock(Read)
Table.unAttach



NOTES - These are observations on the problem:

1)  The problem does not seem to occur on NT 
workstation machines.  The application can be either 
16 or 32 bit.  SetErrorMode seems to work with both 
cases.


2)  The problem is more likely to occur if the 
network protocol is netBEUI.  If possible use netBIOS 
or IPX/SPX.


3)  Constant closing and opening of tables will cause 
this error more often.


4)  The error most commonly occurs on the close of the 
table, not the open.  In this situation the closing 
application tries to delete files that another 
application now has open.


Example Routines:

Routine 1a)  (Delphi Table Open Routine)
----------------------------------------
procedure TForm1.OpenTable(MyTable: TTable);
begin
  { Set the error mode to ignore critical errors }
  SetErrorMode(SEM_FAILCRITICALERRORS);
  { Open the table and check if it was opened }
  MyTable.Open;
  if MyTable.Active = False then
  begin
    { Retry opening the table }
    MyTable.Open;
    { If an error happens again, raise an exception }
    if MyTable.Active = False then
      raise 
        EDatabaseError.Create('Error Opening table');
  end;
  { Set the error mode back to the default }
  SetErrorMode(0);
end;


Routine 1b)  (Delphi Table Close Routine)
-----------------------------------------
procedure TForm1.CloseTable(MyTable: TTable);
begin
  { Set the error mode to ignore critical errors }
  SetErrorMode(SEM_FAILCRITICALERRORS);
  { Close the table and check if it was closed }
  MyTable.Close;
  if MyTable.Active = True then
  begin
    { Retry closing the table }
    MyTable.Close;
    { If an error happens again, raise an exception }
    if MyTable.Active = True then
      raise 
        EDatabaseError.Create('Error Closing table');
  end;
  { Set the error mode back to the default }
  SetErrorMode(0);
end;


Routine 2a)  (C / C++ Table Open Routine)
-----------------------------------------
DbiResult OpenTable(hDBIDb hTmpDb, pCHAR szTblName,
               phDBICur phTmpCur)
{
   DBIResult   rslt;

   // Set the error mode to ignore critical errors
   SetErrorMode(SEM_FAILCRITICALERRORS);

   // Open the table and check if it was opened
   rslt = DbiOpenTable(hTmpDb, szTblName, NULL, 
      NULL, NULL, NULL, dbiREADWRITE, dbiOPENSHARED, 
      xltFIELD, FALSE, NULL, phTmpCur);
   if (rslt != DBIERR_NONE)

      // Retry opening the table
      rslt = DbiOpenTable(hTmpDb, szTblName, NULL, 
         NULL, NULL, NULL, dbiREADWRITE, dbiOPENSHARED, 
         xltFIELD, FALSE, NULL, phTmpCur);

   // Set the error mode back to the default
   SetErrorMode(0);
   return rslt;
}


Routine 2b) (C / C++ Table Open Routine)
----------------------------------------
DBIResult CloseTable(phDBICur phTmpCur)
{
   DBIResult   rslt;

   // Set the error mode to ignore critical errors
   SetErrorMode(SEM_FAILCRITICALERRORS);

   // Close the table and check if it was closed
   rslt = DbiCloseCursor(phTmpCur);
   if (rslt != DBIERR_NONE)

      // Retry closing the table
      rslt = DbiCloseCursor(phTmpCur);

   // Set the error mode back to the default
   SetErrorMode(0);
   return rslt;
}


Reference:


7/15/98 3:24:24 PM