Transaction id and transaction isolation

[Alex]

Hi

Two questions. I am using Informix IDS 9.4 on Windows.

1. I would like to be able to get hold of the transaction id while
still in the tranasction but I have been unable to find a function
that will return it to me. At the moment I am using the
dbinfo('sessionid') as the nearest I could find. Any suggestions?

2. I have a table in my database that I would like one user to be
writing to while another user will be reading from it. The user
reading from the table should only be reading already committed rows -
so that any rows that are being added in user 1's transaction that is
ongoing will not be read by user 2. I have set my isolation level to
read committed with no luck. In SQLServer to achieve this I needed to
set a hint as well - is there something extra I should be setting in
Informix or will I not be able to achieve this behaviour?

TIA
Alex

[rkusenet]

"Alex" <azp74@hotmail.com> wrote in message news:dca1064b.0307202358.43aac613@posting.google.c om...

> Hi
>
> Two questions. I am using Informix IDS 9.4 on Windows.
>
> 1. I would like to be able to get hold of the transaction id while
> still in the tranasction but I have been unable to find a function
> that will return it to me. At the moment I am using the
> dbinfo('sessionid') as the nearest I could find. Any suggestions?

I don't think it is possible to get transaction id. Does it even
get released to API??

> 2. I have a table in my database that I would like one user to be
> writing to while another user will be reading from it. The user
> reading from the table should only be reading already committed rows -
> so that any rows that are being added in user 1's transaction that is
> ongoing will not be read by user 2. I have set my isolation level to
> read committed with no luck. In SQLServer to achieve this I needed to
> set a hint as well - is there something extra I should be setting in
> Informix or will I not be able to achieve this behaviour?

set isolation to committed read does not select uncommitted rows. Is your
database a logged one. You should not be having this problem.

You can try set isolation to repeatable read.

[rkusenet]

"rkusenet" <rkusenet@sympatico.ca> wrote

> > 2. I have a table in my database that I would like one user to be
> > writing to while another user will be reading from it. The user
> > reading from the table should only be reading already committed rows -
> > so that any rows that are being added in user 1's transaction that is
> > ongoing will not be read by user 2. I have set my isolation level to
> > read committed with no luck. In SQLServer to achieve this I needed to
> > set a hint as well - is there something extra I should be setting in
> > Informix or will I not be able to achieve this behaviour?

>
> set isolation to committed read does not select uncommitted rows. Is your
> database a logged one. You should not be having this problem.
>
> You can try set isolation to repeatable read.

also make sure that the write user is using begin work and commit work.
If you are not using that, then each insert is an auto commit and that
may lead to the problem you are facing.

[Art S. Kagel]

On Wed, 23 Jul 2003 05:18:15 -0400, Alex wrote:

If you are seeing COMMIT records in the logical log after each statement
then your database is UNLOGGED! ANSI databases REQUIRE COMMIT or
ROLLBACK but do not permit BEGIN WORK inserting an implied BEGIN with the
first statement not part of a transaction.

Art S. Kagel

> "rkusenet" <rkusenet@sympatico.ca> wrote in message
> news:<bfgce7$egk00$1@ID-75254.news.uni-berlin.de>...

>> "rkusenet" <rkusenet@sympatico.ca> wrote

>> > > 2. I have a table in my database that I would like one user to be
>> > > writing to while another user will be reading from it. The user
>> > > reading from the table should only be reading already committed
>> > > rows - so that any rows that are being added in user 1's
>> > > transaction that is ongoing will not be read by user 2. I have set
>> > > my isolation level to read committed with no luck. In SQLServer to
>> > > achieve this I needed to set a hint as well - is there something
>> > > extra I should be setting in Informix or will I not be able to
>> > > achieve this behaviour?
>> >
>> > set isolation to committed read does not select uncommitted rows. Is
>> > your database a logged one. You should not be having this problem.
>> >
>> > You can try set isolation to repeatable read.

>>
>> also make sure that the write user is using begin work and commit work.
>> If you are not using that, then each insert is an auto commit and that
>> may lead to the problem you are facing.

>
> I _think_ my database is has ansi compliant logging - I have managed to
> use the ISA to view my log files and it looks very much as though
> commits are happening after every statement, although I've yet to find
> anything in the ISA which tells me what my current setting is. Also, in
> dbaccess I cannot use the begin work command - I get the message "256:
> transaction not available". But, I would have thought that if every SQL
> statement is auto committing then I would not see the problem I'm
> having, as effectively, there will never be any uncommitted rows in the
> table being written to by the trigger.
>
> I do not want to set my isolation level to repeatable read because I do
> not want the user reading the table to constantly be starting and ending
> transactions. Committed read is definitely the isolation level I want -
> it's just a matter of me understanding Informix enough to set it up
> properly, I think!
>
> Alex