Case study 9: Which is locking level?
  • Platform: HP-UNIX 11, Tru64 , Windows
  • Sybase ASE version: 12.5.x
  • Background story: In multiuser testing process we saw that although every user has to access its row in table and locking level on table was set on row, one process blocks another, so I presume that we have forgotten to set row level locking on every table which is accessed in tested transaction.
  • Task: Find out what are locking schemes for all tables in database.
  • Solution: Data about tables locking levels are stored in sysobjects table, in bit mask column sysstat2.
    Following script shows all the tables which do not have row level locking:

    select name from sysobjects where type = 'U' and sysstat2 & 32768 <> 32768 order by name

    To find out other locking schemes in your database use appropriate bit masks as shown below:
    8192 Table uses allpages locking scheme
    16384 Table uses datapages locking scheme
    32768 Table uses datarows locking scheme

    Other ways for checking locking schema are following:

    select name, case (sysstat2 & 57344)
    when 0 then 'Allpages'
    when 8192 then 'Allpages'
    when 16384 then 'Datapages'
    when 32768 then 'Datarows'
    end as 'Lock Schema'
    from sysobjects where type='U'


    select name,lockscheme(id)
    from sysobjects
    where type = 'U'

    For more information about Sybase system tables, and especially interesting sysstat2 column look at Sybase ASE documentation, Reference Manual Volume 4: Tables.

Locations of visitors to this page
  powered by myself. DBA-Sybase 2006