Results 1 to 6 of 6
  1. #1
    Join Date
    Sep 2003
    Location
    Wellington
    Posts
    4

    Unanswered: Blocks with row level locking?!?

    We have a table that users are getting blocked on.
    The table is insert/delete only with no updates.
    Historical data is deleted during a purge run. It is during this purge that the users get blocked. However they are not trying to access old data, as the users are inserting new records only, no updates or deletes.
    We delete 500 rows at a time, up to about 1 million rows during a nightly job.
    However as this system is 24/7 there will always be users on.

    My question is why would users be blocked when they are inserting only, and the purge is deleting old data that is not being used at all.

    Thanks

  2. #2
    Join Date
    Sep 2004
    Location
    Stockholm, Sweden
    Posts
    28
    not sure why, but it is possible that the locking granularity swiches to lock whole pages or even the whole table (Sybase uses multiple-granularity locking).

    Is your table a heap or sorted (clustered) file??

    Try running sysmon, it should give you a better view of whats happening.

    /Mats

  3. #3
    Join Date
    Nov 2002
    Posts
    833
    by default sybase locks blocks. For your table in question you should switch to row-level locking
    within sybase central you will find a button to change to row level locking

  4. #4
    Join Date
    Sep 2003
    Location
    Wellington
    Posts
    4

    Row level locking

    Hi thanks for the replies.
    Firstly it is row level locking already and it is a sorted(clustered) table.

    The table has over 100 million rows in it with about 1 million inserts per day and up to 1 million deletes per day.

    We have run the delete command manually and it uses indexes etc and is fine. However when we run it as a stored procedure it escalates to an Ex_table lock, even though we are only deleting in txns of 500 rows.

    The stored procedure is a replicated stored procedure, but i'm not aware that this would have any effect like this?

    sp_lock shows only EX_Row and EX_Page locks, however hold_locks shows the EX_Table as stated above.

    Thanks

  5. #5
    Join Date
    Sep 2004
    Location
    Stockholm, Sweden
    Posts
    28
    Hi again,
    Ok so now you know that ex_table lock is the problem.

    You really should check your configuration values for the parameters "row lock promotion HWM" and "row lock promotion LWM". These decides when to switch to table locking. The default is 200 so if you delete 500 rows at one you really should increase these parameters.

    See Performance and Tuning guide, section "setting lock promotion thresholds" for more info...

    /Mats

  6. #6
    Join Date
    Sep 2003
    Location
    Wellington
    Posts
    4

    Thanks

    Ok that's great i will read up on that and test those values in our test region.
    Thanks again for your help.
    Brett

Posting Permissions

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