CPORDLS (Order Serial/Lot File) Btrieve Page Size
For any Btrieve table, the page size can be from 512 bytes to 16,384 bytes as follows:
- File Format 7 - 9.0: 512 bytes - 8192 bytes
- File Format 9.5: 1024 bytes - 16384 bytes
Default Page Size
Generally speaking, you don't need to be concerned with the Btrieve page size. Elliott chooses 4,096 as the default page size to achieve a balance between performance and locking. However, there is an exception to this rule, which we will try to explain with this document.
Page Locking
When the Elliott application reads a record with a lock, it uses record locking. That is to say, when there are multiple records in a data page, only the record that's locked is locked and users can still access other records on the same data page. So then what is page locking for? As far as we are aware, page locking can take place in the following situations:
- Index Page Update: In a Btrieve file, there's also the index page. When Btrieve updates a record, it needs to update both the data page and the index page. When it updates the index page, it will temporarily lock it. Therefore, it is possible for another user to receive a lock message due to an index page lock, even though this user is updating a different record or the record may be on a totally different data page.
- Transaction: In various different places, Elliott uses the PSQL transaction capability to ensure data integrity. For example, when Elliott invoices before a particular invoice starts posting, Elliott declares the start of the transaction with the PSQL engine. After that invoice is posted, Elliott declares the end of the transaction with PSQL and proceeds to the next invoice. If, for whatever reason, the posting stops in the middle of the process and after the Elliott task is closed, when Elliott starts up again and connects to PSQL, the engine will know that the transaction has been terminated abnormally, thus reversing the database state back to right before the invoice started posting. This is a wonderful feature. However, when PSQL is in a transaction, the engine has to prepare to roll back the update, so it produces extensive locks on data and index pages that have been changed to prevent a roll back conflict. Therefore, there is a much greater chance of locking occurring during a transaction.
In some Elliott installations with shipping verification, especially with users choosing to perform billing selection after shipping verification, we have noticed an increase in CPORDLS table locking conflicts. The main reason behind this is because:
- CPORDLS is a small table which means: (1) The table does not have a lot of records; and (2) The record size is small so it translates to more records per data or index page.
- During the billing selection in shipping verification, Elliott will start a transaction before it begins and end the transaction when it is done with billing selection. If the user uses serialized items, then Elliott has to write to the CPORDLS table. Due to the transaction reason, PSQL has to protect the data and index pages that just got updated in CPORDLS by using locks.
- If another user is also perform shipping verification and billing selection at the same time on a different order, there's a possibility of a locking conflict happening, and we often see the CPORDLS table show up.
Dead Lock Condition
However, sometimes the locking conflict may result in a dead lock condition like the following:
- On the first user's screen: "2nduser locked CPORDLS...."
- On the second user's screen: "1stuser locked CPORDLS...."
If you have other users performing shipping verification and billing selection at the same time, you may receive a locking message similar to the one shown above.
This type of dead lock condition can happen in any kind of database. It can't be resolved until one user is being selected as the victim and terminated. In Elliott, this can be done by using the PSQL Monitor Utility to close the dead lock victim's user session. The good news is because the user is in the middle of a transaction when termination takes place, the data integrity remains intact. But it is a nuisance when this happens.
Rebuild CPORDLS Page Size to 512 Bytes
To reduce the chances of a dead lock conflict with CPORDLS, we suggest that you rebuild the CPORDLS page size to 512 bytes. With a smaller page size, you have less number of records per data page, and you have less number of indexes per index page. Thus, the potential locking conflict is reduced. Since CPORDLS is a small table, the performance impact of using a small page size will be hardly noticed.
To rebuild CPORDLS page size to 512 bytes, you will need to go to your PSQL server and go to Start -> All Programs -> Pervasive -> PSQL 11 -> Utilities -> Rebuild. Once the Rebuild utility starts up, in the first page add CPORDLS.BTR file into the list. This file may, for example, have the following path M:\Elliott7\DATA\CPORDLS.BTR.
Once you have selected the CPORDLS.BTR file, choose "Next." In the next page, specify the File Format as 9.0 (because 9.5 minimum page size is 1024), and choose Page Size as 512. Then click "Next" to start the rebuild.
Keep in mind that you can't rebuild this file if your other Elliott users open this file.