Re: Locking DSA 7.13
In article <6mc1ti$337$1@news.xmission.com>, Nick Nobbe <nnob@loc.gov>
writes
>
>Can anyone tell me what the PB front end may be doing with regard to
>locking and isolation?
>
>Env: Online 7.13
> Buffered db
> Schema - all tables to lock mode row
>
>Scenario:
>
> Two PB clients connecting to editinfo table (partnum 100024).
>
> Clients 1 and 2 insert new rows into the table, one row at a time.
>
> Client logs on, goes to a report function and gets the message.
>Informix ODBC 244 - Can't do a physical order read to fetch the next row.
>Just trying to do a select on the same table with dbaccess, I get the same
>ISAM 107 error: record is locked.
>
> If clients or sql users other than clients 1 and 2 want to select only
>(no update) and set isolation to dirty read, can they fetch records in this
>table? Can they update rows in this table?
>
They should be able to fetch rows as long as they are not fetching FOR
UPDATE. They will not be able to update.
> I gather from the following outputs (some details omitted to spare
>your eyes) that
>
>1. inserts are being done
>
>2. locking is set to not wait
>
>3. a large number of row locks have been applied and not released (and this
>seems to be the most puzzling part)
>
One lock per row + one per index key
the K-1 locks are index keys..
>4. clients 1 and 2 have placed an intent exclusive lock on the table. Why,
>if a row lock would be sufficient?
>
It stops someone dropping the table whilst it si in use.
>
>RSAM Version 7.10.UC3 -- On-Line -- Up 2 days 21:59:04 -- 12904 Kbytes
>
>Userthreads
>address flags sessid user tty wait tout locks nreads
>nwrites
>80c10ba4 ---P--D 0 informix - 0 0 0 52 32
>80c10f34 ---P--F 0 informix - 0 0 0 0 0
>80c112c4 ---P--F 0 informix - 0 0 0 0 0
>80c11654 ---P--B 3 informix - 0 0 0 0 0
>80c119e4 ---P--D 0 informix - 0 0 0 0 0
>80c1b314 Y-BP--- 59 jan JYAN 810f5150 0 36 1 41
>80c1ba34 Y-BP--- 57 vlg VGRO 81176068 0 83 5 92
> 7 active, 50 total
>
>
>RSAM Version 7.10.UC3 -- On-Line -- Up 2 days 21:59:29 -- 12904 Kbytes
>
>Sess SQL Current Iso Lock SQL ISAM F.E.
>Id Stmt type Database Lvl Mode ERR ERR Vers
>59 INSERT bcs CR Not Wait 0 0 7.20
>
>Current SQL statement :
> INSERT INTO informix.editinfo ( conno, edit_info, initials, lockflag )
> VALUES ( '98991029', ?, 'jan', 'Y' )>
>Last parsed SQL statement :
> INSERT INTO informix.editinfo ( conno, edit_info, initials, lockflag )
> VALUES ( '98991029', ?, 'jan', 'Y' )>
>
>RSAM Version 7.10.UC3 -- On-Line -- Up 2 days 21:59:35 -- 12904 Kbytes
>
>Sess SQL Current Iso Lock SQL ISAM F.E.
>Id Stmt type Database Lvl Mode ERR ERR Vers
>57 SELECT bcs CR Not Wait 0 0 7.20
>
>Current statement name : s0002
>
>Current SQL statement :
> SELECT conno, edit_info, s2in,
> initials, lockflag FROM informix.editinfo WHERE ( conno =
> '98990506' )>
>Last parsed SQL statement :
> SELECT conno, edit_info, s2in,
> initials, lockflag FROM informix.editinfo WHERE ( conno =
> '98990506' )>
>
>RSAM Version 7.10.UC3 -- On-Line -- Up 2 days 21:59:45 -- 12904 Kbytes
>
>Locks
>address wtlist owner lklist type tblsnum rowid key#/bsiz
>80c244b0 0 80c1ba34 80c244dc HDR+IX 100024 0 0
>80c244dc 0 80c1ba34 0 HDR+S 100002 202 0
>80c24508 0 80c1ba34 80c24534 HDR+X 100024 93c04 K-1
>80c24534 0 80c1ba34 80c245e4 HDR+X 100024 93c04 0
>80c24560 0 80c1ba34 80c244b0 HDR+X 100024 93e02 0
>80c2458c 0 80c1ba34 80c24560 HDR+X 100024 93c03 0
>80c245b8 0 80c1ba34 80c2458c HDR+X 100024 93c03 K-1
>80c245e4 0 80c1ba34 80c245b8 HDR+X 100024 94301 0
>80c24610 0 80c1ba34 80c24508 HDR+X 100024 94102 0
>80c2463c 0 80c1ba34 80c24610 HDR+X 100024 93c05 0
>80c24668 0 80c1ba34 80c2463c HDR+X 100024 93c05 K-1
>80c24694 0 80c1b314 0 S 100002 202 0
>80c246c0 0 80c1b314 80c24694 IX 100024 0 0
>80c246ec 0 80c1b314 80c246c0 HDR+X 100024 94401 0
>80c24718 0 80c1b314 80c246ec HDR+X 100024 93c06 0
>80c24744 0 80c1b314 80c24718 HDR+X 100024 93c06 K-1
>80c24770 0 80c1b314 80c24ab4 HDR+X 100024 93c0b 0
>80c2479c 0 80c1b314 80c24744 HDR+X 100024 94501 0
>80c247c8 0 80c1b314 80c2479c HDR+X 100024 93c07 0
>80c247f4 0 80c1b314 80c247c8 HDR+X 100024 93c07 K-1
>80c24820 0 80c1b314 80c247f4 HDR+X 100024 94601 0
>80c2484c 0 80c1b314 80c24820 HDR+B 100024 94500 1208
>80c24878 0 80c1b314 80c2484c HDR+X 100024 94701 0
>80c248a4 0 80c1b314 80c24878 HDR+X 100024 93c08 0
>80c248d0 0 80c1b314 80c248a4 HDR+X 100024 93c08 K-1
>80c248fc 0 80c1b314 80c248d0 HDR+X 100024 94801 0
>80c24928 0 80c1b314 80c248fc HDR+X 100024 93c09 0
>80c24954 0 80c1b314 80c24928 HDR+X 100024 93c09 K-1
>80c24980 0 80c1ba34 80c24668 HDR+X 100024 94802 0
>80c249ac 0 80c1ba34 80c24980 HDR+B 100024 94100 0
>80c249d8 0 80c1ba34 80c249ac HDR+B 100024 94800 625
>80c24a04 0 80c1b314 80c24954 HDR+X 100024 94803 0
>80c24a30 0 80c1b314 80c24a04 HDR+X 100024 93c0a 0
>80c24a5c 0 80c1b314 80c24a30 HDR+X 100024 93c0a K-1
>80c24a88 0 80c1b314 80c24a5c HDR+X 100024 94901 0
>80c24ab4 0 80c1b314 80c24a88 B 100024 94800 22
>80c24ae0 0 80c1b314 80c24770 HDR+X 100024 93c0b K-1
>80c24b0c 0 80c1b314 80c24ae0 HDR+X 100024 94902 0
>80c24b38 0 80c1b314 80c24b0c HDR+B 100024 94900 0
>80c24b64 0 80c1b314 80c24b38 HDR+X 100024 93c0c 0
>80c24b90 0 80c1b314 80c24b64 HDR+X 100024 93c0c K-1
>80c24bbc 0 80c1b314 80c24b90 HDR+X 100024 94a01 0
>80c24be8 0 80c1ba34 80c249d8 HDR+X 100024 94a02 0
>80c24c14 0 80c1ba34 80c24be8 HDR+X 100024 93c0d 0
>80c24c40 0 80c1ba34 80c24c14 HDR+X 100024 93c0d K-1
>80c24c6c 0 80c1b314 80c24bbc HDR+X 100024 93c0e 0
>80c24c98 0 80c1b314 80c24c6c HDR+X 100024 93c0e K-1
>80c24cc4 0 80c1ba34 80c25194 HDR+X 100024 95501 0
>80c24cf0 0 80c1ba34 80c24c40 HDR+X 100024 94b01 0
>80c24d1c 0 80c1ba34 80c24cf0 HDR+X 100024 93c0f 0
>80c24d48 0 80c1ba34 80c24d1c HDR+X 100024 93c0f K-