Locking DSA 7.13
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?
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)
4. clients 1 and 2 have placed an intent exclusive lock on the table. Why,
if a row lock would be sufficient?
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-1
80c24d74 0 80c1b314 80c24c98 HDR+X 100024 94c01 0
80c24da0 0 80c1b314 80c24d74 HDR+X 100024 93c10 0
80c24dcc 0 80c1b314 80c24da0 HDR+X 100024 93c10 K-1
80c24df8 0 80c1ba34 80c24d48 HDR+X 100024 94d01 0
80c24e24 0 80c1ba34 80c24df8 HDR+B 100024 94a00 173
80c258f8 0 80c1ba34 80c258cc HDR+X 100024 93f09 K-1
119 active, 2000 total, 128 hash buckets
@@NL@