Two processes accessing the same table.
I have a table which includes this:
LOCK MODE ROW
I open a cursor thus:
SELECT reader, filename, readstatus FROM tableA WHERE filename
= 'xxxxxx' FOR UPDATE
I execute next on this cursor compiling a list of readers with a particular
readstatus.
I then sleep before closing the cursor so I can test the behaviour of
another process that accesses one of the same rows in the same table thus:
EXEC SQL SET ISOLATION TO COMMITTED READ;
UPDATE user_file_read_table SET readStatus = 2 WHERE reader = 'joblogs'
AND fileName = 'xxxxxxx'
carry out some actions.....
UPDATE user_file_read_table SET readStatus = 1 WHERE reader = 'joblogs'
AND fileName = 'xxxxxxx'
My expoectation, given the locking status is that the actions will not get
carried out until the first process stops sleeping and unlocks its cursor,
but contrary to that the second process just sails on through.
Any suggestions ?
Andrew H
sending to informix-list
On Tue, 13 Jan 2004 07:16:24 -0500, Andrew Hardy wrote:
Did you FETCH the row with filename = 'xxxxxxx'? With most (all?) isolation
levels the row is not locked until it is fetched from the server. You can
check that with onstat -k and look for Intent Exclusive (IX) locks with the
row's pagenumber:slot number (basically it's rowid if not a fragmented table).
Art S. Kagel
> I have a table which includes this:
>
> LOCK MODE ROW
>
> I open a cursor thus:
>
> SELECT reader, filename, readstatus FROM tableA WHERE filename = 'xxxxxx'
> FOR UPDATE>
> I execute next on this cursor compiling a list of readers with a particular
> readstatus.
>
> I then sleep before closing the cursor so I can test the behaviour of
> another process that accesses one of the same rows in the same table thus:
>
> EXEC SQL SET ISOLATION TO COMMITTED READ; UPDATE user_file_read_table SET
> readStatus = 2 WHERE reader = 'joblogs' AND fileName = 'xxxxxxx'
>
> carry out some actions.....
>
> UPDATE user_file_read_table SET readStatus = 1 WHERE reader = 'joblogs'
> AND fileName = 'xxxxxxx'>
> My expoectation, given the locking status is that the actions will not get
> carried out until the first process stops sleeping and unlocks its cursor,
> but contrary to that the second process just sails on through.
>
> Any suggestions ?
>
> Andrew H
>
>
>
> sending to informix-list