DBMS locking occurs at the record level. This level of locking granularity works well with high performance and high volume applications.
Locking is automatically used when updating or deleting records with the UPDATE or DELETE SQL statement.
When selecting records with no intent to update, CONNX issues a SQL statement in a READ ONLY transaction. This minimizes any locks placed on the data. However, if data is selected with the intention to update through the use of the SELECT .. FOR UPDATE SQL statement or by setting the SQL_CONCUR_LOCK property of the statement handle, CONNX issues a read/write transaction. An error message is returned if an attempt is made to read a record locked by another user.
Locking can be triggered when issuing a SELECT statement by setting the lock property of the statement handle to SQL_CONCUR_LOCK.
The implementation of this feature varies in each product:
Example in Visual Basic ADO:
rs.Open "select customerid from customers_dbms", conn, adOpenKeyset, adLockPessimistic
Locking for DBMS should be controlled by the use of transactions. The CONNX DBMS module supports transactions. Transactions guarantee that a complete unit of work is performed. If any part of that unit work generates an error, the complete unit can be rolled back.
By default, CONNX is in an Automatic Transaction mode. This means that each SQL statement is placed in a separate transaction.
To control the use of transaction in your application, change to Manual Transaction mode.
The implementation of this feature varies in each product:
Example in Visual Basic ADO:
Conn.BeginTrans
rs.Open "select customerid from customers_dbms", conn, adOpenKeyset, adLockPessimistic
conn.CommitTrans
Records accessed in a transaction are locked against reads and updates. The locks are released when the transaction is committed or rolled back.