2 Replies Latest reply on Sep 26, 2013 5:24 PM by ChrisJenkins-Oracle

    Timesten LOCK issue


      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 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 (64 bit Linux/x86_64) (tt11:17001) 2011-02-02T02:20:46Z


      sys.odbc.ini file configuration:






      CacheGridEnable = 0

      PermSize         = 1000

      TempSize         = 200

      CkptLogVolume    = 200


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

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

          • 2. Re: Timesten LOCK issue

            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:





            until 'rows deleted' = 0


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