Table lock problem

I've come across a strange problem where an exclusive table lock is being taken on a large table which is uniquely keyed on a single column when the table is updated by the key column. The table key is hash unique and the table has about a dozen rules. The table is not scanned and disk IO in the QEP output and output from trace point qe90 indicate only a couple of pages are accessed. The table is optimized weekly and remodified 3 weekly.

I've also run verifydb in report mode against the table and analysed the results to examine the extent of overflow chains and whilst overflow is about 30% the longest overflow chain is 3 pages.

I've tried updating the table and setting "norules" with no change in behaviour. I've also tried extending the table to have more locations and remodifying the table to btree unique, again with no change in behaviour in either case.

This problem exists in both our operation and test environments.

I've also created a "cut-down" version of this table with about 800,000 rows and don't see this table locking behaviour.

Any suggestions would be appreciated.

TIA

Laurence Breeze



Details:

SunOS 5.8, Ingres Version II 2.0/0001 (su4.us5/00)

Table:

  1> help table ci_d_stud_basics


Name: ci_d_stud_basics
Owner: ccdba
Created: 01-mar-2003 00:40:25
Location: location1,
location1a,
location1b
Type: user table
Version: OI2.0
Page size: 2048
Cache priority: 0
Alter table version: 0
Alter table totwidth: 598
Row width: 598
Number of rows: 3407652
Storage structure: hash with unique keys
Compression: none
Duplicate Rows: not allowed
Number of pages: 1528436
Overflow data pages: 479780
Journaling: enabled
Base table for view: no
Permissions: yes
Integrities: none
Optimizer statistics: yes; see avg count below, more info in the iistats catalog

Column Information:
Key Avg Count
Column Name Type Length Nulls Defaults Seq Per Value
personal_id char 8 no yes 1 unique
title varchar 14 no yes
surname varchar 25 no yes
forenames varchar 40 no yes
initials varchar 4 no yes
birth_date date no yes
ousba_status char 1 no yes
ousba_status_date date no yes
day_telephone_num varchar 25 no yes
evening_telephone_num varchar 25 no yes
region_code char 2 no yes
regulations_bypassed char 1 no yes
sex char 1 no yes
promotion_activity_code char 6 no yes
record_version_num integer 1 no yes
stud_status1 char 1 no yes
stud_status_date1 date no yes
stud_status_rsn1 char 2 no yes
stud_status2 char 1 no yes
stud_status_date2 date no yes
stud_status_rsn2 char 2 no yes
address_line1 varchar 35 no yes
address_line2 varchar 35 no yes
address_line3 varchar 35 no yes
address_line4 varchar 35 no yes
address_line5 varchar 35 no yes
postcode varchar 35 no yes
country_code char 2 no yes
character_set_code integer 4 no yes
study_centre_code char 3 no yes
last_address_change_date date no yes
trnsn_key integer 2 no yes
first_reg_pres_code char 3 no yes
regs_bypass_user_id varchar 8 no yes
regs_bypass_date date no yes
ou_graduate char 1 no yes
ousba_opened_date date no yes
ousba_opened_dept_code char 4 no yes
stud_char1_8 integer 1 no yes
stud_char9_16 integer 1 no yes
stud_char17_24 integer 1 no yes
stud_char25_32 integer 1 no yes
stud_char33_40 integer 1 no yes
stud_char41_48 integer 1 no yes
stud_char49_56 integer 1 no yes
stud_char57_64 integer 1 no yes
stud_char65_72 integer 1 no yes
stud_char73_80 integer 1 no yes
stud_char81_88 integer 1 no yes
stud_char89_96 integer 1 no yes
stud_char97_100 integer 1 no yes
sn_warning char 1 no yes
srnew_dearchive_rqstd char 1 no yes
catchment_area_id char 4 no yes
result_letter_inhibit char 1 no yes
last_address_change_id varchar 8 no yes
first_reg_pres_code_5 char 5 no yes
last_stud_stat1_chng_id char 8 no yes
last_stud_stat2_chng_id char 8 no yes
ousba_defaults_count integer 1 no yes
overseas_transfer_agreed char 1 no yes
ousba_new_contract_date date no yes
ousba_account_status char 1 no yes
ousba_account_status_date date no yes
ousba_account_purged char 1 no yes
ousba_account_purged_date date no yes
catchment_area_override_status char 1 no yes

Secondary indexes:    none


Output from:

set lock_trace;
set norules;
update ci_d_stud_basics
set trnsn_key = 9999
where personal_id = '12345678';

********************************************************************
UNLOCK: ALL Tran-id: <0000420159FD6C9E>
LOCK: TABLE PHYS Mode: X Timeout: 0 Key: (circe_acct,ci_d_stud_basics
LOCK: TABLE PHYS Mode: X Timeout: 0 Key: (circe_acct,ci_d_stud_basics
LOCK: TABLE PHYS Mode: X Timeout: 0 Key: (circe_acct,<Temporary Table

********************************************************************


.

Relevant Pages

  • Re: [Info-ingres] Table lock problem
    ... The audit table is identical to the base table in terms of the columns, except there are three extra columns, change_type, change_date and changed_by. ... Having dropped all the rules and recreated each one in turn I find that the third rule on its own leads to the table lock following a keyed update. ... the results to examine the extent of overflow chains and whilst ... TABLE PHYS Mode: X?? ...
    (comp.databases.ingres)
  • Re: New "timeout" api, to replace callout
    ... Timer expires on active session but source of timer was just ... time has expired and it is ready to run, once its lock gets freed. ... disable or cleanup the timeout. ...
    (freebsd-arch)
  • Re: miibus + USB = problem
    ... detach, it unregisters the timeout. ... with the lock held, thus ensuring deadlock if the timeout fires after ... This is why you can't run the detach routine locked in most ... The problem with USB devices, is that the read-register process is very ...
    (freebsd-hackers)
  • Re: Resolving race conditions with callbacks and cancellation?
    ... callback could be called after cancel call completes. ... The callback must be to the state-machine inside the lock. ... The state is 'Eidle' and so this event causes an action routine to be called that changes the object SM state to 'EreadWithTimeout', stores N inside the object, queues the object to the timeout thread, unlocks the SM and makes the read call, where the thread driving the object gets blocked. ...
    (comp.programming.threads)
  • Re: New "timeout" api, to replace callout
    ... only ever grab the specified lock when it calls the timeout function. ... My plan is to use non-blocking grabs of the timeouts lock to get ... When a timeouts timer expires, the thread that services the timeouts ...
    (freebsd-arch)