R:BASE 10.5 and higher versions include a new (actually separate) PAGELOCK operating condition, which specifies how R:BASE locks data in a multi-user environment, based on the position of the row in the data file?
Previously, the R:BASE operating condition was controlled by QUALCOLS as a dual-functioning setting, specifying unique columns for ODBC tables and for page locking. Now page locking (PAGELOCK) is separate from ODBC unique columns (QUALCOLS).
What does this mean for R:BASE users?
The application code must be updated to add PAGELOCK and set the condition to ON/OFF appropriately where QUALCOLS was used for page locking. Examples below demonstrate the setting could also be used dynamically in case-by-case instances to increase multi-user performance.
To summarize the code changes, if you used SET QUALCOLS 10 for faster page locking, add SET PAGELOCK OFF to your code. If you used SET QUALCOLS 2 to enforce a page lock for a faster UPDATE/DELETE process, add SET PAGELOCK ON in your code.
PAGELOCK specifies how R:BASE locks data when updating and deleting rows.
The settings for PAGELOCK are:
ON - R:BASE uses page locking or row locking as appropriate. When PAGELOCK is ON and two or more users are updating rows within the same page of data, R:BASE only lets the first user update rows--the other users are locked out until the first user's update has been completed.
OFF - R:BASE uses a fast row-locking method where only row locking is applied with no page locking. When PAGELOCK is OFF, you can lock rows of data instead of locking a page of data. You increase multi-user performance when PAGELOCK is OFF. And even more so when STATICDB and FASTLOCK are on.
If you know that your application mainly updates or deletes data a row at a time, rather than many rows, set PAGELOCK to OFF for row locking. In this case, R:BASE locks a row, reads the row, makes the change, and then releases the row. Otherwise, set PAGELOCK ON for page locking when you are doing an UPDATE and/or DELETE affecting many rows in a table.
Keep in mind that the PAGELOCK setting can be changed dynamically and can be different for different users using the same database.
Technically, the most efficient and fastest method for updating data in multi-user environment is to SET STATICDB ON, SET FASTLOCK ON, and SET PAGELOCK OFF. This particular combination will result in the fewest contentions between users.
Notes:
•FASTLOCK and PAGELOCK can be set on at the same time.
•Setting STATICDB and FASTLOCK to ON (in that order), with PAGELOCK set to OFF will significantly increase multi-user performance with individual row changes.
•PAGELOCK is not the same as SET ROWLOCKS.
•Setting the value of PAGELOCK does not change the setting of ROWLOCKS.
•The PAGELOCK setting can be changed dynamically and can be different for different users using the same database.
Example:
-- The UPDATE must alter the values for many rows
SET FEEDBACK ON
SET PAGELOCK ON -- use page locking
UPDATE <tablename> SET <columnname> = value -- no WHERE Clause
SET PAGELOCK OFF -- use row locking
SET FEEDBACK OFF
CLS
