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'
or:
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.
