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.
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.
