Locks!! Table Level Locks!! How to Analyze and Optimize ??

Sybase/SAP ASE Locking

Most of Sybase databases are designed for either OLTP or mixed workloads and especially for multi-user, multi-transactions at any point of time. Adaptive Server protects the tables, data pages, or data rows currently used by active transactions by locking them for integrity and consistency of Data. Locking is a concurrency control mechanism: it ensures the consistency of data within and across transactions. Locking is needed in a multiuser environment, since several users may be working with the same data at the same time.

Locking affects performance when one process holds locks that prevent another process from accessing needed data. The process that is blocked by the lock sleeps until the lock is released. 

This is called lock contention. BTW, Locking is handled automatically by Adaptive Server, with options that can be set at the session and query level by the user. 

Based on the data and rows/pages being processed the locking granularity changes. By locking at higher levels of granularity, the amount of work required to obtain and manage locks is reduced. If a query needs to read or update many rows in a table:

• It can acquire just one table-level lock
• It can acquire a lock for each page that contained one of the required rows
• It can acquire a lock on each row

Less overall work is required to use a table-level lock, but large-scale locks can degrade performance and availability of data to other concurrent processes, by making other users wait until locks are released. Decreasing the lock size makes more of the data accessible to other users. However, finer granularity locks can also degrade performance, since more work is necessary to maintain and coordinate the increased number of locks. To achieve optimum performance, a locking scheme must balance the needs of concurrency and overhead, entirely depends on the work-load or transactions type.

Adaptive Server provides these locking schemes:

• Allpages locking, which locks both datapages and index pages
• Datapages locking, which locks only the data pages
• Datarows locking, which locks only the data rows

For each locking scheme, Adaptive Server can choose to lock the entire table for queries that acquire many page or row locks, or can lock only the affected pages or rows.

Adaptive Server has two levels of locking:

·       For tables that use allpages locking or datapages locking, either page locks or table locks.

·       For tables that use datarows locking, either row locks or table locks

Page or row locks are less restrictive (or smaller) than table locks. A page lock locks all the rows on data page or an index page; a table lock locks an entire table. A row lock locks only a single row on a page. Adaptive Server uses page or row locks whenever possible to reduce contention and to improve concurrency.

Every row or page in a table is also protected with a lock. These locks only come in two flavours:

·       share

·       exclusive

Many transactions can hold a share lock concurrently, but only one transaction can hold an exclusive lock.

Adaptive Server uses a table lock to provide more efficient locking when an entire table or a large number of pages or rows will be accessed by a statement. Locking strategy is directly tied to the query plan, so the query plan can be as important for its locking strategies as for its I/O implications.

The following describes the types of table locks.

·       Intent lock

An intent lock indicates that page-level or row-level locks are currently held on a table. Adaptive Server applies an intent table lock with each shared or exclusive page or row lock, so an intent lock can be either an exclusive lock or a shared lock. Setting an intent lock prevents other transactions from subsequently acquiring conflicting table-level locks on the table that contains that locked page. An intent lock is held as long as page or row locks are in effect for the transaction.

·       Shared lock

This lock is similar to a shared page or lock, except that it affects the entire table. For example, Adaptive Server applies a shared table lock for a select command with a holdlock clause if the command does not use an index. A create nonclustered index command also acquires a shared table lock.

·       Exclusive lock

This lock is similar to an exclusive page or row lock, except it affects the entire table. For example, Adaptive Server applies an exclusive table lock during a create clustered index command. update and delete statements require exclusive table locks if their search arguments do not reference indexed columns of the object.

How to Analyze and Optimize?

Use sp_object_stats or sp_sysmon to determine the overall server level lock contention, and then use it to tune to reduce lock contention of the system. We also need to monitor these reports whenever resources changed or locking configuration changed for the Server.

Viewing current locks on the Server

To get a report on the locks currently being held on Adaptive Server, use sp_lock:

sp_lock

fid spid loid locktype         table_id   page  row dbname   context

--- ---- ---- ---------------- ---------- ----- --- -------- ----------------

  0  15   30  Ex_intent         208003772     0   0 sales    Fam dur

  0  15   30  Ex_page           208003772  2400   0 sales    Fam dur, Ind pg

  0  15   30  Ex_page           208003772  2404   0 sales    Fam dur, Ind pg

  0  15   30  Ex_page-blk       208003772   946   0 sales    Fam dur

  0  30   60  Ex_intent         208003772     0   0 sales    Fam dur

  0  30   60  Ex_page           208003772   997   0 sales    Fam dur

  0  30   60  Ex_page           208003772  2405   0 sales    Fam dur, Ind pg

  0  30   60  Ex_page           208003772  2406   0 sales    Fam dur, Ind pg

  0  35   70  Sh_intent          16003088     0   0 sales    Fam dur

  0  35   70  Sh_page            16003088  1096   0 sales    Fam dur, Inf key

  0  35   70  Sh_page            16003088  3102   0 sales    Fam dur, Range

  0  35   70  Sh_page            16003088  3604   0 sales    Fam dur, Range

  0  49   98  Sh_intent         464004684     0   0 master   Fam dur

  0  50  100  Ex_intent         176003658     0   0 stock    Fam dur

  0  50  100  Ex_row            176003658 36773   8 stock    Fam dur

  0  50  100  Ex_intent         208003772     0   0 stock    Fam dur

  0  50  100  Ex_row            208003772 70483   1 stock    Fam dur

  0  50  100  Ex_row            208003772 70483   2 stock    Fam dur

  0  50  100  Ex_row            208003772 70483   9 stock    Fam dur

