BDE and Database Desktop Locking Protocol (TI3160)

BDE and Database Desktop Locking Protocol (TI3160)

Intended Audience This information will be of benefit to anyone considering designing a database application using Delphi and the BDE. Prerequisites A basic knowledge or interest in Paradox locking protocols and table formats. Purpose To give users a better understanding of the table locking protocol. Table and field types and features supported Each major release of Paradox has implemented improvements to table structures since version 2.0. All Paradox table types from Paradox 1.0 through Paradox 3.5 are compatible with each other. Paradox 4.0 adds a new data type to the table format: Binary Large Objects, commonly known as BLObs and new types of Secondary Indices. Paradox 4.0 supports two types of BLOb fields: Memo, and BLOb. Versions of Paradox prior to 4.0, or the Engine prior to 3.0, cannot read, write, or create this new table format. If you attempt to read or write a Paradox 4.0 table type in an earlier version of Paradox, it will return an error that the table is password protected. Paradox 5.0 added several new data types to the table format: Long Integer, Time, TimeStamp, Logical, Autoincrement, BCD, Bytes. Paradox 7.0 added a descending secondary index. Any table created or modified to include any of these newer features will default to the respective table level. The default table type created using the Database Desktop and the BDE (Borland Database Engine) is a Paradox 4.0. Although the default can be changed in the BDE configuration utility or the Database desktop configuration utility to default to Paradox 3, 4, 5 and 7 for the BDE. Paradox 4.0 can read, write, and create Paradox table types that are compatible with Paradox 1.0 through Paradox 4.0. So a table created in Paradox 1.0 is compatible with Paradox 4.0. A table created in Engine 1.0 or 2.0 can be read by or written to by Paradox 4.0. Paradox and the Engine do not change the table type when reading or writing. The table type is only changed when a Restructure is performed on the table. Paradox Locking Protocols There are two different Paradox locking protocols: the protocol introduced with Paradox 2.0 and the protocol introduced with Paradox 4.0. These two protocols are not compatible with each other. The locking protocol has no bearing on which type of table a program can work with. There are a few programs that can support either locking protocol; however, these programs can only support one protocol at a time. We will only focus on the 4.0 locking protocol. Database Desktop/ Paradox 4.0 Locking Protocol The Paradox 4.0 locking protocol is the only protocol available for Paradox 4.0 and the IDAPI Engine. The designation "Paradox 4.0 locking protocol" represents this style of locking. Directory Locks Paradox 4.0 places a locking file, PDOXUSRS.LCK, in each directory where tables are being accessed. This locking file regulates concurrent access to files in the directory. The lock file references PDOXUSRS.NET, so every user must map to the data directory the same way. It also places an exclusive PARADOX.LCK file in the directory as well. It does this to prevent versions of Paradox or the Engine using the older locking system from inadvertently accessing tables. Working/Shareable Directories When Paradox or Database Desktop needs to access tables in a directory, they place a "Shareable" PDOXUSRS.LCK file in that directory and an "Exclusive" PARADOX.LCK file in that directory. This designation means that other Paradox 4.0 users can access tables in that directory. The exclusive PARADOX.LCK file is placed in this directory to keep the older, incompatible locking protocol from putting data at risk. In Paradox, this is known as a "Working" directory. Private/Exclusive Directories Paradox and Database Desktop also need a directory to store temporary files, such as the Answer table from a query. When Paradox or Paradox Runtime start, they also place an "Exclusive" PDOXUSRS.LCK file in a directory and an "Exclusive" PARADOX.LCK file in the same directory, designating that directory as the location for temporary files. This designation means that other Paradox users cannot access tables in that directory. In Paradox this is known as a "Private" directory. Table Locks Paradox 4.0 places each table lock in the directory locking file: PDOXUSRS.LCK. It no longer uses the separate table lock file of previous versions. For example, if three users are viewing the CUSTOMER.DB table and one user is restructuring the ORDERS.DB table, the PDOXUSRS.LCK file will have a shareable lock listed for each of those three users who are viewing the CUSTOMER.DB table, and an exclusive lock on ORDERS.DB for the user who is restructuring that table. Paradox 4.0 Locking Protocol Concurrency In a multi-user environment, the Paradox 4.0 locking protocol maintains concurrency, the simultaneous use of applications, through the PDOXUSRS.NET file. All users who want to share Paradox tables must map to the same PDOXUSRS.NET file in the same way using the same path, but not necessarily the same drive letter. Paradox places a PDOXUSRS.LCK and an exclusive PARADOX.LCK file in each directory where tables are being accessed preventing previous versions of Paradox from accessing files in the same directory. Each user who wants to share tables in that directory must map that directory the same way using the same drive letter and path. Paradox then places all of the locking information for that table in the PDOXUSRS.LCK file, reducing the number of files needed. Network Control File The Paradox network control file, PDOXUSRS.NET, serves as the reference point for all lock files created by Paradox. The net file contains the users currently using the BDE and which table they're accessing. Each lock file references the network control file and contains information regarding the locks on the table and by which user, so each user must map to the same network control file in the same way, but not necessarily with the same drive letter. For example, if you are using volume DATA on server SERVER_1 and the network control file is in the directory /PDOXDATA each user must map //SERVER_1/DATA:/PDOXDATA the same way, however, each user should, but is not required to use the same drive letter. If the network you are using does not have volumes, then DATA would be a directory off the root of SERVER_1. If you are mapping //SERVER_1/DATA to the root of drive P: then each Paradox system would specify the location of PARADOX.NET as P:/PDOXDATA/. However, other users could map //SERVER_1/DATA to the root of drive O: and specify O:/PDOXDATA/ as the location of the network control file. Configuring 16 bit Database Engine / IDAPI.CFG The Database Engine configuration file holds the network specific information and the list of database aliases, as well as other information. You can configure IDAPI using the Database Engine configuration program, BDECFG.EXE, to set the location of the network control file. Also add, delete, modify database aliases including which driver or type of alias used and whether IDAPI will share local tables with other programs using the Paradox 4.0 locking protocol as well as some other specifics regarding the tables and how data is displayed. Local Settings 16 bit The WIN.INI file holds the locations of the IDAPI.CFG file, the Database Desktop "Working" directory, and the Database Desktop "Private" directory. You can use any text editor to change these designations in the WIN.INI file. The location of the IDAPI.CFG file is CONFIGFILE=<full drive, path, and file name> or CONFIGFILE01=<full drive, path, and file NAME> in the [IDAPI] group. The locations of the Database Desktop "Working" and "Private" directories are in the [DBD] group as WORKDIR=<full drive and directory>, and PRIVDIR=<full drive and directory>. Configuring 32 bit Database Engine / IDAPI32.CFG The BDE configuration file holds the same information as the Database Engine configuration file. Use the BDE Configuration, BDECFG32.EXE, to configure IDAPI32.CFG. Optionally you can store the information in the registry or in both the registry and IDAPI32.CFG. Local Settings 32 bit The registry holds the locations of the IDAPI32.CFG, the "Working" directory, and the "Private" directory. The location of the IDAPI32.CFG file is stored in HKEY_LOCAL_MACHINE/Software/Borland/Database Engine. The value CONFIGFILE01 holds the data containing <full drive, path, and file name>. The location of the BDE "Working" and "Private" directories are stored in HKEY_CURRENT_USER/Software/Borland/DBD/7.0/Configuration/WorkDir and HKEY_CURRENT_USER/Software/Borland/DBD/7.0/Configuration/PrivDir respectively. Each directory default value stores the data containing <Full drive and directory>. Accessing a Paradox Table The BDE will first try to access the PDOXUSRS.NET file. If a PDOXUSRS.NET file is not found, Paradox will create a new PDOXUSRS.NET file and continue with the startup procedure. If the PDOXUSRS.NET file is found but the owner of this net file used a different path, i.e. mapped to the server differently, an exception of "Multiple net files in use" will be raised and the BDE will shutdown. After the net is successfully opened an exclusive lock, PARADOX.LCK, is placed in the temporary, private, directory. If it fails to place the lock the BDE will shut down. This can fail if some other user has an exclusive lock in this directory or the lock files are using different net files. After it secures a directory for private use it will place a shareable PARADOX.LCK in the working directory and now Initialization is complete.