Neoview Control Query Default (CQD) Reference Guide (R2.5)

TABLELOCK
Transaction control and lockingCategory
Specifies whether to use table locks for subsequent DML access to tables.Description
To indicate whether the system determines when table locks are
to be used for accessing the table.
‘SYSTEM’
Table locks are always used.‘ON’
Table locks are not used.‘OFF’
The default value is ‘SYSTEM’.
Values
See the Neoview SQL Reference Manual, Introduction chapter, sections on Database Integrity
and Locking and Transaction Management.
In the default setting of SYSTEM, if the transaction is very long, is doing many updates or
using the REPEATABLE or SERIALIZABLE isolation and thereby acquiring many locks,
the system tries to escalate the lock to a table lock. By doing so, the system just has to
manage a single lock instead of the overhead of managing so many row level locks. The
manual discusses the threshold on when this happens. Locking many rows and attempts
at lock escalation by the system, impacts performance.
If you know that you are doing a lot of updates, there are no other concurrent updaters,
and queries that concurrently want to read the table do so using READ UNCOMMITTED,
you are much better off acquiring a lock on the table. That way you acquire a single lock
instead of a lock on every row that you update. This is a lot more efficient.
On the other hand, if you have queries that need READ COMMITTED access, or there are
other concurrent updaters, and there is no way to reduce the size of the transactions and
commit more often, then you should set table lock to OFF. This way the system does not
waste time and impact performance by trying to escalate the lock to a table lock.
Usage
Not applicableProduction usage
There is an impact on concurrency if this is set to ON, though the performance may improve
because only a single lock is acquired and the system does not have to acquire locks for
each row.
There is also an impact in that the system may be spending a lot of system resources
managing a large number of row level locks if this is set to OFF.
So both these settings should be carefully used. However, the default setting of SYSTEM
may also not be the right setting in all situations. Locking or escalation errors and large
number of lock waits being reported in EMS (file system error 35 – Unable to obtain a lock
control block, or the transaction or open lock unit limit has been reached) and the query
metrics repository, should be an indication that this setting should be looked at.
Impact
This should be considered mostly at the query level. You could consider this at the service
level if the workloads using that service level are well understood and leverage the same
setting.
Level
If you issue a CONTROL TABLE statement for the TABLELOCK or TIMEOUT option, the
specified control table value overrides the system-defined default setting.
Though not directly related INSERT_VSBB sometimes needs to be looked at as well when
reviewing the need for this setting.
Conflicts/Synergies
You should always break the transactions for updaters (inserters or loads) into smaller
transactions by committing often. There are various issues, apart from those discussed in
relation to this CQD, which can be caused by long-running update transactions. If for some
reason such transactions cannot be controlled, then you need to look at acquiring a table
lock if at all possible, in order to keep the locking overhead to a minimum.
Addressing the real
problem
Neoview Release 1.0Introduced in release
Not applicableDeprecated in release
52 Transaction Control and Locking