Trying to understand the behaviour of intent lock in Sybase ASE

[DilipVoora]

Hi Experts,

I would like to seek some clarification about lock type "intent" and it's actual behaviour. Read the manuals available online to understand it but when I am trying to relate it with respect to other process, I feel I am missing something. Can you enlighten me on this please?

1. ASE applies 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. When there is an intent lock by a spid (to access a page/row by getting shared/exclusive lock) the entire table is locked by the spid always. Is this correct?

2. Consider two SPIDs 108 and 109. 108 want to delete a row in a table in a page, an Ex_intent lock will be held on the entire table(if my undertsanding is correct) and during the same time if 109 a select query is trying to acess a row(based on a where condition) , it will be in blocked state due to 108. In this case under sp_lock what kind of lock we will be held (like Sh_blk or Row_Blk)?

3. How the behaviour of intent lock will be with respect to APL and DOL(DPL & DRL)?

Regards,
Dilip Voora

[DilipVoora · Accepted solution]

Answering to my own questions after discussing with SAP PS and testing,

1. Table will not be locked compltely when there is an intent lock (Ex or Sh) on a table.
    Reason : In a way, an intent lock does not really lock anything. The mechanism is more of a 'secondary'          lock, as there is already a shared or exclusive page or row lock and the intent lock is a resutl of that. It is a      synchronisation locking type to accomodate concurrency and avoid unnecessary blocking. An INTENT            lock does not lock the table itself but it does prevent conflicting table locks to be taken out.
2. SPID 109 will be entertained and not blocked.
    Reason : Yes, an Ex_intent lock will be held and it does apply to the entire table but it does not lock the      table. Thus the select query is allowed to read and incase if it is blocked could be due to other factors like      isolation level.

References:

https://help.sap.com/docs/SAP_ASE/a08646e0736e4b4b968705079db4c5f5/a8e7fa0cbc2b101497b1ed80d79f2e58....
https://help.sap.com/docs/SAP_ASE/a08646e0736e4b4b968705079db4c5f5/a8e95e69bc2b1014b47d8df722675c67....
https://help.sap.com/docs/SAP_ASE/a08646e0736e4b4b968705079db4c5f5/a8ea9a48bc2b1014bf9d8565f3cd6823....
https://help.sap.com/docs/SAP_ASE/a08646e0736e4b4b968705079db4c5f5/a8eb04a3bc2b1014bef8884d8400b0ab....
https://help.sap.com/docs/SAP_ASE/a08646e0736e4b4b968705079db4c5f5/a8eb90a9bc2b1014a4f8b558fa208f90....
https://help.sap.com/docs/SAP_ASE/a08646e0736e4b4b968705079db4c5f5/a8eb640fbc2b10148a3da909d0e6b37f....
https://help.sap.com/docs/SAP_ASE/a7b3e46335184f5caf70a08c91c540f3/a97b70b4bc2b1014af8dcd7ce8a74e26....

PS: Thanks to Bart for the clarification.

Regards,
Dilip Voora