Locking and Multi-User Access

Delphi Database, Delphi Components from ComponentAce

Locking and Multi-User Access

Previous Top Next

Absolute Database is designed to do simplify development of multi-user database application as much as possilbe.
Locking is performed only when database is open in multi-user mode. To gain multi-user access to a database, set TABSDatabase.MultiUser to True before opening a database file.

Locking

Absolute Database uses locking to ensure transactional integrity and database consistency. Locking prevents users from reading data being changed by other users, and prevents multiple users from changing the same data at the same time. If locking is not used, data within the database may become logically incorrect, and queries executed against that data may produce unexpected results.

How Absolute Database Engine Performs Locking

Absolute Database acquire locks using calls to the operating system, which could be Windows 95/98/ME/NT/2000/XP. If database file is placed on file sever and accessed via network, these calls are handled by the client operating system to the file server's operating system, which could be Netware, Windows NT, Windows 95/98/ME, etc.
MaxConnections property of TABSDatabase allows to adjust the number of maximal users the database engine can handle simultaneously.This number should be specified before the database file creation to allocate locking area in a database file.
Set MaxConnections so that it is high enough to avoid exceptions when the large number of users are connecting to the database, but low enough not to degrade performance too severely by forcing database engine to check too many simultaneous connections. The larger value of MaxConnections also causes the larger size of a database file.

Locking Architecture

Absolute Database uses locks to implement pessimistic concurrency control among multiple users performing modifications in a database at the same time. Database engine manages both transactions and locks on a per connection basis. For example, if an application opens two database connections using two TABSDatabase components, locks acquired by one connection cannot be shared with the other connection. Neither connection can acquire locks that would conflict with locks held by the other connection.

Absolute Database locks are applied at various levels of granularity in the database. Locks can be acquired on rows, tables, or databases. Absolute Database dynamically determines the appropriate level at which to place locks for each operation. The level at which locks are applied does not have to be specified by users and needs no configuration by administrator. Each instance of Absolute Database engine ensures that locks granted at one level of granularity respect locks granted at another level. For example, if User1 attempts to acquire an update lock on a row, the instance of Absolute Database also attempts to acquire intent update lock on the table. If User2 has an exclusive lock at table level, User1 is blocked from acquiring locks until the lock held by User2 is freed.

Record Locks

Absolute Database engine uses record locks to prevent multiple users or threads from editing, deleting or posting the same record at the same time. Record lock blocks other attempts of locking this record, but do not block read of the locked record.

Record locking is important in the following opearations:

Inserting a record	When calling Insert/Append method of TABSTable or TABSQuery, nothing is locked until Post method is called. While posting a new record only intension table lock is acquired to prevent table from simultaneous postings of another users. If table lock fails, then a ABS_ERR_TABLE_LOCKED error will be triggered by the database engine. You can handle this error using TABSTable.OnPostError, see Absolute DB Reference and source code of DBManager utility for more details.
Updating a record	When calling Edit method of TABSTable or TABSQuery, database engine attempts to acquire intension table lock and then record lock and then database engine checks if the record was not modified or deleted by another user since last read. If table lock fails, then a ABS_ERR_TABLE_LOCKED error will be triggered by the database engine. If record lock fails, then a ABS_ERR_RECORD_LOCKED error will be triggered by the database engine. If record was modified since last read, then a ABS_ERR_UPDATE_RECORD_MODIFIED error will be triggered by the database engine. If record was deleted since last read, then a ABS_ERR_UPDATE_RECORD_DELETED error will be triggered by the database engine. You can handle ths error using TABSTable.OnEditError, see Absolute DB Reference and source code of DBManager utility for more details.
Deleting a record	When calling Delete method of TABSTable or TABSQuery, database engine attempts to acquire intension table lock and then record lock and then database engine checks if the record was not modified or deleted by another user since last read. If table lock fails, then a ABS_ERR_TABLE_LOCKED error will be triggered by the database engine. If record lock fails, then a ABS_ERR_RECORD_LOCKED error will be triggered by the database. If record was modified since last read, then a ABS_ERR_DELETE_RECORD_MODIFIED error will be triggered by the database engine. If record was deleted since last read, then a ABS_ERR_DELETE_RECORD_DELETED error will be triggered by the database engine. You can handle ths error using TABSTable.OnDeleteError, see Absolute DB Reference and source code of DBManager utility for more details.

Table Locks

Absolute Database engine acquires table locks internally and determines the appropriate level at which to place locks for each operation.
There are several lock modes: shared, update, exclusive and intent. The lock mode indicates the level of dependency the connection has on the locked object. Absolute Database controls how the lock modes interact. For example, an exclusive lock cannot be obtained if other connections hold shared locks on the resource.

Lock Time-out

Locks are held for the length of time needed to protect the resource at the level requested.
If a connection attempts to acquire a lock that conflicts with a lock held by another connection, the connection attempting to acquire the lock is blocked until:

·	The conflicting lock is freed and the connection acquires the lock it requested.

·	The time-out interval for the connection expires.

The default time-outs: 1.5 second for record level locking, 90 seconds for table locking.
Registered users of Absolute Database with source code can customize locking time-outs.

Dec 03, 2024