Informix SELECT FOR UPDATE lock retention

| view attached Hello! Context: Informix IDS 12.x and 14.x + CSDK 4.50.FC6 We try to understand in which conditions promotable locks (set by a SELECT FOR UPDATE) cursor are released. The database is without transactions . We wonder why such lock is released, when a second cursor using a simple SELECT fetches the same row. DECLARE c1 CURSOR FOR SELECT * FROM tab1 FOR UPDATE OPEN c1 FETCH c1 <-- sets update/promotable lock DECLARE c2 CURSOR FOR SELECT * FROM tab1 WHERE pkey = ... OPEN c2 FETCH c2 <-- lock set by fetch (3) goes away Is this behavior expected? I can reproduce with a simple ESQL/C program (see attachment). I expect that the lock remains until first cursor is closed. Doc is missing some details about this case. Update cursors Duration of a lock Seb ------------------------------ Sebastien FLAESCH ------------------------------

Hi, this cannot work in a database without transactions. A lock exists per transaction (until you either commit or rollback). This can be a local or global transaction, but without transactions, the lock will only work while the current statement is executed. Same for auto-commit = true .... Best,

Thanks for your quick answer Marcus, but we need a more detailed explanation for this behavior. Try my sample: With second cursor, instead of fetching the row from table "tab1", fetch a row from "tab2", by using this: EXEC SQL DECLARE c2 CURSOR FOR SELECT * FROM tab2 WHERE pkey = $p_pkey; In this case, the lock set on first table/row tab1 by first cursor c1 is not released. So your statement "the lock will only work while the current statement is executed" is wrong to me. Reading the doc: https://www.ibm.com/docs/en/informix-servers/14.10?topic=work-locks-while-modifying "If they ( transactions ) are not in use, the lock is released as soon as the modified row is written to disk." => looks like a second SELECT on the same table to fetch the same row, has the same effect than writing to the disk. Obviously using transactions is best practice, but we have customers still using Informix databases without logging and we need to clarify this behavior. Seb ------------------------------ Sebastien FLAESCH ------------------------------

If I run your attached file I get: >> [DATABASE nolog ]: sqlcode=0 sqlerrd[2]=0 >> [DECLARE c1 ]: sqlcode=0 sqlerrd[2]=0 >> [OPEN c1 ]: sqlcode=0 sqlerrd[2]=0 >> [FETCH c1 ]: sqlcode=0 sqlerrd[2]=0 Database selected. tabname rowidlk type tab1 0 IX tab1 257 U 2 row(s) retrieved. Database closed. >> [DECLARE c2 ]: sqlcode=0 sqlerrd[2]=0 >> [OPEN c2 ]: sqlcode=-217 sqlerrd[2]=0 So row is locked. In your post, you (maybe) have typo: DECLARE c2 CURSOR FOR SELECT * FROM tab1 WHERE pkey = ... OPEN c1 FETCH c1 <-- lock set by fetch (3) goes away You are declaring c2 and open/fetch c 1 - so if you reopen and fetch c1 lock is surely released. HTH Hrvoje ------------------------------ Hrvoje Zokovic ------------------------------

And is your customer aware of the consequences using non-logging databases? e.g. they can lose EVERYTHING up to last backup. Hrvoje ------------------------------ Hrvoje Zokovic ------------------------------

Thank you Hrvoje I will fix the initial post typos. Here is what I get in my env/machine: sf@toro:~/dbvendors/informix/tests/locks01$ esql -o test_locks_01.bin test_locks_01.ec && ./test_locks_01.bin >> [DATABASE test4 ]: sqlcode=0 sqlerrd[2]=0 >> [DECLARE c1 ]: sqlcode=0 sqlerrd[2]=0 >> [OPEN c1 ]: sqlcode=0 sqlerrd[2]=0 >> [FETCH c1 ]: sqlcode=0 sqlerrd[2]=0 Database selected. tabname rowidlk type tab1 0 IX tab1 257 U 2 row(s) retrieved. Database closed. >> [DECLARE c2 ]: sqlcode=0 sqlerrd[2]=0 >> [OPEN c2 ]: sqlcode=0 sqlerrd[2]=0 >> [FETCH c2 ]: sqlcode=0 sqlerrd[2]=1 Database selected. tabname rowidlk type No rows found. Database closed. ------------------------------ Sebastien FLAESCH ------------------------------

About using non-logging db: it's customer choice, and I think they are aware of consequences. We have customers still using Informix SE Seb ------------------------------ Sebastien FLAESCH ------------------------------

