Re: Transaction id and transaction isolation

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