Re: Locking problem ... again
Solution! thanks for the headsup on the index, forgot about those
puppies, lets take a look, mmmm 3 indexes reference that field.
Ah yes, lets see, legacy issue (why optimise the query, just add
an index).
Thanks sue
brett
Sue_Davidson@exe.com.au wrote:
>
> I haven't tested this properly, but I think that there is one lock per row
> updated (depending on locking level page or row) plus one per row per index on
> that field. ie if you have one index on ins_pub_company you would get approx 60K
> locks, if you have two indexes on that field you would get 90K locks.
>
> Brett Geer <brett@brabys.co.za> on 06/02/2001 16:43:31
>
> To: "informix-list@iiug.org" <informix-list@iiug.org>
> cc: (bcc: Sue Davidson/EXE)
> Subject: Locking problem ... again
>
> Morning all,
>
> our box has say 29K records in a table I want to update,
> case in point:
>
> select count (*) from cocinstn where ins_pub_company = 9 returns 29651>
> now,
>
> update cocinstn set ins_pub_company = 26 where ins_pub_company = 9>
> grabs 60K locks before I kill it... Even with the table locked exclusive
> it grabbed 7K
>
> IDS 7.31.UC4 on AIX 4.3.3ML4
>
> Any ideas?
>
> brett
>
> --
>
> -----------------------------------------------------------------
> Brett's 12th law of UNIX administration...
> People tend not to react well when they lose control over their
> computers. Typically, it brings out the worst in them ...
> -----------------------------------------------------------------
> Brett Geer - UNIX Admin/Analyst/Programmer - Intratex Holdings.
> Tel. +27 31 717 4000 Direct. +27 31 717 4146
> Fax. +27 31 717 4001
> -----------------------------------------------------------------
> The little voices are talking to me again, telling me to reach
> for a keyboard and type rm -rf /*
> last week they had me rm -rf `echo $MANPATH | sed 's/:/ /g'`
> now I fear I have no answers
> -----------------------------------------------------------------
--
-----------------------------------------------------------------
Brett's 13th law of UNIX administration...
The 13th sucks, we'll skip this one
-----------------------------------------------------------------
Brett Geer - UNIX Admin/Analyst/Programmer - Intratex Holdings.
Tel. +27 31 717 4000 Direct. +27 31 717 4146
Fax. +27 31 717 4001
-----------------------------------------------------------------
The little voices are talking to me again, telling me of a long
forgotten rhyme... the Rhyme of the Ancient Sysadmin
# ps -ef | kill -9 `awk '/albatross/{print $2}'`
-----------------------------------------------------------------
That's wierd, man! If u asked me yesterday, I woulda said that putting a lock onto a table should put a single shared or exclusive lock onto each index as well? Are you issuing a LOCK TABLE command? Why is this so? (David, Art, etc, over to you?) Brett Geer wrote in message <95qqtf$ajg$1@news.xmission.com>... > >Solution! thanks for the headsup on the index, forgot about those >puppies, lets take a look, mmmm 3 indexes reference that field. > >Ah yes, lets see, legacy issue (why optimise the query, just add >an index). > >Thanks sue > >brett > >Sue_Davidson@exe.com.au wrote: >> >> I haven't tested this properly, but I think that there is one lock per row >> updated (depending on locking level page or row) plus one per row per index on >> that field. ie if you have one index on ins_pub_company you would get approx 60K >> locks, if you have two indexes on that field you would get 90K locks.