Some more config details, if this helps: Informix IDS-14.10.FC6 Attached, my onconfig file. ------------------------------ Sebastien FLAESCH ------------------------------

BTW, I wonder about the error sqlcode=-217 in your case: This is not a lock error: $ finderr -217-217 Column column-name not found in any table in the query (or SLV is undefined). Seb ------------------------------ Sebastien FLAESCH ------------------------------

Yes, I see. As I can see, locks still belong to "first session". Since there's no logging, isolation level is set to NL (no-logging) and "second session" can read that record. Sess SQL Current Iso Lock SQL ISAM F.E. Id Stmt type Database Lvl Mode ERR ERR Vers Explain 301 SELECT nolog NL Not Wait 0 0 9.41 Off Hrvoje ------------------------------ Hrvoje Zokovic ------------------------------

Hello Sebastien, A quick answer is that the behavior is NORMAL fllowing the 6 statements that you used. Let me go explain in mode detail what is happening. DECLARE c1 CURSOR FOR SELECT * FROM tab1 FOR UPDATE in statement 1 This statement declares?? a structure at the front end level (esql/c or 4gl ) process running the program by the name of C1 (to make it short). This statement does not go to the engine (beging not sent to the sqlexec session at the engine. This prepares the area in your program to receive the data returned by the cursor OPEN c1 in statement 2 Ths statement is the first one related to the cursor that goes to the engine. It does a lots of things: - verifies if the user is allowed to perform the SELECT on the table tab1 in your case (read rights related to GRANT) - verifies the existence of all of the columns, etc - verifies if an UPDATE cursor is not already in place on the same table - reserves memory at the session level (SCB : session control block) in the virtual memry segment of the engine - and returns an SQLCA structure that contains the returns codes (SQLCODE, isam error code, etc) In your case, you try to open the same corsor C1 which returns a negative SQLCODE in statement 5 FETCH c1 in statement 3 - reads the row requested from the disk to the structure in memory (engine level); it actually reads a group of rows to avoid performing several disk reads - locks the first row returned to the application using a U lock in order to protect that row from being updated by another session. ONLY ONE U LOCK at a time on a row is permitted; other sessions can read that row of course by no one can update (put an X LOCK ) it while the read is positionned DECLARE c2 CURSOR FOR SELECT * FROM tab1 WHERE pkey = ... in statement 4 - does the same as the first DECLARE: reserve another structure using the name C2 OPEN c1 in statement 5 - this statement tries to open an already open cursor and of course should return an error FETCH c1 in statement 6 This statement will fetch the next row related to the first opened cursor, releases the lock on the first row (the previsouly read row), and puts a lock on the current row pointed to by the cursor. Remeber ONLY U lock at a time can be present. From the performance point of view, the row is returned from the receiving buffer (FET_BUF_SIZE) instead of really reading it from disk. After all of the rows present in the fetch buffer are returned to the application, another buffer pool worth of rows is read from disk and so on. Now, if you change the statement 5 OPEN C1 to OPEN C2, this statement returns an error since there is existing cursor open on the SELECT in question ( cannot have another UPDATE cursor on the same data). And of course the FETCH C2 will return an error as well and the lock stays on the row returnd using statement 3 in your code. You are using a non logged database. The isolation level is DIRTY READ by default; you read uncommitted data. The cursor is closed when you close the cursor explicitely or you commit a transaction if you are in transaction mode (not in your case); whichever comes first. I hope that this helps you. ------------------------------ Hello! Context: Informix IDS 12.x and 14.x?? + ?? CSDK 4.50.FC6 We try to understand in which conditions promotable locks (set by a SELECT FOR UPDATE) cursor are released. The database is without transactions . We wonder why such lock is released, when a second cursor using a simple SELECT fetches the same row. DECLARE c1 CURSOR FOR SELECT * FROM tab1 FOR UPDATE OPEN c1 FETCH c1???? <-- sets update/promotable lock DECLARE c2 CURSOR FOR SELECT * FROM tab1 WHERE pkey = ... OPEN c1 FETCH c1???? <-- lock set by fetch (3) goes away Is this behavior expected? I can reproduce with a simple ESQL/C program (see attachment). I expect that the lock remains until first cursor is closed. Doc is missing some details about this case. Update cursors Duration of a lock -- Cordialement, Regards, Khaled Bentebal Directeur G??n??ral - ConsultiX T??l: 33 (0) 1 39 12 18 00 Mobile: 33 (0) 6 07 78 41 97 Email: khaled.bentebal@consult-ix.fr Site Web: www.consult-ix.fr

Sebastien: The scope of locks, especially exclusive locks for update/delete/insert, is dependent on the isolation level. With an unlogged database, so dirty read isolation, the scope of a lock is a single operation. So, when you execute the second FETCH on C1 (the C2 cursor is irrelevant to this) that would normally, and correctly, release the lock on the row fetched FOR UPDATE by the first FETCH. However, that is not what you did. You executed an OPEN C1 before the second FETCH and that OPEN operation closes the original cursor opened in line 2 which will release the lock held by C1 to that point regardless of what the isolation level was, even in an ANSI MODE logged database which has the most strict default isolation. Art ------------------------------ Art S. Kagel, President and Principal Consultant ASK Database Management Corp. www.askdbmgt.com ------------------------------

OK, I see that the second OPEN should have been OPEN C2. Even so, your first session fetched two rows for update. The lock is only held on each row, in dirty read isolation (marked as NL in onstat reports in an unlogged database), while the row is the "current" row. Once you fetch the second row, whether you updated that first row or not, the lock on the first row is releases. Normal. Art ------------------------------ Art S. Kagel, President and Principal Consultant ASK Database Management Corp. www.askdbmgt.com ------------------------------

Sorry for the confusion, but I did a typo in my first post, the second DECLARE c2 must of course be followed by OPEN c2 / FETCH c2. If you lock at the code sample, you will see that the second DECLARE c2 is not an update cursor (there is no FOR UPDATE clause) I suggest to try my sample, and check that you get the same output as me. I get NO ERROR with the second cursor, and the U lock set by FETCH c1 IS LOST. Seb ------------------------------ Sebastien FLAESCH ------------------------------

Thank you Art, so just to confirm: No matter if I fetch 2 rows from the same cursor, or from one FOR UPDATE cursor and then do a fetch with another regular cursor (both fetching rows from the same table), the U lock set by the first fetch is released at second fetch. Correct? Tested: If the second cursor fetches a row from another table, the U lock is retained. Seb ------------------------------ Sebastien FLAESCH ------------------------------

No, I still see locks: >> [DATABASE nolog ]: sqlcode=0 sqlerrd[2]=0 >> [DECLARE c1 ]: sqlcode=0 sqlerrd[2]=0 >> [OPEN c1 ]: sqlcode=0 sqlerrd[2]=0 >> [FETCH c1 ]: sqlcode=0 sqlerrd[2]=0 Database selected. tabname rowidlk type tab1 0 IX tab1 257 U 2 row(s) retrieved. Database closed. >> [DECLARE c2 ]: sqlcode=0 sqlerrd[2]=0 >> [OPEN c2 ]: sqlcode=0 sqlerrd[2]=0 >> [FETCH c2 ]: sqlcode=0 sqlerrd[2]=1 Database selected. tabname rowidlk type tab1 0 IX tab1 257 U 2 row(s) retrieved. Database closed. Hrvoje ------------------------------ Hrvoje Zokovic ------------------------------

You are operating under DIRTY READ isolation (marked as NL instead of DR because the database is non-logging, but they are the same). You can always read any row, whether locked or not, under DIRTY READ ISOLATION that's kind of why it is called "dirty read" because you may be able to read a dirty row that was changed by another session but not yet committed. In addition, in dirty read isolation, committing is instantaneous and one row at a time since there are no multi-operation transactions available! That's why the second session can read the locked row and why it would see the updated value even though the cursor is still open. Under READ COMMITTED isolation, the default under normal logged mode databases, the second session would still be able to read the row, but it would see the unmodified version. As to why the lock seems to go away once the second session reads the row, I'm not sure why it even lasts that long, because, as I said, the lock on the first row should disappear when the second row is fetched FOR UPDATE. Art ------------------------------ Art S. Kagel, President and Principal Consultant ASK Database Management Corp. www.askdbmgt.com ------------------------------

Hrvoje, Thanks for testing! You see the locks are still there after the second cursor fetch, and this is what my customer and myself are expecting. So the big question is why are these locks released in our case and not with your config? Informix bug with our versions? Can you provide the CSDK et Informix IDS version you have? What OS? I am using Linux Debian 11. Seb ------------------------------ Sebastien FLAESCH ------------------------------

Art, May I suggest that you test my sample, to see if you reproduce? Thanks ------------------------------ Sebastien FLAESCH ------------------------------