Re: Lock types

In <21clml$hj6@panix.com> clay@panix.com (Clay Irving) writes: >I tried to to select the count of the number of rows in a table this >afternoon and I received a message from ISQL that the table was locked. I >ran 'tbstat -k' and received the following: > RSAM Version 5.00.UC2 -- On-Line -- Up 7 days 05:12:00 -- 6208 Kbytes > Locks > address wtlist owner lklist type tblsnum rowid size > 3002c4dc 0 30002040 0 HDR+S 1000002 202 0 > 3002c83c 0 300020ac 0 S 1000002 203 0 > 3002c87c 0 30001efc 3002cd1c HDR+IX 10002a1 0 0 > ^^^^^^^^^^^^^^^^^^^^^^^^ > 3002c8bc 0 30001efc 3002cd3c HDR+IX 100029a 0 0 > ^^^^^^^^^^^^^^^^^^^^^^^^ > 3002cb5c 0 30001e24 0 HDR+S 1000002 203 0 > 3002ccdc 0 30002184 0 S 1000002 203 0 > 3002cd1c 0 30001efc 0 S 1000002 203 0 > 3002cd3c 0 30001efc 3002c87c HDR+U 10002a1 109 0 > 3002cdbc 0 30001ce0 0 S 1000002 203 0 > 3002ce7c 0 30001efc 3002c8bc HDR+U 100029a 10a 0 > 10 active, 100000 total, 8192 hash buckets >The underlined locks are giving me a problem -- Can someone explain what >a 'header intent exclusive' lock is? The documentation doesn't explain... HDR just means that it is at the head of a hash chain. The two IX locks are needed by the two Update locks to prevent conflicting table level locks from being placed. IMHO your select should not be attempting a conflicting table level lock unless you are running at Repeatable Read isolation. At Dirty Read no table level lock should be requested. At Commited Read it should attempt to place an IS, which does not conflict. At Cursor Stability it should place an IS, which does not conflict. At Repeatable Read it will place an S which *does* conflict. However, what it is actually doing, is attempting to place a shared lock. This is a side-effect of the fact that count(*) is no longer achieved via a sequential scan (but is, IMHO, a bug). If you change the query to force a scan (eg SELECT COUNT(*) FROM foo WHERE 1 = 1;), then it will work, but it will, of course, be slow if the table is large. Alternatively, if you don't actually need Committed Read isolation, just SET ISOLATION TO DIRTY READ, and it will work fine. graeme -- Disclaimer: The author's opinions are his own, and not necessarily those of Pyramid Technology Ltd. or Pyramid Technology Inc. --------------------------------------------------------------------------- -m------- Graeme Sargent Voice: +44 (0)252 373035 ---mmm----- Senior Database Consultant Fax : +44 (0)252 373135 -----mmmmm--- Pyramid Technology Ltd. Telex: Tell who??? -------mmmmmmm- Farnborough, Hants GU14 7PL Email: graeme@pyra.co.uk --------------------------------------------------------------------------- We have the technology. The tricky bit is learning how to use it.