5 Replies Latest reply: Jun 23, 2009 12:55 PM by Chrisjenkins-Oracle RSS

    SQL Developer holding locks/autocommit behavior

      I'm looking for a better workaround for this issue. From the SQL Developer TimesTen extension release notes:

      Commit and rollback behavior for TimesTen DDL and DML operations: Unlike the Oracle database, TimesTen does not implicitly commit DDL transactions. The transactions can be rolled back like DML statements.

      If the "Autocommit in SQL Worksheet" preference is checked, the TimesTen SQL operations within the Connections navigator are automatically committed. The transactions in SQL Worksheet are also committed automatically if there are no active Data tabs. If there are active Data tabs, the transactions in the worksheet are not automatically committed and the user must issue an explicit commit by either clicking the Commit button or by issuing the Commit command in SQL Worksheet.

      When the "Autocommit in SQL Worksheet" preference is not checked, TimesTen SQL operations are not automatically committed. This applies to both SQL Worksheet and the DDL operations available within the Connections navigator. The user must explicitly commit the transactions by clicking the Commit button in SQL Worksheet.

      So when I open a table in SQL Developer, there is an active Data tab. That open cursor holds a lock. I do have Autocommit enabled, but as you can see from the release notes, open data tabs do not autocommit. I have to go into a worksheet and explicitly issue a commit (or click the commit button) to close the cursor and release the lock. Is there a way to automate this?
        • 1. Re: SQL Developer holding locks/autocommit behavior
          I'm not sure I fully understand the issue. Simply having an active data tab for a TimesTen open in SQL Developer does not hold any kind of lock that would cause problems for anyone. Typically the session will be holding an IX database level lock and one or more shared Command locks but these are of no consequence.

          Maybe I am missing something here. You can view the locks that are held in TimesTen usingthe ttXactAdmin command. Could you please explain what problem you are encountering and post the relevant ttXactAdmin output to illustrate it.


          • 2. Re: SQL Developer holding locks/autocommit behavior
            When I open the table, the ttXactAdmin shows a database level lock:
            ttXactAdmin -connStr dsn=COMM1
            2009-06-23 10:35:08.440
            TimesTen Release

            Outstanding locks

            PID Context TransID TransStatus Resource ResourceID Mode

            Program File Name: tkr

            2932 0x848c470 1.42880411 Active

            Program File Name: ttcserver
            Client machine: OMA-ENG-SBARR (OMA-ENG-SBARR.dtn.com); IP address:;
            Client PID: 3764;

            13026 0x80d06d0 3.72 Active Database 0x01312d00 X

            Awaiting locks

            PID Context TransID Resource ResourceID RMode HolderTransI
            D HMode Name
            2932 0x848c470 1.42880411 Database 0x01312d00 X 3.72

            2 outstanding transactions found


            I issue a commit in the worksheet, and the lock goes away allowing other open transactions to continue:
            ttXactAdmin -connStr dsn=COMM1
            2009-06-23 10:37:54.666
            TimesTen Release

            0 outstanding transactions found
            • 3. Re: SQL Developer holding locks/autocommit behavior
              This behaviour is because you have configured datastore (DS) level locking (LockLevel=1) either in your DSN definition (sys.odbc.ini) or in the ODBC connection string. Database level locking is a little used 'specialist' configuration. I strongly suggest that you change this setting to the default value (0, row level locking) and this 'problem' will disappear.


              • 4. Re: SQL Developer holding locks/autocommit behavior
                For performance reasons, we have database logging turned off on some of our datastores. Row level locking requires database logging, so I get this error when I try to override the server DSN lock level in my jdbc string in SQL Developer with LockLevel=0.

                oracle.javatools.db.DBException: java.sql.SQLException: [TimesTen][TimesTen ODBC Driver][TimesTen]TT0827: Bad options: With no logging or diskless logging, DurableCommits must be off; and with no logging DSLevelLocking must be on (LockLevel set to 1) -- file "db.c", lineno 9212, procedure "sbDbConnect"

                Just for completion purposes, here is my server DSN configuration:

                So it looks like I'm left with explicitly issuing a commit with those datastores. If I find a way to automate this within SQL Developer, I'll be sure to post it here.

                Thanks for your prompt responses!
                • 5. Re: SQL Developer holding locks/autocommit behavior
                  Yes, you'll have to do the commit manually. Since Logging=0 is a niche use case and has been deprecated in the latest release (11g) and will be removed completely in some future release I can't see this changing in the future, sorry.