Sybase now supports new locking methods called “data-only” locking. In previous releases, all locking happened at the page level. When you upgrade, by default, your server performs page-level locking. However, you may change the locking scheme on your existing tables with the alter table command.
The new locking schemes are:
Datapages locking
Datarows locking, also known as row-level locking
If you intend to use the data-only locking feature, you need to be aware of the following system and application issues:
The number of locks required for your applications may increase, and you may need to change the number of locks configuration parameter.
On data-only-locked tables with clustered indexes, the default behavior of disallowing duplicate rows and the ignore_dup_row option are not enforced during inserts and updates. This changes the behavior of commands that insert duplicate rows and may change the behavior of create clustered index if the table contains duplicate rows.
On data-only-locked tables, the sorted_data option to create index can be used only immediately following a bulk copy operation that copies into an existing table. Use of the sorted_data option is prohibited once additional page allocation operations have been made for the table.
Bulk copy into data-only-locked tables requires the version of bcp and the bulk copy libraries shipped with Adaptive Server version 11.9.2. Older versions of bcp and the bulk-copy libraries can still be used to copy into allpages-locked tables. See the Release Bulletin for the required version number.
When using parallel sort for data-only-locked tables, the number of worker processes must be configured to equal or exceed the number of partitions, even for empty tables. The database option select into/bulkcopy/pllsort must also be enabled.
Clustered indexes on data-only-locked tables may not return rows in clustered key order if there is no order by clause. Bulk copy is not guaranteed to copy out a table in clustered key order.
Queries on unpartitioned allpages-locked tables with clustered indexes return rows in the clustered key order if the query does not include a sort (that is, if there are no clauses such as order by or distinct). Bulk copy also copies the rows out of allpages-locked tables in clustered key order for both partitioned and unpartitioned tables.
