5 Replies Latest reply: Jan 30, 2013 5:18 AM by 957803 RSS

    SQL Tracing in TimesTen with actual data visible

    957803
      Hi,

      We're having some issues with our TimesTen-based application where under some circumstances (it appears to increase under load) the results appear to get 'mixed up', i.e. a query appears to not have received the right result set from the database. I know the result set is wrong because we're doing performance testing where each query should actually return exactly the same.

      I also say 'appears' because I think the problem is on the client side, where multiple threads access the database simultaneously and somehow things get mixed up. This happens so deep in the application that increasing the logging on client side didn't tell me what I need to know thus far.. And before I look further, I want to exclude the possibility that the database is mixing up results from queries processed in parallel.

      To accomplish that I would like to enable a TimesTen trace in ttTraceMon where the actual data returned as a result set to the client is visible. Is this possible? There are a lot of components you can trace, I haven't tried them all but at least the SQL component does not show this information, not even at the highest trace level.

      Is there any way to get a view of the actual result set that is being returned to a client, before that client's driver or the application (we use JDBC) actually interprets the results?

      Any clues are greatly appreciated! Thanks in advance.

      We use TimesTen 11.2.2.

      Kind regards,
      Pieter van Wijngaarden
        • 1. Re: SQL Tracing in TimesTen with actual data visible
          Chrisjenkins-Oracle
          Hi,

          I'm not aware of any internal trace mechanism that will display the data but I am looking into it. A few questions to maybe help clarify what may be the problem area(s):

          1. Are you using direct mode or client/server connectivity for the application?

          2. What kind of queries are you running that seem to show incorrect results? Are they simple single table lookups returning just a few rows or complex queries with joins etc.? Can you give any examples and an example of correct versus incorrect results?

          3. Are there any updates (i.e. insert/update/delete) occurring on those same tables while they are being queried either from the application or from some other soucre (replication, cache refresh etc.)?

          4. Does your application share database connections or other connection related objects (prepared statements, result sets etc.) across application threads? If so, does the application follow all the rules for doing that safely?

          5. Can you please indicate the exact TT version you are using (output of ttVersion command)?

          Chris

          Edited by: ChrisJenkins on Jan 28, 2013 2:35 PM
          • 2. Re: SQL Tracing in TimesTen with actual data visible
            957803
            Hi Chris,

            Thanks for your response. To answer your questions:

            1. Are you using direct mode or client/server connectivity for the application?
            We use client/server connectivity

            2. What kind of queries are you running that seem to show incorrect results? Are they simple single table lookups returning just a few rows or complex queries with joins etc.? Can you give any examples and an example of correct versus incorrect results?
            The queries we see are mostly simple queries, where we receive one or two records from a table with about 240000 records (at least, that was the situation during the testing).

            3. Are there any updates (i.e. insert/update/delete) occurring on those same tables while they are being queried either from the application or from some other soucre (replication, cache refresh etc.)?
            No, no simultaneous updates are happening while the data is queried.

            4. Does your application share database connections or other connection related objects (prepared statements, result sets etc.) across application threads? If so, does the application follow all the rules for doing that safely?
            yes it definitely shares database connections, I'm not sure about prepared statement objects. result sets should never be shared among threads, and we're not sure but we think the application does NOT follow all the rules for doing that safely :)

            5. Can you please indicate the exact TT version you are using (output of ttVersion command)?
            [timesten@lx122 ~]$ ttVersion
            TimesTen Release 11.2.2.2.0 (64 bit Linux/x86_64) (tt1122:53399) 2011-12-23T09:26:28Z

            I'd still really like to know the answer to the question I initially posted, but with the specific problem we're facing now we are already quite certain it is the application server, not the database.

            Kind regards,
            Pieter
            • 3. Re: SQL Tracing in TimesTen with actual data visible
              Jspalmer-Oracle
              No, there's no way to see the result set via tracing in TT. You could use tcpdump to dump out the actual TCP packets being sent from TT to a client, and use various flags to look for recognisable strings of data and identify particular ports that it's being sent to/from. You can use lsof to determine which ports are being used by the client connections. That way you might be able to prove certain result set data is sent to a particular client port at a particular timestamp, and then compare against what happens to it once the application does something with it.
              • 4. Re: SQL Tracing in TimesTen with actual data visible
                Chrisjenkins-Oracle
                The answer sadly is no. There is not currently any trace level that will show data values.

                If you really need to share database connections between application threads then it should be done very carefully. Some rules to observe are:

                1. When a thread is using a connection it should have exclusive use of it. Do not allow multiple threads to make concurrent use of the same connection; TimesTen has internal mutex protection to avoid this causing problems for the database but due to the nature of the ODBC and JDBC APIs it is very hard at an application level to ensure correctness with this approach.

                2. Many objects (statements, result sets etc.) are closely associated with a specific connection. When a thread has 'relinquished control' of a connection it should not then manipulate any object associated with that connection in any way whatsoever.

                For example:

                1. Thread 1 'reserves' connection 1.

                2. Thread 1 issues a query on connection 1 and obtains a result set.

                3. Thread 1 'releases' connection 1.

                4. Thread 2 'reserves' connection 1 and starts to do some work.

                5. Thread 1 starts to process values from the result set obtained in step 2.

                At this point you have two threads working concurrently on connection 1 and all bets are off (and certainly you will see problems).

                Code that violates these rules may seem to work okay with some databases but not with others. That is not the fault of the database but just a happy coincidence. This code pattern is just incorrect.

                Also please note that, unlike Oracle DB, in TimesTen a commit or rollback operation on a connection closes / invalidates all result sets associated with that connection so again if you share connections in a way similar to the above this can also cause you problems.

                Good luck with your debugging.

                Chris

                Edited by: ChrisJenkins on Jan 30, 2013 10:03 AM
                • 5. Re: SQL Tracing in TimesTen with actual data visible
                  957803
                  Thanks for the tips and advice guys! This definitely helps.