|
Category: ASE: Configuration
locking scheme
ASE provides three different locking schemes.
allpages locking scheme,
datapages locking scheme
and
datarows locking scheme.
To find out which locking scheme is used for
tables
of a
database
query the
system table
sysobjects
of the respective database. The locking scheme is stored
in the column sysstat2 encoded in a bitmap.
Locking Scheme - sysobject - sysstat2 bitmap
-
8192 - 0x2000 - Table uses
allpages locking scheme.
-
16384 - 0x4000 - Table uses
datapages locking scheme.
-
32768 - 0x8000 - Table uses
datarows locking scheme.
Query to determine the locking scheme of all tables in a database
A query on sysobjects could look as follows. Issue use database
before executing the query.
select
name
, 'table uses...' = case (sysstat2 & 57344)
when 8192 then 'allpages locking scheme'
when 16384 then 'datapages locking scheme'
when 32768 then 'datarows locking scheme'
end
from sysobjects where type = "U"
Changing the locking scheme
Usually, a dbo can maintain the objects in their database.
The server wide setting can be determined and modified by users with sso_role, using
sp_configure "lock scheme"
For example:
sp_configure "lock scheme", 0, datarows
This will create objects per default with a locking scheme of datarows.
Additionally, tables can be created with a certain locking scheme using the
create table
syntax:
create table table_name (column_name_list)
[lock {datarows | datapages | allpages}]
Tables can also be altered after creation. The command
alter table
is used for this purpose:
alter table table_name
lock {allpages | datapages | datarows}
What to look out for when changing the locking scheme
Some points have to be considered when changing the locking scheme:
If changing from allpages locking to data-only locking,
the table is copied within ASE and every index is created on the copie of the table.
This can take some time depending on the size of the table
and also requires sufficient free space in the database.
Applications should preferably not access the table in the mean time.
There are furthermore some particularities to be considered when changing from allpages locking
to data-only locking.
-
Changing the locking scheme is only possible for tables, for which the maximum lenght
of 1962
bytes
(including the 2 bytes for the
offset table)
has not been reached. This means that a row must not be filled close to the maximum with user data.
For tables with dataonly-locking scheme and a fixed length column
the maximum size of a data row is 1960 bytes,
(including the two bytes of offset table.)
-
Tables with a variable column length require 2 additional bytes
for every column with variable length including columns allowing
NULL values.
Furthermore, changing the locking scheme to or from allpage locking using
alter table ... lock
is a heavily i/o intensive process, caused by copying the table and recreating indixes on it.
During the alter command, the process passes these steps:
-
First all rows of the table are copied into new
data pages
and formatted with the new locking scheme. When changing to
data-only locking scheme, every row smaller than
10 bytes is being extended to 10 bytes. The same is valid when
changing to allpage locking from data-only-locking.
-
Afterwards, all indexes are dropped and the re-created.
-
Next, the old table is being deleted.
-
Subsquently, the
system tables
are updated with the new information.
-
Finally, the table counter is updated to force recompilation of
query plans.
Changing the locking scheme: Particularities with clustered indexes
When changing locking schemes from allpage-locking to data-only locking a
clustered index
on a table is copied to the new
data pages
in the order of the key of the clustered index.
With a non-clustered index, the rows are copied following their
page chain.
To allow for a
rollback
when executing the command
alter table ... lock
changing the locking scheme is done in a single
transaction.
This is accomplished by locking the table with an
exclusive table lock.
Changing from datepages locking to datarows locking or
vice versa, only requires updating system tables,
so that the change should be finished within a relatively short time and can
therefore be done during normal operation.
The
database option
select into/bulkcopy/pllsort is not required for this task.
Before changing the locking scheme, it is recommended to
dump
the user and
master
database! Afterwards, run
on the altered tables and
on the user user database to check consistency of the data.
To be able to dump the
transaction log
after changing from allpages locking
to data-only locking or vice versa, it is necessary to
dump the whole database first.
|
