Professional Web Applications Themes

transaction id and tranasction isolation - IBM DB2

Hi Two questions. I am using DB2v8 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 or register that will return it to me. At the moment I am using the 'user' register 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 ...

  1. #1

    Default transaction id and tranasction isolation

    Hi

    Two questions. I am using DB2v8 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 or
    register that will return it to me. At the moment I am using the
    'user' register 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 stability with no luck (I have also tried cursor stability). In
    SQLServer to achieve this I needed to set a hint as well - is there
    something extra I should be setting in DB2 or will I not be able to
    achieve this behaviour?

    TIA
    Alex
    Alex Guest

  2. #2

    Default Re: transaction id and tranasction isolation

    Alex <azp74hotmail.com> wrote:
    > Hi
    >
    > Two questions. I am using DB2v8 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 or
    > register that will return it to me. At the moment I am using the
    > 'user' register as the nearest I could find. Any suggestions?
    You could use the application id
    ([url]http://www7b.boulder.ibm.com/dmdd/library/techarticle/0302stolze/0302stolze.html[/url])
    and count the transactions of each session/connection yourself in the
    application.
    > 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.
    Any isolation level other than UR (uncommitted read) will de that for you.
    > I have set my isolation level to
    > read stability with no luck (I have also tried cursor stability).
    What means "no luck"?
    What is the error message you got? Did you get an error message at all or
    was one applicition just "hanging"?
    > In
    > SQLServer to achieve this I needed to set a hint as well - is there
    > something extra I should be setting in DB2 or will I not be able to
    > achieve this behaviour?
    No, no hint is necessary for such standard scenarios.

    --
    Knut Stolze
    Information Integration
    IBM Germany / University of Jena
    Knut Stolze Guest

  3. #3

    Default Re: transaction id and tranasction isolation

    I realise I may not have been quite clear enough.

    I have opened my DB2 control centre/clp and, before connecting to the
    database I have set the isolation to cursor stability (read committed)
    and set autocommit off using the command 'update command options using
    c off'. I used 'change isolation to cs' to change the txn isolation.
    Both commands executed ok.

    Then, I have connected to the same database using an ODBC based tool.
    When configuring my DSN I have ensured that autocommit is off and the
    transaction isolation is also set to cursor stability.

    The ODBC user updates a table in the database. This update causes a
    trigger to fire and write to a second table. The ODBC user has not
    yet committed.

    The DB2 user tries to read the table the trigger wrote to: the
    command centre just sits and waits until the ODBC user executes a
    commit or rollback. As soon as the ODBC user does this then the DB2
    user gets the results back.

    However, I want the DB2 user to be able to read around the uncommitted
    row/s that the ODBC user might be writing and read only already
    committed stuff, without having to wait for a commit.

    Am I doing something wrong, now that I have explained more fully what
    I'm doing, or am I not going to be able to get this behaviour out of
    DB2?

    TIA (again!)
    Alex
    Alex Guest

  4. #4

    Default Re: transaction id and tranasction isolation

    "Alex" <azp74hotmail.com> wrote in message
    news:dca1064b.0307220310.5e3fab8aposting.google.c om...
    > I realise I may not have been quite clear enough.
    >
    > I have opened my DB2 control centre/clp and, before connecting to the
    > database I have set the isolation to cursor stability (read committed)
    > and set autocommit off using the command 'update command options using
    > c off'. I used 'change isolation to cs' to change the txn isolation.
    > Both commands executed ok.
    >
    > Then, I have connected to the same database using an ODBC based tool.
    > When configuring my DSN I have ensured that autocommit is off and the
    > transaction isolation is also set to cursor stability.
    >
    > The ODBC user updates a table in the database. This update causes a
    > trigger to fire and write to a second table. The ODBC user has not
    > yet committed.
    >
    > The DB2 user tries to read the table the trigger wrote to: the
    > command centre just sits and waits until the ODBC user executes a
    > commit or rollback. As soon as the ODBC user does this then the DB2
    > user gets the results back.
    >
    > However, I want the DB2 user to be able to read around the uncommitted
    > row/s that the ODBC user might be writing and read only already
    > committed stuff, without having to wait for a commit.
    >
    > Am I doing something wrong, now that I have explained more fully what
    > I'm doing, or am I not going to be able to get this behaviour out of
    > DB2?
    >
    > TIA (again!)
    > Alex
    Much better explanation. However, it might help to understand exactly what
    the SQL is (along with DDL of the objects), because it is not clear if we
    are talking about updates of existing rows, or inserts of new rows (caused
    by the trigger). There is obviously a contention problem here, and there is
    difference between ignoring inserted rows that have not been committed, and
    being able to read an existing row that another process has an exclusive
    lock on.


    Mark A Guest

  5. #5

    Default Re: transaction id and tranasction isolation

    >
    > Much better explanation. However, it might help to understand exactly what
    > the SQL is (along with DDL of the objects), because it is not clear if we
    > are talking about updates of existing rows, or inserts of new rows (caused
    > by the trigger). There is obviously a contention problem here, and there is
    > difference between ignoring inserted rows that have not been committed, and
    > being able to read an existing row that another process has an exclusive
    > lock on.
    Not a problem. At this stage everything is relatively simple.

    The table the user is writing to is just:

    table1 (prim integer primary key,
    sec integer);

    The table that the trigger is writing to is:

    audittable (user_id varchar(128) not null,
    utcdate timestamp not null,
    sql_statement varchar(8000) not null,
    primary key(user_id, utcdate));

    table1 has the update trigger on it, so that when a user updates the
    table the trigger inserts a row in to the audittable, recording the
    user, date/time and a string of information.

    So, user1 begins a transaction, updates table1, audittable gets
    written to (row n), but as user1 has not issued a commit statement
    neither the change to table1 nor the insert to the audittable have
    been committed. I would still expect user2 to be able to read rows 1
    .... n-1 (that have been committed previously) from the audittable and
    only be able to read row n when user1 has issued a commit statement.

    [SQL

    user1: begin;
    update table1 set sec = 4 where prim = 1;
    -- table1 is updated and a trigger is fired which does:
    -- insert into audittable ('user1', '23-jul-03 09:34...', 'user1 has
    done something!');

    user2: select * from audittable;
    -- user2's application sits and waits ... I would like user2 to see
    everything that's been committed except the row above

    user1: commit
    -- user2's application now returns all rows from audittable

    end of SQL]

    I can't see why user1 would need locks on any other rows in audittable
    other than the one the trigger has inserted. I most definitely don't
    want user2 to be able to do dirty reads on the audittable either.

    I hope this example makes things clearer!

    TIA (again!)
    Alex
    Alex Guest

  6. #6

    Default Re: transaction id and tranasction isolation


    "Alex" <azp74hotmail.com> wrote in message
    news:dca1064b.0307230139.fb35a50posting.google.co m...
    > >
    > > Much better explanation. However, it might help to understand exactly
    what
    > > the SQL is (along with DDL of the objects), because it is not clear if
    we
    > > are talking about updates of existing rows, or inserts of new rows
    (caused
    > > by the trigger). There is obviously a contention problem here, and there
    is
    > > difference between ignoring inserted rows that have not been committed,
    and
    > > being able to read an existing row that another process has an exclusive
    > > lock on.
    >
    > Not a problem. At this stage everything is relatively simple.
    >
    > The table the user is writing to is just:
    >
    > table1 (prim integer primary key,
    > sec integer);
    >
    > The table that the trigger is writing to is:
    >
    > audittable (user_id varchar(128) not null,
    > utcdate timestamp not null,
    > sql_statement varchar(8000) not null,
    > primary key(user_id, utcdate));
    >
    > table1 has the update trigger on it, so that when a user updates the
    > table the trigger inserts a row in to the audittable, recording the
    > user, date/time and a string of information.
    >
    > So, user1 begins a transaction, updates table1, audittable gets
    > written to (row n), but as user1 has not issued a commit statement
    > neither the change to table1 nor the insert to the audittable have
    > been committed. I would still expect user2 to be able to read rows 1
    > ... n-1 (that have been committed previously) from the audittable and
    > only be able to read row n when user1 has issued a commit statement.
    >
    > [SQL
    >
    > user1: begin;
    > update table1 set sec = 4 where prim = 1;
    > -- table1 is updated and a trigger is fired which does:
    > -- insert into audittable ('user1', '23-jul-03 09:34...', 'user1 has
    > done something!');
    >
    > user2: select * from audittable;
    > -- user2's application sits and waits ... I would like user2 to see
    > everything that's been committed except the row above
    >
    > user1: commit
    > -- user2's application now returns all rows from audittable
    >
    > end of SQL]
    >
    > I can't see why user1 would need locks on any other rows in audittable
    > other than the one the trigger has inserted. I most definitely don't
    > want user2 to be able to do dirty reads on the audittable either.
    >
    > I hope this example makes things clearer!
    >
    > TIA (again!)
    > Alex
    This definitely sounds like a concurrency problem with user2 waiting for a
    lock to be released.

    DB2 defaults to row level locking unless table level locking was defined for
    the table. First make sure that row level locking is being used. Also make
    sure that you are using Cursor Stability in the isolation level to maximize
    the concurrency.

    However there is also the possibility of lock escalation where DB2
    dynamically chooses a table level lock based on a number of parameters. Lock
    escalation can be made less likely by increasing the number of locks allowed
    globally with "maxlocks" or "locklist" parameters, or both, in the database
    configuration file.

    The locklist parm indicates the amount of storage that is allocated to the
    locklist. If DB2 runs out of storage, escalation occurs.

    The maxlocks parm indicates the percentage of locks in the locklist held by
    any one application before lock escalation occurs. So this may need to be
    increased, especially if there are not a lot of other applications running
    at the same time.

    You can determine whether lock escalation is occurring with a snapshot.

    The only other thing I can think of is that there is some kind of lock on
    the index for audit table that user2 is waiting for. Is there an index on
    audittable? In DB2 version 8.1 type 2 indexes are used which helps with
    concurrency on index locks. What DB2 version are you using?


    Mark A Guest

Similar Threads

  1. Multi-server Config Performance Isolation
    By spikehenning in forum Coldfusion Server Administration
    Replies: 0
    Last Post: October 28th, 08:54 PM
  2. Application isolation vs Load balancing and failover
    By Olav69 in forum Coldfusion Server Administration
    Replies: 0
    Last Post: June 8th, 05:57 PM
  3. High Isolation/COM+/Idle Shutdown
    By Kirk Potter in forum ASP Components
    Replies: 1
    Last Post: April 17th, 01:16 AM
  4. Win2K Tx Isolation - verify please
    By Jeremiah Johnson in forum ASP.NET Web Services
    Replies: 1
    Last Post: March 6th, 07:46 PM
  5. Transaction id and transaction isolation
    By Alex in forum Informix
    Replies: 3
    Last Post: July 23rd, 09:33 PM

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