About the Sybase database lock

The choice of lock type is indeed an important consideration in Sybase database design. Sybase development and management staff more headaches, for this reason, to some introductory information about lock!

The supported locking mechanism

Full page lock

Full page locking is both a new term and a type of lock supported by ASE (Adaptive Server Enterprise) in the past. This type has the following characteristics:

Lock all accessible pages at the page level

When various types of pages are changed in any way, these exclusive pages are locked and the locking mechanism is maintained until the transaction terminates;

When the next required page has been successfully obtained, share page lock for the current access page that has been released (if a third layer of ANSI isolation is used, keep the locking mechanism to the end of the transaction) using the page-level time stamp (timestamp) to determine if the change occurs. Details are recorded in the transaction log for use forward or backward when the system is restored.

This locking method often provides the highest performance solution, especially when the application design has considered these features. However, some application systems, when some activities occur, this way of locking the entire page may have a significant impact on system performance. This is particularly true for applications that are designed in the context of the general environment for database manufacturer products, such as file systems or other databases that have support for finer-scale locking mechanisms.

In addition, there are a series of problems, which are to work around more difficult conditions. They typically take a more Sybase-specific solution. It is a challenge for commercial application manufacturers, because it will require them to cross the database platform they support to complete the work of maintaining their original code, which is a considerable workload. The basic questions in this area are as follows:

There is a controversy over the most terminal leaf page of a non-clustered index that has been created in ascending values

Deadlocks can occur when inserting and querying tables that do not have a clustered index;

Deadlocks can occur between the updating of a clustered index value and query access to a table that is not a clustered index;

Conflicts may occur on the last row of tables that are not indexed (although partitions can be used for the last specific address);

There is a potential conflict between tables with a small number of rows (although the fill factor [fillfactors] and the maximum number of rows per page [Max_rows_per_page] can be used for a specific address)

The need to lock the sides of each page is often split; if a table is so small that it resides on a single page, access to a single row will actually break the locking mechanism for the entire table.

Lock data only

The data locking mechanism only attempts to address the main issues of concern in the previous section of this article (other topics will be addressed in other functional areas). This locking method supports two different types of work: data row locking and Data page locking. In both cases, the locking method that they support is different from the previous locking mechanism. Locking data only has the following characteristics:

Transaction locking is not corrupted in index pages. Instead, it uses a mechanism called latching. Latches are synchronous methods similar to rotational locks (spinlocks) that are not transaction-independent and retain only a short period of time (generally, when a task physically changes a small piece of data in a database, This cycle is equivalent to changing some byte of data in a 2K page in a shared storage area once the task is complete, it will open the latch directly. When this situation can also be temporary with other blocks, because such latches do not have context switching to the server task, nor can they involve deadlocks, and can only remain in the primary for a short period of time, they do not produce significant contention.

Takes a single row for data row lock (the row ID [RID----row ID] is a combination of the logical page number and the line number on the page on which it is located);

Supports fixed row identity rids, which can be forward, allowing the movement of data rows to be completed without the change of their RID. When a row becomes larger than its available space, the above results do not require any changes to the nonclustered index.

Inserts can be done at the end of a table without any contention, and this functionality has been added.

Support the use of range lock, the next keyword lock and infinite lock, and other ways to lock the logical range value

Supports page segmentation caused by top-level operations. These cases are submitted directly, and the "system" transaction can cause the page to remain locked in a shorter time period.

In order to support these changes, a series of improvements are needed in the structure of the storage table used. The main effects of these improvements are as follows:

The clustering index is now stored as a "drop Index" ("Placement Indexes") used by IBM DB2 products, as many people are familiar with. Way This structure is similar to a nonclustered index and requires a similar amount of space. This modified structure causes the data to be stored sequentially across the data pages in the initial storage, but when inserted, they are kept as tightly as possible so that there is no page segmentation in the correct logical page. In addition, the data order in the data page is not maintained when the new row increases. The application of this index adds an I/O operation to the travel of each clustered index.

The row offset table has been added to the index and data pages. This increase and the new row index row storage format have the potential to reduce the number of index entries stored in each index page.

Fixed row identification (RIDs). When a row is moved, the forward address for assigning a new row position is placed at the location used to host the row. When this movement requires a change in the nonclustered index, access to the row requires an additional I/O operation to get the ' forward ' position.

In general, indexes will be smaller and shorter because of the following reasons:

Use a double key restriction mechanism from each leaf-level page to restrict the double key (Duplicate key) For example, if the value "green" is in the row with the following line identifier (RIDs) equal to 123-1, 234-2, and 345-3, the value "green" is stored separately. , 123-1,234-2,345-3, instead of storing the value "green" ("greens,") three times. Each value is stored only once per index page.

The suffix is compressed in non-leaf nodes of the nonclustered index tree (for example, if the key value is "GREEN" and "HAMILTON" and the two values split, then the "G" and "H" are stored in the non-page-level index page).