32  13   64  Sh_page           240003886 17264   0 stock

32  16   64  Sh_page           240003886  4376   0 stock

32  19   64  Sh_page           240003886 22367   0 stock

32  32   64  Sh_intent          16003088     0   0 stock    Fam dur

32  32   64  Sh_intent          48003202     0   0 stock    Fam dur

32  32   64  Sh_intent         240003886     0   0 stock    Fam dur

This example shows the lock status of serial processes and two parallel processes:

·       spid 15 hold an exclusive intent lock on a table, one data page lock, and two index page locks. A “blk” suffix indicates that this process is blocking another process that needs to acquire a lock; spid 15 is blocking another process. As soon as the blocking process completes, the other processes move forward.

·       spid 30 holds an exclusive intent lock on a table, one lock on a data page, and two locks on index pages.

·       spid 35 is performing a range query at isolation level 3. It holds range locks on several pages and an infinity key lock.

·       spid 49 is the task that ran sp_lock; it holds a shared intent lock on the spt_values table in master while it runs.

·       spid 50 holds intent locks on two tables, and several row locks.

·       fid 32 shows several spids holding locks: the parent process (spid 32) holds shared intent locks on 7 tables, while the worker processes hold shared page locks on one of the tables.

The lock type column indicates not only whether the lock is a shared lock (“Sh” prefix), an exclusive lock (“Ex” prefix), or an “Update” lock, but also whether it is held on a table (“table” or “intent”) or on a “page” or “row.”

A “demand” suffix indicates that the process will acquire an exclusive lock as soon as all current shared locks are released. 

sp_familylock displays the locks held by a family. This examples shows that the coordinating process (fid 51, spid 51) holds a shared intent lock on each of four tables and a worker process holds a shared page lock:

sp_familylock 51

fid spid loid locktype         table_id   page  row dbname   context

--- ---- ---- ---------------- ---------- ----- --- -------- ----------------

51  23  102  Sh_page           208003772   945   0 sales

51  51  102  Sh_intent          16003088     0   0 sales    Fam dur

51  51  102  Sh_intent          48003202     0   0 sales    Fam dur

51  51  102  Sh_intent         176003658     0   0 sales    Fam dur

51  51  102  Sh_intent         208003772     0   0 sales    Fam dur

You can also specify two IDs for sp_familylock. ( sp_familylock 23 also gives the above set)

Getting information about blocked processes

sp_who reports on system processes. If a user’s command is being blocked by locks held by another task or worker process, the status column shows “lock sleep” to indicate that this task or worker process is waiting for an existing lock to be released.

The blk_spid or block_xloid column shows the process ID of the task or transaction holding the lock or locks.

You can add a user name parameter to get sp_who information about a particular Adaptive Server user. If you do not provide a user name, sp_who reports on all processes in Adaptive Server. 

Some options to improve locks performance: 

These locking guidelines can help reduce lock contention and speed performance:

·       Use the lowest level of locking required by each application. Use isolation level 2 or 3 only when necessary.

Updates by other transactions may be delayed until a transaction using isolation level 3 releases any of its shared locks at the end of the transaction.

Use isolation level 3 only when nonrepeatable reads or phantoms may interfere with your desired results.

If only a few queries require level 3, use the holdlock keyword or at isolation serializing clause in those queries instead of using set transaction isolation level 3 for the entire transaction.

If most queries in the transaction require level 3, use set transaction isolation level 3, but use noholdlock or at isolation read committed in the remaining queries that can execute at isolation level 1.

·       If you need to perform mass inserts, updates, or deletes on active tables, you can reduce blocking by performing the operation inside a stored procedure using a cursor, with frequent commits.

·       If your application needs to return a row, provide for user interaction, and then update the row, consider using timestamps and the tsequal function rather than holdlock.

·       If you are using third-party software, check the locking model in applications carefully for concurrency problems.

Also, other tuning efforts can help reduce lock contention. For example, if a process holds locks on a page, and must perform a physical I/O to read an additional page, it holds the lock much longer than it would have if the additional page had already been in cache.

Better cache utilization or using large I/O can reduce lock contention in this case. Other tuning efforts that can pay off in reduced lock contention are improved indexing and good distribution of physical I/O across disks.

To help reduce lock contention between update and select queries:

·       Use datarows or datapages locking for tables with lock contention due to updates and selects.

·       If tables have more than 32 columns, make the first 32 columns the columns that are most frequently used as search arguments and in other query clauses.

·       Select only needed columns. Avoid using select * when all columns are not needed by the application.

·       Use any available predicates for select queries. When a table uses datapages locking, the information about updated columns is kept for the entire page, so that if a transaction updates some columns in one row, and other columns in another row on the same page, any select query that needs to access that page must avoid using any of the updated columns.

Finally, an Index scan/seek reduces table accessing time or locking period so always check for table scans in Query plans especially for Queries with joins.