This discussion is archived
10 Replies Latest reply: Aug 29, 2012 7:43 AM by 598001 RSS

How I can undestand what transaction is "dirty"?

598001 Newbie
Currently Being Moderated
I Write timesten application using C api.
My code do lots of selects, updates, merges, etc. How i can undestand what I need to call commit?
  • 1. Re: How I can undestand what transaction is "dirty"?
    Gennady Sigalaev Journeyer
    Currently Being Moderated
    Hi Vladimir,
    My code do lots of selects, updates, merges, etc. How i can undestand what I need to call commit?
    It is a very big question :). It is really depends on your application, but basically you have two options:

    - control it yourself (you should know what does your application do and commit transaction when it's necessary) - not a very good idea
    - end user should decide when transaction must be committed - good approach in my opinion of course.

    Best regards,
    Gena
  • 2. Re: How I can undestand what transaction is "dirty"?
    598001 Newbie
    Currently Being Moderated
    Application - network server. No user interaction.

    Maybe exist some method to get count or size of modified, ineserted, or deleted records in current transaction? Also we must count calss like SELECT .. FOR UPDATE;
  • 3. Re: How I can undestand what transaction is "dirty"?
    Gennady Sigalaev Journeyer
    Currently Being Moderated
    Maybe exist some method to get count or size of modified, inserted, or deleted records in current transaction? Also we must count calss like SELECT .. FOR UPDATE;
    I am afraid that TimesTen doesn't contain this functionality. Your application should commit transaction when it's necessary based on business logic.



    Regards,
    Gena

    UPD. You can try to use ttXactAdmin utility for receiving information about active transactions, but it is like brain storm.

    Edited by: Gennady Sigalaev on 24-Aug-2012 08:41
  • 4. Re: How I can undestand what transaction is "dirty"?
    ChrisJenkins Guru
    Currently Being Moderated
    Vladimir,
    I Write timesten application using C api.
    My code do lots of selects, updates, merges, etc. How i can undestand what I need to call commit?
    Commit calls delineate transactions (atomic units of work in the database). The concept of what constitutes a transaction is fundamentally and intrinsically determined by the application and its logic. Only the application developer can decide what should constitute a transaction and hence when to call commit. No other metric or guideline makes any sense. You need to consider what kinds of data manipulation your application performs and which operations need to be grouped together and performed atomically. Commit (or rollback) release locks so SELECT operations that acquire locks (such as SELECT FOR UPDATE) should alose be considered when planning your transaction boundaries.

    A few general rules:

    1. Transactions should be small (not too many operations per transaction and not too much data modified per transaction) and short (not open for a long period of time). Large/long transactions can be very problematic in some circumstances.

    2. It is a very bad idea to let the user decide when to commit (sorry Gennady!). User's can go for a coffee, go home in the evening etc. and can leave open and hence long running transactions.

    Chis
  • 5. Re: How I can undestand what transaction is "dirty"?
    Gennady Sigalaev Journeyer
    Currently Being Moderated
    Hi Chris,
    It is a very bad idea to let the user decide when to commit (sorry Gennady!). User's can go for a coffee, go home in the evening etc. and can leave open and hence long running transactions.
    I think it is a very good topic for debates.
    I think you mean the application which could work with TimesTen, don't you?
    I meant a conception the OLTP system on the whole. Developers create application for end users and end users work with application.
    It means that only end user know when business transaction should be committed, otherwise we can get logically inconsistent data. Do you agree?
    Of course, we can get some issues like you mentioned (coffee, go home and etc.) but it is not changing the conception, moreover there are some methods which help with these issues.

    Regards,
    Gena
  • 6. Re: How I can undestand what transaction is "dirty"?
    ChrisJenkins Guru
    Currently Being Moderated
    I'm afraid I fundamentally disagree Gennady (doesn't mean I am right of course :-)).

    In any real-world OLTP system, the application is executing pre-defined logic based on carefully modelled business processes which have then been translated into application code, database transactions etc. Part of that design and implementation should (indeed must)- include very precise definitions of what constitutes a business transaction and how that maps to one or more database transactions. There is no scope for end users to arbitrarily make those decisions; indeed that will result in inconsistent/invalid data.

    There are very few cases where an end user should directly be allowed to decide when to commit or rollback. Sure they can be given an 'Apply' or 'Cancel' option within the application but that is not the same thing at all. Also, good application design should ensure that the application never keeps a database transaction open while waiting for user input/decision. I have seen many production issues caused by that kind of design (and I am speaking generally here not just about TimesTen).

    Chris
  • 7. Re: How I can undestand what transaction is "dirty"?
    Gennady Sigalaev Journeyer
    Currently Being Moderated
    Chris, thank you for your answers.
    In any real-world OLTP system, the application is executing pre-defined logic based on carefully modelled business processes which have then been translated into application code, database transactions etc.
    Absolutely agree.
    Part of that design and implementation should (indeed must)- include very precise definitions of what constitutes a business transaction and how that maps to one or more database transactions.
    Also agree.
    There is no scope for end users to arbitrarily make those decisions; indeed that will result in inconsistent/invalid data.
    I've got some questions.
    There are very few cases where an end user should directly be allowed to decide when to commit or rollback.
    Sure they can be given an 'Apply' or 'Cancel' option within the application but that is not the same thing at all.
    Basically, In most of the application i've seen the end user has only these two options :) Do you think that it's not OLTP system? I think it is.
    For example:
    Let's imagine that we have a business process - create/update customer in bank. People come in bank across the country and a lot of consultants create/update the application.
    The consultant should input customers data and save it (or not). How can we do that without user interaction?
    Also, good application design should ensure that the application never keeps a database transaction open while waiting for user input/decision.
    I have seen many production issues caused by that kind of design (and I am speaking generally here not just about TimesTen).
    I agree with you, but how achieve concurrency without using transaction? What if different people are changing the same records?
    Of course, it depends on blocking strategy but anyway.

    Regards,
    Gena
  • 8. Re: How I can undestand what transaction is "dirty"?
    598001 Newbie
    Currently Being Moderated
    I solve this problem using statement type flag. When statement prepared I assign flag to it.
    typedef enum {
    STMT_UNKNOWN,
    STMT_SELECT,
    STMT_SELECT_FOR_UPDATE,
    STMT_UPDATE,
    STMT_INSERT,
    STMT_MERGE,
    STMT_DELETE,
    STMT_CALL,
    STMT_DDL,
    } stmt_type_t;

    Connection initialy marked as CLEAN.
    after executing every stmt I update connection state
    switch (stmt->type) {
    case STMT_SELECT:
    case STMT_DDL:
    break;
    case STMT_CALL:
    case STMT_UPDATE:
    case STMT_DELETE:
    case STMT_SELECT_FOR_UPDATE:
    case STMT_INSERT:
    case STMT_MERGE:
    case STMT_UNKNOWN:
    if (stmt->connection->transaction_state == TRANSACTION_CLEAN) {
    stmt->connection->transaction_state = TRANSACTION_DIRTY;
    }
    break;
    }

    If call of transaction end i call commit only if connection marked DIRTY.

    where is sample pseudocode:

    function my_function() {
    i=read_some_value();
    if (i==10) {
    update_some_value();
    }
    }

    start_transaction;
    my_function();
    end_trnsaction;

    In this code I must call commit only if update_some_value function called.
  • 9. Re: How I can undestand what transaction is "dirty"?
    ChrisJenkins Guru
    Currently Being Moderated
    That should (mostly) work (though note that a SELECT that retrieves a value from a sequence also needs to be committed) but it seems like a lot of work fior a very minor optimisation. Why not just always call commit in the 'end_transaction' call? What are you hoping to achieve/gain by only calling commit in some circumstances?

    Chris
  • 10. Re: How I can undestand what transaction is "dirty"?
    598001 Newbie
    Currently Being Moderated
    Below you can see 2 reports. First before commit optimzattion, second - after.
    CPS column contain CallPerSecond for this operation. Total - time in ms spend for this operation per second (CPS*( ExecTime,FetchTime))
    I decrease commits count from 60000 to ~10000 and also get >10% optimization in time spend to database operations. From 950 to 854.

    <pre>
    Counter | TtlCount | TtlExec | ExecTime | TtlFetch | FetchTime | CPS | ExecTime | FetchTime | Total | MaxExec | MaxFetch
    -------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
    session.commit | 1151803 | 3842.320000 | 0.003336 | 0.000000 | 0.000000 | 38377.294287 | 0.003327 | 0.000000 | 127.675043 | 1.013000 | -0.001000
    session.stmt_insert_session | 60014 | 3721.153000 | 0.062005 | 0.000000 | 0.000000 | 1999.370472 | 0.062121 | 0.000000 | 124.202942 | 2.231000 | -0.001000
    spr.stmt_get_subscriber | 120028 | 850.482000 | 0.007086 | 235.852000 | 0.001965 | 3998.740945 | 0.007149 | 0.001989 | 36.543205 | 0.138000 | 0.220000
    spr.stmt_update_subscriber_last_online | 60015 | 1057.967000 | 0.017628 | 0.000000 | 0.000000 | 1999.370472 | 0.017695 | 0.000000 | 35.378739 | 0.996000 | -0.001000
    spr.stmt_get_profile_accums_by_subscr_id_ex | 60014 | 1031.184000 | 0.017182 | 20.358000 | 0.000339 | 1999.370472 | 0.017335 | 0.000333 | 35.322945 | 0.229000 | 0.148000
    session.stmt_merge_session_blob_data | 30004 | 1037.709000 | 0.034586 | 0.000000 | 0.000000 | 999.485259 | 0.034952 | 0.000000 | 34.934290 | 0.944000 | -0.001000
    session.stmt_delete_session | 120025 | 1036.781000 | 0.008638 | 0.000000 | 0.000000 | 3998.740945 | 0.008647 | 0.000000 | 34.576831 | 1.111000 | -0.001000
    spr.commit | 630633 | 989.594000 | 0.001569 | 0.000000 | 0.000000 | 21009.988053 | 0.001575 | 0.000000 | 33.081002 | 0.189000 | -0.001000
    spr.stmt_get_subscriber_services | 60014 | 858.312000 | 0.014302 | 127.486000 | 0.002124 | 1999.370472 | 0.014267 | 0.002132 | 32.787536 | 1.016000 | 0.117000
    -------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
    SUMM | 3870958 | 25792.545000 | 0.000000 | 2656.631000 | 0.000000 | 128968.394428 | 0.000000 | 0.000000 | 949.994641

    Counter | TtlCount | TtlExec | ExecTime | TtlFetch | FetchTime | CPS | ExecTime | FetchTime | Total | MaxExec | MaxFetch
    -------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
    session.stmt_insert_session | 80007 | 4655.627000 | 0.058190 | 0.000000 | 0.000000 | 2001.484092 | 0.059144 | 0.000000 | 118.376501 | 1.232000 | -0.001000
    session.commit | 201195 | 3407.578000 | 0.016937 | 0.000000 | 0.000000 | 5031.454464 | 0.017148 | 0.000000 | 86.280971 | 1.655000 | -0.001000
    session.stmt_merge_session_blob_data | 40006 | 1603.433000 | 0.040080 | 0.000000 | 0.000000 | 1000.792035 | 0.038003 | 0.000000 | 38.033497 | 1.695000 | -0.001000
    spr.stmt_get_subscriber_services | 80007 | 1120.113000 | 0.014000 | 172.496000 | 0.002156 | 2001.284133 | 0.014329 | 0.002210 | 33.099622 | 0.193000 | 0.134000
    spr.stmt_get_profile_accums_by_subscr_id_ex | 80007 | 1239.963000 | 0.015498 | 29.798000 | 0.000372 | 2001.384112 | 0.015720 | 0.000374 | 32.211007 | 0.161000 | 0.079000
    session.stmt_delete_session | 159991 | 1258.926000 | 0.007869 | 0.000000 | 0.000000 | 4001.168557 | 0.008034 | 0.000000 | 32.143721 | 1.113000 | -0.001000
    spr.stmt_get_subscriber | 160014 | 989.738000 | 0.006185 | 249.226000 | 0.001558 | 4002.568266 | 0.006409 | 0.001595 | 32.035443 | 0.259000 | 0.159000
    session.stmt_get_peer_info | 160018 | 1128.148000 | 0.007050 | 75.181000 | 0.000470 | 4001.668453 | 0.007087 | 0.000472 | 30.247515 | 0.172000 | 0.152000
    session.stmt_get_connection | 160018 | 872.399000 | 0.005452 | 292.739000 | 0.001829 | 4001.668453 | 0.005566 | 0.001883 | 29.810805 | 0.166000 | 0.274000
    session.stmt_get_peer_info_by_id | 200039 | 812.547000 | 0.004062 | 339.433000 | 0.001697 | 5002.660447 | 0.004103 | 0.001714 | 29.100653 | 0.179000 | 2.858000
    -------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
    SUMM | 3181882 | 30538.665000 | 0.000000 | 3118.943000 | 0.000000 | 79599.259274 | 0.000000 | 0.000000 | 853.898960
    </pre>

Legend

  • Correct Answers - 10 points
  • Helpful Answers - 5 points