Transaction id and transaction isolation

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

"Alex" <azp74@hotmail.com> wrote in message news:dca1064b.0307202358.43aac613@posting.google.com... > 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 yourdatabase a logged one. You should not be having this problem. You can try set isolation to repeatable read.

"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.

You can get the transaction id from the sysmaster but only after you have done something, the fact in you are in a transaction not available until work is done Alex wrote: > > 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 -- Paul Watson # Oninit Ltd # Growing old is mandatory Tel: +44 1436 672201 # Growing up is optional Fax: +44 1436 678693 # Mob: +44 7818 003457 # www.oninit.com #