2 Replies Latest reply: Sep 26, 2013 12:24 PM by Chrisjenkins-Oracle RSS

    Timesten LOCK issue

    user13343835

      We are facing Timesten LOCK timeout issue in client environment very frequently but it has not happening in local test environment tried to reproduce for many times. Below is the Error message

       

      An exception occured while inserting data to USER_DATA_SATGING_TABLE : [TimesTen][TimesTen 11.2.1.8.0 ODBC Driver][TimesTen]TT6003: Lock request denied because of time-out

      Details: Tran 199.4005 (pid 11169) wants Xni lock on rowid BMUFVUAAABPBAAAOjo, table DATA_STAGING_TABLE. But tran 194.2521 (pid 11169) has it in X (request was X). Holder SQL (DELETE FROM USER_DATA_STAGING_TABLE WHERE STATUS= 'COMPLETE') -- file "table.c", lineno 15230, procedure "sbTblStorLTupSlotAlloc":false

       

      SYS.SYSTEMSTATS output on lock details:

       

      < lock.locks_granted.immediate                                , 5996359, 0, 0 >
      < lock.locks_granted.wait                                     , 0, 0, 0 >
      < lock.timeouts                                               , 8, 0, 0 >
      < lock.deadlocks                                              , 0, 0, 0 >
      < lock.locks_acquired.table_scans                             , 10256, 0, 0 >
      < lock.locks_acquired.dml                                     , 4, 0, 0 >

       

      After doing rollback of transaction using ttXactAdmin, it is going fine without any issue and it is reoccurring in 2/3 days later. 

       

      Previous lock information by using ttXactAdmin command.

       

      PID Context        TransID TransStatus Resource  ResourceID       Mode  SqlCmdID         Name

       

      Program File Name: java

       

      92910x16907fd0      198.6732   Active  Database  0x01312d0001312d00   IX0
                                                     Command   1056230912       S 1056230912

       

          0x16988810      199.4371   Active  Database  0x01312d0001312d00   IX0
                                                     Row   BMUFVUAAABxAAAADD9   X 1055528832       USER.USER_DATA_STAGING_TABLE
                                                     Table 718096           IXn   1055528832       USER.USER_DATA_STAGING_TABLE
                                                     EndScan   BMUFVUAAAAKAAAAGD1   U 1055528832       USER.USER_DATA_STAGING_VIEW
                                                     Table 718176           IXn   1055528832       USER.USER_DATA_STAGING_VIEW
                                                     Command   1056230912       S 1056230912

       

          0x1882e610      201.16275  Active  Database  0x01312d0001312d00   IX0

       

      TTVERSION : TimesTen Release 11.2.1.8.0 (64 bit Linux/x86_64) (tt11:17001) 2011-02-02T02:20:46Z

       

      sys.odbc.ini file configuration:

      UID=user

      PWD=user

      Driver=/opt/TimesTen/tt11/lib/libtten.so

      DataStore=/var/TimesTen/tt11/ds/TT_USER_DS

      LogDir=/var/TimesTen/tt11/ttlog/TT_User

      CacheGridEnable = 0

      PermSize         = 1000

      TempSize         = 200

      CkptLogVolume    = 200

      DatabaseCharacterSet=WE8ISO8859P1

      Connections      = 128

       

       

      Here we are using XLA message listener to get notification a USER.USER_DATA_STAGING_VIEW which in turn looks for a table USER.USER_DATA_STAGING_TABLE. Delete operation on this table locks table and blocks for further insert.

       

      I want to know

      1) why this happening ?

      2) why is this happening in specific environment and not able to reproduce the same in other environment.

      3) how to resolve this issue?

       

      This is very critical issue which blocks us for further move in client solution. Expecting your reply ASAP.

        • 1. Re: Timesten LOCK issue
          Steve - Oracle

          If this is critical then please raise a Service Request with Oracle Support.

          • 2. Re: Timesten LOCK issue
            Chrisjenkins-Oracle

            For critical issues you should always log an SR as Steve advised. These forums are not monitored 24x7 and are not the best way to get rapid assistance for critical problems.

             

            For this issue it seems like a DELETE operation on the table was holding a lock on a row that an INSERT operation needed to get a lock on (looks like an INDEX 'next key' lock). The inserter was not able to acquire the lock within the defined timeout (which by default is 10 seconds unless you have configured it to be different). 10 secodns is a long time so the question is why the DELETE operation was not committed (which would release) the locks within 10 seconds. Maybe it was deleting a very large number of rows? If that is the issue then the best solution is to break such a large delete up into a series of smaller deletes. For example:

             

            repeat

               DELETE FIRST 256 FROM USER_DATA_STAGING_TABLE WHERE STATUS= 'COMPLETE';

               COMMIT;

            until 'rows deleted' = 0

             

            Alternatively you could just increase the lock timeout value (but that is really just 'hiding' the issue).

             

            Chris