Re: Transaction id and transaction isolation

"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

"Alex" <azp74@hotmail.com> wrote > 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". if your database is ansi logging, then you can not use begin work. Every INSERT/UPDATE/DELETE starts a new transaction automatically. All you want is to end a transaction by COMMIT WORK. for e.g. INSERT INTO ... UPDATE ... DELETE ... COMMIT WORK ; In the above, the first insert will start a transaction and the 3 statments will be committed at COMMIT WORK. They are not auto committed. This is just like Oracle. > 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. No. Your problem is because you are seeing every row as it is auto committed, which is what you don't want. This is what you wrote in your first post:- " 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. " The reason you are seeing every ongoing insert is because there is no transaction boundary defined in your write process. The only way you can prevent your read process from reading ongoing inserts is to define a commit work at the end of write process. > 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. I did not understand this. Pls explain it again. > 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! ravi