2 Replies Latest reply: Sep 28, 2012 3:09 AM by 964820 RSS

    Return first n rows with skip locked.

    964820
      Timesten is used from Java using client-server access. We have to replicate rows from TimesTen to another database.

      We have parallel job processing rows. While processing, rows are locked:

      SELECT FIRST N * FROM TABLE_X FOR UPDATE

      The problem is that the first job selects and locks first n rows and other job have to wait until locks are released. Is it possible to skip locked rows, so other jobs would select another set of rows.

      With Oracle this can be accomplished using SKIP LOCKED clause. With SQL Server using WHERE Locked IS NULL clause.

      Is possible to have this kind of behavior in TimesTen?

      Is there some other alternative for replicating rows? XLA API seems to be for this, but it seems a little bit complicated and we have to use direct-access, which is not suitable for our setup.

      Thanks
        • 1. Re: Return first n rows with skip locked.
          ChrisJenkins
          Unfortunately, TimesTen does not have any option to skip locked rows. I'm guessing that the 'other database' isn't Oracle? If it is you could use an AWT cache group. XLA (or JMS/XLA for Java) is really the best option for 'replicating' in terms of performance etc. It is true that to use it the component that does the 'replication' would have to run on the machine where TimesTen is running but the rest of the app would not have to do so. Mind you direct mode does give much better performance :-)

          If you really need to do this through SQL then you'll have to figure out a way to get the different jobs to look at different sets of rows without locking them. Exactly how you might do this of course depends a lot on the application and exactly what it is trying to do. One approach would be to have a column in the table to identify which 'replicator' is currently processing the row. You could use NULL to mean it is not being processed and some id value (could just be a number) to indicate that replicator 1 (or 2 or 3 or ...) is processing it. Replicator 'n' would 'reserve' a set of rows by updating he column to 'n' and committing. Then it would do its work and afterwards update the column again to some other value that means 'replication done'. This may work okay or may not depending on what else the application needs to do concurrently with those rows etc.

          Chris
          • 2. Re: Return first n rows with skip locked.
            964820
            This is exactly the knowledge we have arrived to and we are going with the similar solution You have offered (dividing rows to jobs), because this is simplest for us to implement.

            Thanks for You response.