Professional Web Applications Themes

Transaction id and transaction isolation - Informix

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

  1. #1

    Default 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 Guest

  2. #2

    Default Re: Transaction id and transaction isolation


    "Alex" <azp74hotmail.com> wrote in message news:dca1064b.0307202358.43aac613posting.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 Guest

  3. #3

    Default Re: Transaction id and transaction isolation


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


    rkusenet Guest

  4. #4

    Default 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" <rkusenetsympatico.ca> wrote in message
    > news:<bfgce7$egk00$1ID-75254.news.uni-berlin.de>...
    >> "rkusenet" <rkusenetsympatico.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
    Art S. Kagel Guest

Similar Threads

  1. WS-Transaction
    By moko in forum ASP.NET Web Services
    Replies: 1
    Last Post: August 13th, 12:25 AM
  2. Transaction Log
    By Shamim in forum Microsoft SQL / MS SQL Server
    Replies: 5
    Last Post: August 22nd, 06:25 AM
  3. Transaction
    By basidati in forum Microsoft SQL / MS SQL Server
    Replies: 3
    Last Post: August 8th, 03:41 PM
  4. transaction id and tranasction isolation
    By Alex in forum IBM DB2
    Replies: 5
    Last Post: July 23rd, 11:13 AM

Bookmarks

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •  

1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22 23 24 25 26 27 28 29 30 31 32 33 34 35 36 37 38 39 40 41 42 43 44 45 46 47 48 49 50 51 52 53 54 55 56 57 58 59 60 61 62 63 64 65 66 67 68 69 70 71 72 73 74 75 76 77 78 79 80 81 82 83 84 85 86 87 88 89 90 91 92 93 94 95 96 97 98 99 100 101 102 103 104 105 106 107 108 109 110 111 112 113 114 115 116 117 118 119 120 121 122 123 124 125 126 127 128 129 130 131 132 133 134 135 136 137 138 139