SYBASE ASE 15.7 Lock-in and Concurrent Control Learning

Introduction to Locks

granularity

• Watch lock
• Page lock
• Row lock

Higher-granularity locking can reduce the overhead of acquiring and managing locks, but large-scale locking can lead to performance degradation.
Fine-grained locking allows other users to access more data, but also increases the overhead of maintaining and coordinating locks, thereby reducing performance.
To achieve optimal performance, locking schemes must strike a balance between concurrency and administrative overhead.

Lock scheme

Locking schemes refer to values that can be viewed and set through sp_helpconfig'lock Scheme'and sp_configure'lock scheme','allpages' | datapages | datarows'.

• All pages
  Locking objects: data pages and index pages
  Duration: Whole transaction
  Performance issues: Index keys are very short, index pages contain many keys, the number of rows locked is much larger than data pages, lock competition is fierce.
• Data pages
  Lock object: data page. Index pages use latches
  Duration: Data Page: The whole transaction. Index Page: Period of Physical Change
  Performance issues: latch
• Data row locks
  Lock object: data row. Data pages use latches, index pages use latches
  Duration: Data row: The whole transaction. Data page: Period of physical change. Index Page: Period of Physical Change
  Performance issues: latch

Lock level

• Data Page or All Page Lock Tables: Page Lock or Table Lock
• Data row lock table: row lock or table lock

The number of page locks exceeds the threshold set by sp_setpglockpromote. Attempt to upgrade table locks and release row locks.

Lock type

• Page lock and row lock types:
  Shared locks, exclusive locks, update locks

• Table lock type:
  Intention locks, shared locks, exclusive locks

Lock blockage

Shared locks allow shared locks to block exclusive locks. Exclusive locks block shared locks and exclusive locks.

Lock and transaction isolation level

In the case of concurrent database, it may cause data "update loss", "dirty reading", "non-repeatable reading", "hallucination". sybase uses two-stage lock protocol to ensure the isolation and consistency of transactions. The two-stage protocol refers to the acquisition and release stages of locks. The acquisition stage includes sharing locks when reading data, exclusive locks when updating data, and release stages refer to the release stages of sharing locks and exclusive locks. The isolation level of database affects the release timing of shared locks.

• Update lost:
  Changes to data by one transaction are overwritten by another transaction.

• Dirty reading:
  One transaction reads uncommitted changes from another transaction.

• Not repeatable:
  The results of two queries within a transaction are inconsistent.

• Illusions:
  An insert that is queried in one transaction to uncommitted in another transaction.

sybase supports isolation level 4:

• Level 0:
  When updating data, exclusive locks are added, while reading data is not locked. There will be no "lost update", dirty reading, unrepeatable reading and hallucination reading.

• Level 1:
  When updating data, add exclusive lock, read data and share lock, and release the share lock after reading data. There will be no "lost update", "dirty reading", "unrepeatable reading", "hallucination reading".

• Level 2:
  Update data overtime exclusive lock, read data plus shared lock, and release shared lock at the end of transaction. There will be no "lost update", "dirty reading", "unrepeatable reading", and "hallucination".

• Level 3:
  Update data overtime exclusive locks and scope locks, read data and share locks, and release shared locks at the end of transactions. There will be no "lost update", "dirty reading", "unrepeatable reading", "hallucination reading".

  The higher the isolation level of the database, the lower the concurrency performance.

View Lock Tool

Commonly used tools for looking at locks are "sp_lock" and "sp_who" and sp_sysmon. They look at the locked object, type, level and SP id, but object ID and spid are not easy to see which application is which and which table is which ip locked.

sp_object_stats `00:05:00': It can monitor the number of lock grants and the most contested objects in a specific period of time.

Another way to view lock details:

select pr.spid ,
pr.ipaddr,
pr.program_name,
pr.cmd,
db_name(lc.dbid) as dbname, 
obj.objname as tbname,
pr.blocked as lock_spid,
locktype.lockname,
locktype.locktype
from master..syslocks lc 
    left join master..sysprocesses pr on lc.spid = pr.spid
    left join (
                select id objid,name objname,db_id('YWST') dbid from YWST..sysobjects 
                union all
                select id objid,name objname,db_id('DB_ATY') dbid from DB_ATY..sysobjects
                union all
                select id objid,name objname,db_id('DB_ZGZXLCJDDJ_TASK') dbid from DB_ZGZXLCJDDJ_TASK..sysobjects
        union all
                select id objid,name objname,db_id('JCSZ') dbid from JCSZ..sysobjects
                union all
                select id objid,name objname,db_id('YYFZ') dbid from YYFZ..sysobjects
              ) obj on lc.id = obj.objid and lc.dbid = obj.dbid
    left join (
                select 1 as locktype,'Exclusive watch lock' as lockname
                union all
                select 2 as locktype,'Shared table lock' as lockname
                union all
                select 3 as locktype,'Exclusive Intention Lock' as lockname
                union all
                select 4 as locktype,'Shared intent lock' as lockname
            union all
                select 5 as locktype,'Exclusive Page Lock' as lockname
            union all
                select 6 as locktype,'Shared Page Lock' as lockname
union all
                select 7 as locktype,'Update Page Lock' as lockname
union all
                select 8 as locktype,'Exclusive Lock' as lockname
union all
                select 9 as locktype,'Shared row locks' as lockname
union all
                select 10 as locktype,'Update row locks' as lockname
union all
                select 11 as locktype,'Sharing Next Key Lock' as lockname
union all
                select 256 as locktype,'Lock blocking another process' as lockname
union all
                select 512 as locktype,'Request lock' as lockname
              ) locktype on lc.type = locktype.locktype

Reflection

In the investigation of NP problems, it is found that the "granularity", "type", "level" of locking seriously affects the performance of the subsequent system. Can we solve the problem through the application of locking principle?
For example:

• A large number of deletions and insertions of ddts will cause exclusive table locks without indexes. If there are indexes, it will also be upgraded to table locks for excessive locks, resulting in query and update blockages. Can lock upgrade threshold be set to alleviate the problem?
• In provinces where there is no separation of reading and writing, query applications such as statistical analysis and comprehensive statistics will result in prolonged shared locks on business tables, thus blocking the preservation of cases. Can we avoid prolonged shared locks by setting the isolation level of query applications to 0?