Keeping transactions short

Any transaction that acquires locks should be kept as short as possible. In particular, avoid transactions that need to wait for user interaction while holding locks.

Table 3-1: Examples

With page-level locking

With row-level locking

begin tran
select balance 
from account holdlock
where acct_number = 25

Intent shared table lock Shared page lock

Intent shared table lock Shared row lock

If the user goes to lunch now, no one can update rows on the page that holds this row.

If the user goes to lunch now, no one can update this row.

update account
set balance = balance + 50
where acct_number = 25

Intent exclusive table lock Update page lock on data page followed by exclusive page lock on data page

Intent exclusive table lock Update row lock on data page followed by exclusive row lock on data page

No one can read rows on the page that holds this row.

No one can read this row.

commit tran

Avoid network traffic as much as possible within transactions. The network is slower than Adaptive Server. The example below shows a transaction executed from isql, sent as two packets.

begin tran
update account
set balance = balance + 50
where acct_number = 25
go

isql batch sent to Adaptive Server Locks held waiting for commit

update account
set balance = balance - 50
where acct_number = 45
commit tran
go

isql batch sent to Adaptive Server Locks released

Keeping transactions short is especially crucial for data modifications that affect nonclustered index keys on allpages-locked tables.

Nonclustered indexes are dense: the level above the data level contains one row for each row in the table. All inserts and deletes to the table, and any updates to the key value affect at least one nonclustered index page (and adjoining pages in the page chain, if a page split or page deallocation takes place).

While locking a data page may slow access for a small number of rows, locks on frequently-used index pages can block access to a much larger set of rows.