Weird behavior with locks during delete.

Hi there, I am running into a problem where I can't see to wrap my head around. I'll try to provide all the information that I can think of, but please don't hesitate to ask if you think I didn't mention something. Current setup: * 14.10.FC9W1DE via the test docker containers * DEF_TABLE_LOCKMODE row * USELASTCOMMITTED "COMMITTED READ" As I am testing there are no connections that could cause locking issues aside from what I am showing below now. The test setup consists of the following queries: CREATE TABLE testing (testval INTEGER ) ; INSERT INTO testing (testval ) VALUES ( 1 ) ; INSERT INTO testing (testval ) VALUES ( 2 ) ; So basically we create a simple table and insert some values. Now we run the following queries in one dbaccess session: BEGIN WORK; DELETE FROM testing WHERE testval = 1;Leaving this transaction open we can observe (truncated) the following locks: IBM Informix Dynamic Server Version 14.10.FC9W1DE -- On-Line -- Up 00:08:52 -- 173256 Kbytes 2023-04-25 11:05:27Locks address wtlist owner lklist type tblsnum rowid key#/bsiz DML table_name 4419a1c0 0 4560b1a8 4419a798 HDR+X 100196 101 0 D testing:informix.testing 4419a798 0 4560b1a8 441a3c58 HDR+IX 100196 0 0 testing:informix.testing 9 active, 20000 total, 16384 hash buckets, 0 lock table overflows Now when I execute this in another connection: SET LOCK MODE TO WAIT 10; DELETE FROM testing WHERE testval = 2;I immediately get: 243: Could not position within table (informix.testing). 107: ISAM error: record is locked.Which begs the question: Why does informix not wait 10 seconds here for the lock to time out? If I set back USELASTCOMMITTED to "NONE" it properly waits (but that is not an option due to other reasons) 10 seconds and errors out or successfully deletes the row if I commit the other transaction in the meantime. Am I missing something here or am I running into a bug? Thanks, Florian ------------------------------ Florian Apolloner ------------------------------

I'd say you are hitting a bug. It should not be blocking on any lock since you have DEF_LOCK_MODE set to ROW so row level locking should not be locking out row 2 when row 1 is being deleted. That's the first problem. Second is that, yes, the lock wait 10 should hold the error for 10 seconds waiting for the lock to clear not return immediately with an error. Art ------------------------------ Art S. Kagel, President and Principal Consultant ASK Database Management Corp. www.askdbmgt.com ------------------------------

The reason that you are running into the lock even when updating a different row is that there is no index on testing(testval), so when the second delete runs, it bumps into the lock, but I expect that you know that. Even with the USELASTCOMMITTED it seems that the second delete cannot get past that lock, and instead fails immediately rather than waiting 10 seconds. I am not sure why that would be, but I think that it is because you are trying to change data, rather than just read it, but you would still hope that the wait would kick in. With the index in place, you can read where testval=2. You can also read the record where testval=1, which shows the USELASTCOMMITTED is working. However you still cannot delete or update where testval=1 because the USELASTCOMMITTED only impacts what you can READ. So the problem is why is the lock mode wait not doing anything when USELASTCOMMITTED = COMMITTED READ and running a DELETE. I did notice that when doing an UPDATE within the transaction instead, the second DELETE does wait. Maybe, if we think that what we are doing is getting the last committed value, then the delete is "committed" but the lock is still on there so the second delete cannot get past it. I expect that's what is happening, but I am not sure that is expected behavior. I would have expected that the second delete would essentially ignore the locks (work like a DIRTY READ) until it got to the row I want to process, but that is not the case. ------------------------------ Mike Walker xDB Systems, Inc www.xdbsystems.com ------------------------------

Hi Florian, I agree with the explanation from Mike. The solution is - to create an index or - my preferred solution, is you set inside the session before the delete: set environment uselastcommitted 'none'; set isolation dirty read; The not waiting 10 seconds sounds like a bug in this special situation (I agree with Art). Regards Henri ------------------------------ Henri Cujass leolo IT, CTO Germany IBM Champion 2021 2022 2023 IIUG Board Member ------------------------------

Hi Mike, Indeed; with an index on the column it seems to work. Kinda makes sense since it doesn't have to scan the table now to find the rows. Cheers, Florian ------------------------------ Florian Apolloner ------------------------------

Hi Henri and Art, Thank you for confirming my suspicion about this being a bug. We will check if we can get this submitted as defect. As far as Henri's suggestions go, I'd rather avoid changing USELASTCOMMITED inside a session since at the point of the DELETE (in the actual code, not my minified example) I might already be in a transaction that already did other things (not sure if I can even change it there) and this might happen with basically every DELETE over the whole codebase. Cheers, Florian ------------------------------ Florian Apolloner ------------------------------

Not quite seeing the problem - here's what I'm trying: Preparation: DROP TABLE testing; CREATE TABLE testing (testval INTEGER) lock mode row; INSERT INTO testing (testval) VALUES (1); INSERT INTO testing (testval) VALUES (2);-- no index for now -- create index tti on testing (testval); Raising the lock, in its own persisting session: BEGIN WORK; UPDATE testing SET testval=2 WHERE testval=1;The actual test: SET LOCK MODE TO WAIT 5; SET ISOLATION COMMITTED READ LAST COMMITTED;SELECT CURRENT; SELECT * FROM testing WHERE testval=2;SELECT CURRENT; DELETE FROM testing WHERE testval=2;SELECT CURRENT; The first select returns one row (the committed match, i.e. row #2) immediately - no waiting and no error. The second select waits the desired 5 seconds before returning 244/154. I intentionally changed the "raising the lock" part to an UPDATE instead of DELETE to better highlight how that lock could protect an entity that's potentially relevant for the concurring operation's WHERE clause: should the WHERE clause see the old (or even the new matching) value, should it wait, should its statement fail or succeed? And apparently the statement type does make a difference at that, indicating (?) that COMMITTED READ LAST COMMITTED only applies to SELECTs? The documentation states (somewhat fuzzily): The LAST COMMITTED keywords are only effective with concurrent read operations. They cannot prevent locking conflicts or errors that can occur when concurrent sessions attempt to write to the same row. Andreas ------------------------------ Andreas Legner ------------------------------

Hi Andreas, You are running an UPDATE after BEGIN WORK, my example has a DELETE there. So we are not really testing the same thing here, are we? Thanks, Florian ------------------------------ Florian Apolloner ------------------------------

Andreas, That's the same behavior that I observed yesterday - if an UPDATE is run inside the transaction, the second session with the DELETE will wait and then timeout. However, the behavior is different if a DELETE is run in the transaction, which causes the second session to return a lock error immediately. ------------------------------ Mike Walker xDB Systems, Inc www.xdbsystems.com ------------------------------

Indeed - and the error changes to 243/107, as described by Florian. So the lock apparently is incurred in a different way ... and the crucial difference is between 154 and 107, not 244 vs. 243. There might be an explanation for this, but it's not obvious, so if important, please raise a support case. ------------------------------ Andreas Legner ------------------------------