Locks

There are 2 types of locking schemes

1. All page locking (APL)
2. Data only Locking (DOL)
   1. Data Page Locking (DPL)
   2. Data Row Locking (DRL)

All Page Locking

In this locking scheme server uses table level locks and page locks, but not row level locks and index page can be locked.

Data Page Locking

Serer uses table locks and page locks but no row level locks and index pages are never locked .

Data Row Locking

Index pages are not locked but server uses table and row level locks.

Lock Promotion Thresholds

In all page locking the lock promotion will be done from page level to table level

• In DPL the lock promotion is done from page level to table level
• In DRL the lock promotion is done row level to table level but not table level

Types of LOCKS

• Shared Lock
• Exclusive Lock
• Update Lock
• Dead Lock

Shared lock

Adaptive server applies shared lock for Read (select) operation .If a shared lock has been applied to a data page or a data row or to an index page other transactions can also acquire a shared lock even when the first transaction is active .

“ No Transactions can acquire Exclusive lock on a page / row / index until all the shared locks on the page / row are released ”

Exclusive Lock

ASE applies an exclusive lock for a DML operations like insert/delete when a transaction gets on a exclusive lock other transactions cannot acquire a lock of any kind on the page / row until the exclusive lock is released at the end of its transaction

“ The other transactions wait or block's until the exclusive lock is released ”

Update Lock

An update lock is applied during the initial phase of an update /delete fetch operation while the page /row is being read .

“It allows shared locks but not update or exclusive locks”

To change the locking scheme of a table

Syntax Alter table table_name lock {allpages | datapages | datarows}

Example Alter table EMP lock datarows

Dead Lock

A dead lock occurs when 2 or more process ,each have a lock on a separate data-page or index page /table and each want to acquire a lock on same page or table locked by other process this situation is called dead lock .

In ASE when a deadlock situation occurs the process which has the least CPU utilized will be killed .The configuration option “print deadlock info” is enabled then it will display the process information and the SQL's which theses process running will be displayed on the error log .

The deadlock situation is automatically dealt by ASE server itself

The Error message sent to error 1205

To know the deadlock info we need to enable the configuration parameters

sp_configuration “Print Deadlock Information”

• Locks can be on page or a table
• Lock promotion can be configured server wide or per object (table)
• Deadlocks are detected and cleared by SQL server after a default amount of time has elapsed
• Deadlock detection time is configurable
• dbcc traceon (3605) → Trace flag prints information what tasks involved in deadlocks