Informix locking problem 2

[mynwo]

Hi All,

I have row level locking for a table. In one session i enter

begin work;
update emp
set name ="jack"
where empno=1

In second sesssion if i say

select * from emp
it gives me an error, cannot do a physical fetch on the table,
but my table has row level locking.

If i update the 10th row in one session, in other session i can view the 1st 9 rows, after that it gives me the same error. This should not happen since table is locked in row mode, it should lock only the current row and display the other rows.

IF i give

set isolation to dirty read;
select * from emp;
i can view all the data but with name with latest changes which may or may not be committed, thus my MIS may go haywire.

Please advice. Appreciate ur prompt reply.

Regards,

lloyd

 

[vpshriyan]

Hi,

--one session
begin; update emp set name="jack" where empno=1; ...

--second session
select * from emp;

The second session bound to report a physical fetch error irrespective of the lock level (row / page) as the default isolation level in a logged database is committed read. As you rightly pointed out, isolation set to dirty read resolves this problem; however it may conclude into phantom results.

That means, should we need 100% results, we have to wait till all manipulations are committed. This is a normal multi-user related issue. You may set SQL LOCK MODE to a desired state, and make it to wait internally, should your query hits such transaction blocks. Like:

SET ISOLATION TO COMMITTED READ ;
SET LOCK MODE TO WAIT ;
SELECT * FROM emp ;

will force the select statement to wait, till transactions are completed if any, i.e., committed or rollbacked.

Regards,
Shriyan
"It takes a lot of patients to be a successful doctor."

 

[mynwo]

Hi Shriyan,

Thanks for your feedback, that means we are helpless in such situation. Ideally if i have row level locking it should only lock the current row, but in this case its not doing. So there is no other alternative but to wait. But this would be a major issue in OLTP env. since there would be many users simultenously accessing the table. Any other workarounds.

Regards,

lloyd

 

[vpshriyan]

Hi lloyd,

If a table is configured for row level locking factor; the database server acquires locks on number of rows that have been manipulated only. When other sessions targets such rows with where clause, it becomes an issue and such row(s) can NOT be bypassed from being read over, even with dirty read isolation.

Hence, a row is always a target if it comes under a where clause of a select. If any such row is under lock (update/delete) there is no way to skip it. Therefore, the engine has to wait for the lock to be released, to get past of the such row.

Multi-user related concurrency issue is very crucial and play major role in OLTP environment. A row considered for manipulation need to be released with-in shortest time as possible. The development, design & implementation of block of codes dealing with concurrency in programs in such scenario either make or break the applications.

The one workaround I can think of is, that to fine tune all the applications that are attached to your database for the minimal lock duration for transactions. For example: If you are updating the information of a row, lock only when you have gathered all the information that to be updated into the row from the user, and do not lock in advance, if your business rule allows.

Regards,
Shriyan
"Failure is only a temporary change in direction to set you straight on the path to your next success.

 

[mynwo]

Hi Shriyan,

Thanks once again for you prompt reply.

Regards,

lloyd

 

[cdlvj]

In addition to Shriyan's reply, we put a date-timestamp on each table.

You read the row, present to user, If the user modifies, then you compare the stamp, and if = update, otherwise inform the user that the record was changed by another user.