Results 1 to 6 of 6
  1. #1
    Join Date
    Jun 2009
    Posts
    5

    Unanswered: locking setting for highest concurrency for given scenario

    HI,
    I am having one table which can have huge data. Every 5 mins there is a process which runs to update / insert records into it. Percentage of successful updates on average will be 20 % and 80 % there will be inserts.
    Total number of records that may be updated or inserted at one time range from 1000 to 4000.
    I have different gui views in different application dependent on same table.

    I am using all default setting for Sybase AES database.
    What is happening now is my process taking very long to perform updates/insert. And during this time if user wants to refresh its gui view it just hangs for a while as there update in progress.

    What kind of locking i can use to achieve this type of concurrency ?
    row level locks or some different kind of isolation level etc ?
    any suggestion will be helpful
    Thanks in advance

  2. #2
    Join Date
    Jun 2007
    Location
    London
    Posts
    2,527
    Some more questions:
    • Why are you updating 4k records every 5mins?
    • What do you mean by Percentage of successful updates on average will be 20 %?
    • How many records in the table?
    • Roughly how long would the batch take if there were no users?
    • Presume the batch queries are using the indexes
    • Presume the user gui's are using the indexes


    Initial thoughts:
    • Could you run smaller batches more frequently ie 200 updates every 30 secs
    • Could you use dirty reads (isolation level 0)
    • Could you lock all the records you're about to update at the start of the transaction ie put keys for all updates into a temporary table, begin tran, update main table where key in this temp table, then proceed as before.
    Last edited by mike_bike_kite; 06-10-09 at 15:59.

  3. #3
    Join Date
    Jun 2009
    Posts
    5
    Thanks mike for your suggestions.

    Hope this clarifies the question.

    We get updates of cumulative intra day system orders from back end which needs to be updated into our database which is used mainly for user viewing. These updates comes in file.
    e.g. We get say 100 records at starts of the day. Then in next 5 mins batch first 100 + new say 100. This continues till end of the day. To start with looks less number of records but as day progresses we may end up in processing 4k records. Every time when we process file we have to find if record present in db or its new based in primary key field. On next day start we clear the table and start with same processing again.
    So I think i was wrong in saying % of successful updates.
    % of existing records will be 90 and new records will be 10 while processing file. And for existing records we are doing update of all columns as we dont know what might have changed (There are some 40 columns in one record).
    In that sense % of successful updates are 90.

    When we tested processing of such 4k records using hibernate It took 4 mins.
    And at this time all select queries were frozen.

    Currently indexes used are based on primary key field for all comparisons.

    We have Sybase Adaptive server Enterprise 12.5.3

  4. #4
    Join Date
    Jun 2007
    Location
    London
    Posts
    2,527
    I haven't had my 2nd coffee yet so take this with a pinch of salt. I'd create a copy of the main table - lets call it a transaction table. Ensure that the primary keys are the same on both tables. It might be worth giving us the table definition and indexes just to make sure things are all correct.

    My batch would run every 5 minutes and do the following:
    • Delete all transaction entries where these records match what's in the main table (you'll need to match every field). This will get rid of 90% of records and not create any locking on the main table.
    • Next update the main table where just the key fields match the records in the transaction table. Then delete these records from the transaction table.
    • Next insert the records that are left into the main table. Then delete these records. The transaction table should be empty now.
    • You could log:
      • what time it ran
      • how long it took
      • show how many (or what percent) of records are dealt with at each stage

    • Load the transaction table with data from the next file.
    Any failure at any stage should be logged and the batch restarted after a minutes delay. None of the above stages needs to be done in a transaction as I believe each stage is atomic. I'm pretty sure the above would be a far faster way of loading the data.

    The other option is just to get the clients to only send changes since their last transfer. You could allow some overlap (say 2 hours) if you're worried about missing updates etc.

    Mike

    EDIT made a few small changes
    Last edited by mike_bike_kite; 06-11-09 at 08:21.

  5. #5
    Join Date
    Jun 2009
    Posts
    5
    Excellent approach mike. Loads of thanks.
    Took 10 mins to understand it completely but it's worth in the end.
    I liked the idea of no transaction.

    Need some clarification though about that other option you mentioned at the end, just to get idea about some alternative.

  6. #6
    Join Date
    Jun 2007
    Location
    London
    Posts
    2,527
    Quote Originally Posted by mithunruikar
    Need some clarification though about that other option you mentioned at the end, just to get idea about some alternative.
    At the moment you get updates of cumulative intra day orders that have happened throughout the day. If you changed this so they only sent orders that have happened since the last time they sent a file then the files would be smaller because you would only get a record once rather than repeatedly throughout the day.

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •