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
Original Message:
Sent: 2/20/2023 3:37:00 AM
From: Sebastien FLAESCH
Subject: Informix SELECT FOR UPDATE lock retention
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
------------------------------