Locking in Exclusive mode is not working as I thought!!!
A developer locking a table in EXCLUSIVE mode found other sessions could still SELECT from it (though UPDATEs were blocked), and wanted reads to fail too. The answer: this depends on the reader's isolation level — a DIRTY READ ignores locks, so the reader must use a higher level such as COMMITTED or REPEATABLE READ. When his SET ISOLATION attempts failed with error 256 ("Transaction not available"), he was told the database must be created with logging (transactions) for isolation levels to be available. No further follow-up is recorded.
Auto-generated by DrWatson from the posts below — may be imperfect; read the full thread.
Hi all:
I am working on an JDBC/Informix application in Java and maybe you gurus can
help me out, there's a table called "sys_info" that has 1 row that
contains a control-number to be the next ticket number used.
The idea is for me to be able to lock that table in exclusive mode (so
nobody else can read it(doing a select should result in an error))
and then update the control number by adding 1 to
it and then unlocking the table...
I ran a little test in 2 windows using dbaccess
Window 1 Window 2
"lock table sys_info in exclusive mode" select * from sys_info
I expected Window 2 to give me an error "Table is locked".
Instead, it allowed me to select the row (which is not what I want it to
do). It won't allow me to update it though (which is what I expected) as I
get the table locked message.
I thought that when you have a table locked in exclusive mode, nobody would
even be able to read it... I am trying to get just that same exact
behavior.
Please help :-)
Theron
Depends on the isolation level. A DIRTY READ isolation ignores any
locks on the table in question.
John Carlson
Informix DBA
WHSmith USA
Mild wrote:
>
> Hi all:
>
> I am working on an JDBC/Informix application in Java and maybe you gurus can
> help me out, there's a table called "sys_info" that has 1 row that
> contains a control-number to be the next ticket number used.
>
> The idea is for me to be able to lock that table in exclusive mode (so
> nobody else can read it(doing a select should result in an error))
> and then update the control number by adding 1 to
> it and then unlocking the table...
>
> I ran a little test in 2 windows using dbaccess
>
> Window 1 Window 2
> "lock table sys_info in exclusive mode" select * from sys_info
>
> I expected Window 2 to give me an error "Table is locked".
> Instead, it allowed me to select the row (which is not what I want it to
> do). It won't allow me to update it though (which is what I expected) as I
> get the table locked message.
>
> I thought that when you have a table locked in exclusive mode, nobody would
> even be able to read it... I am trying to get just that same exact
> behavior.
>
> Please help :-)
> Theron
I cannot get the following command to work on our database:
set isolation to repeatable read
or
set isolation to committed read
What happens is that I get an error message:
256 Transaction not available
Please help!!! What am I doing wrong?
And if I try doing:
begin work;
set isolation to ...
It still gives me the error message? :-(
thanks,
Theron
Turn on logging on the database or begin work. Mild (theronk@sprynet.com) wrote: : I cannot get the following command to work on our database: : set isolation to repeatable read : or : set isolation to committed read : What happens is that I get an error message: : 256 Transaction not available : Please help!!! What am I doing wrong? : And if I try doing: : begin work; : set isolation to ... : It still gives me the error message? :-( : thanks, : Theron -- Rob Wilson rwilson@ntsource.com