Help - SQL command to put a Page Lock on a Table.

[sdas123]

What is the SQL command to put a page lock on a table?

 

[sybaseguru]

At a granular level in Sybase there are two types of locks. These are page level or row level locking. These are determined by the type of lock the table holds. By default the Sybase tables are created using page locks. You can put these tables in row level locking by using alter table <TABLE_NAME> lock datarows. So it all depends whether your table is at page level or row level locking.
To find out what lock mechanism the table is using do a sp_help on the table, at the bottom it should say something like

Object is not partitioned.
Lock scheme Allpages

You can use &quot;holdlock&quot; command to hold a lock on a page or row accordingly but make sure that the holdlock is kept for a shorter period as possible. For example

begin tran
select balance 
from account holdlock
where acct_number = 25
--Beware if you go to lunch now, no one can update rows on the page or row that holds this row!.
update account
set balance = balance + 50
where acct_number = 25
--This is intent exclusive table lock Update page lock on data page followed by exclusive page lock on data 
--page OR 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  or No one can read this row until you do a commit tran!
commit tran

So in summary you can use holdlock for this purpose but remember to commit the transaction as soon as possible.
Good luck and hope this helps