======================================================================
IMPROVE YOUR MULTI-USER PERFORMANCE WITH ROW LOCKING
======================================================================
PRODUCT: R:BASE VERSION : 4.0
AREA : MULTI-USER CATEGORY: ROW LOCKING DOCUMENT#: 659
======================================================================
R:BASE has always had an excellent multi-user interface offering
flexible database and table-locking schemes, including concurrency
control for data entry and editing. R:BASE 4.0 includes row locking,
an additional multi-user feature you invoke by using two new environment
settings: EXTENDED and ROWLOCKS.
Should you use row locking? Will it improve multi-user performance? How
does it fit in with the other multi-user features of R:BASE? To answer
these questions you need to know a bit about the other R:BASE automatic
locking features.
Concurrency Control
===================
The most effective feature for data entry and editing is concurrency
control. This automatic feature allows any number of users, using either
forms or the EDIT command, to enter and edit data at the same time in
one table or in many tables. Concurrency control takes advantage of the
computer's faster-than-human speed to check whether the data user1 is
manually modifying has been changed by someone else since user1
selected it. This leaves the control of change to the data in the hands
of the last person who edits it. Concurrency control gives access of a
particular row of data to several users, not limiting access to a table
or a row of data to one user at a time. When the row of data is finally
saved to the table, R:BASE does do a quick table lock.
Interrupting Concurrency Control
================================
Not all commands can use concurrency control, which alerts the user
that someone else has changed the data. Commands like INSERT, LOAD,
UPDATE, and DELETE are not interactive (that is, the user doesn't see
the data he or she is changing), so the commands can't tell you when
someone else has changed the same data. These commands issue a table
lock to do their work of adding or editing data to a table. But R:BASE
still shows its flexibility by allowing the commands to execute even
when other users are processing using concurrency control. These commands
interrupt concurrency control and lock the table for the duration of the
change or add.
The Wait Queue
==============
Because both commands that use concurrency control (adding data, editing
data using a form, viewing data through the Info menu) and commands that
interrupt concurrency control (INSERT, LOAD, UPDATE, and DELETE) must
put a table lock on the table when they are actually adding/ deleting or
changing the data, delays can occur when many users are working in the
database. When they do, they are accompanied by messages like "Waiting
in lock queue for a required resource". Users must wait to get their
table lock for adding or changing data, and only five users can wait at
a time.
ROWLOCKS Removes the Wait Queue
===============================
By setting ROWLOCKS ON, the same group of commands that locked the whole
table to update rows now locks only the row being processed. Users
working on other rows in the same table are not affected and do not see
"Waiting in lock queue" messages. They won't have to wait for a table
lock to be released, and performance is improved. Because no table lock
is set, no wait queue for that table lock exists and thus the five-user
queue limit is removed. Commands that use concurrency control always
use row locking when ROWLOCKS is set ON. When you add a new row to a
table, however, even with ROWLOCKS ON, a quick table lock, necessary to
update the table and pointer information, is still set.
Note that a DECLARE CURSOR routine doing UPDATEs with ROW-LOCKS ON still
displays some waiting messages. That's because the DECLARE CURSOR
routine locks the sysrules table. It's the cursor that's causing the
wait messages, not the UPDATE.
ROWLOCKS and EXTENDED
=====================
You can use row locking only when EXTENDED is set ON. When you SET
EXTENDED ON, ROWLOCKS is automatically set ON. If one user connects the
database with EXTENDED ON to use row locking, all users must connect the
database with EXTENDED ON. With EXTENDED ON, earlier versions of R:BASE
cannot connect to the database. One workstation can be executing code
with ROWLOCKS OFF while the others have ROWLOCKS ON, but this is not
recommended.
When Is Row Locking Faster?
===========================
There are some considerations to keep in mind when using ROWLOCKS. One
or many rows are updated, depending on the WHERE clause conditions of
the command. If you execute an UPDATE command that changes one row, for
example, only that one row is locked when the update is made, not the
whole table. If you execute an UPDATE that changes several rows, each
of the rows is individually locked as it is updated.
The following command locks only the row WHERE empid = 103:
UPDATE employee SET empext = (empext+1) WHERE empid = 103
Other workstations accessing other rows in the employee table will not
experience the table being locked or wait messages.
The following command might be slower with ROWLOCKS ON than with it OFF
because it has to lock and unlock each row as it updates them in the
table:
UPDATE employee SET empext = (empext+1) WHERE empid +
BETWEEN 100 AND 1000
With ROWLOCKS OFF, the table is locked for the duration of the UPDATE
command. Other users must wait until this entire command is done; no one
else can do anything on the employee table until the update is finished.
The update might execute more slowly with ROWLOCKS ON, but other users
can add and modify data in the employee table while this large update is
being run.
You'll need to evaluate the use of your system to determine whether
ROWLOCKS can benefit your application. You might want to use ROWLOCKS
solely because it removes the lock queue restriction. In normal usage,
users typically notice fewer "Waiting" and "Resource unavailable"
messages with ROWLOCKS ON. Because R:BASE 4.0 is considerably faster
than previous versions of R:BASE, the actual performance differences
between having ROWLOCKS set ON and having it set OFF might not be
noticeable except for the greater throughput and increased efficiency.
Many R:BASE commands are not affected by the ROWLOCKS setting, and
several commands have to lock tables and the database regardless of the
setting. For a discussion of the other table and database locking
commands in R:BASE, see chapter 5 of the Reference Manual for more
information on ROWLOCKS and EXTENDED, see the Command Dictionary and
the Guide to Software Installation and New Features.