Locking Perf Problem: Help Update/Insert WITH table_hints

  • Paul White (6/14/2009)


    Well that's a shame. I spent some time last night creating a solution for this which worked well. The final stage just involved two questions:

    1. Is the table that contains the most recent purchase essential to the application - or can I lose it and just update the main table in-place?

    2. Can each job insert multiple rows for the same product type ID in the same statement? This complicates the task a little if so, but it is still workable.

    My in-progress solution updates all the running totals and deltas in a fraction of a second, so I had high hopes for it.

    Oh well. :unsure:

    RE: 1. The running totals problem becomes a performance issue when dealing with the entire dataset all at once, even when indexed / clustered by ProductTypeID and PurchaseDatetime. This was my original design, and is the reason for limiting the pertinent real-time data to one of its smallest datasets (i.e. a table containing only the last purchases for each ProductTypeID, the CurrentPurchases table). Because of locking this "smallest dataset" can be broken down even further by job and clustered by each block in relation to the job that uses it. Or how Barry recommends, adding a JobNumber and clustering by that (which would honestly provide sort of a precalculation for the entire system on which job gets what data). However, it is only a viable solution if each page lock would be based on the JobNumber index after clustering the table by JobNumber. (Quite intuitive if so) Barry's idea is an improvement on automatically creating 50 CurrentPurchases tables to get around the locking. Also, I found something very interesting that does retries after a lock is encountered (look at Figure 3 Script 1): http://msdn.microsoft.com/en-us/magazine/cc164012.aspx

    RE: 2. Each job does not insert or update multiple rows with a repeat of the same ProductTypeID in each recurred insert/update... in some ways that's fortunate, in some ways, a limitation. Nonetheless, multiples of the same ProductTypeID will never be inserted by a job in the same update / insert.

    Example:

    Job#1 always gets data for ProductTypeIDs 1-200 as a block for ProducTypeIDs 1, 2, 3, ... 200, then bulk inserts/updates CurrentPurchases table for those ProductTypeIDs only.

    Job#2 always gets data for ProductTypeIDs 201-400 as a block for ProductTypeIDs 201, 202, 203, ... 300, then bulk inserts/updates CurrentPurchases table for those ProductTypeIDs only.

    ...

    I'm interested in how you got better performance. The actual execution of each job averages to 1 second for me. But the recurrences for each job execution averages 20 seconds now (should be almost exactly 10 seconds) due to locking and dropped recurrences on some 10 second intervals.

  • Example script attached. I hope it is fairly self-explanatory - I have run out of time again tonight.

    The real world really gets in the way sometimes 🙂

    Each 200-row insert (up to a table size of 400K - I got bored) took around 15ms on my machine. That was with three batch inserts running concurrently from separate connections. I saw no deadlocks.

    I have included some bits and bobs (a procedure) to make concurrent batches easier to set up, but ran out of time to create a proper script. If you feel you need more than the WAITFOR and procedure setup from separate SSMS tabs can give you, set up some Agent Jobs, or write a command script to fire up OSQL or SQLCMD.

    I hope I have broadly understood what you are trying to achieve.

    Cheers

    Paul

  • Paul, thank you very much. I worked all night (and am now going to my day job) on restructuring an instance of the production code to include Barry's idea... PAGLOCK updates, and a CurrentPurchases table with an added "JobNumber" column. I created a clustered index on JobNumber in the CurrentPurchases table. I prayed that when I executed the jobs, they would only access the rows with the Job's JobNumber in it, and when it would go to update the table bubbled up through the trigger, the updates would page by the index, and there would be no deadlocks. This is not the case, but it did bring my timing accuracy up to about 65% since some 10 second job recurrences are skipped due to deadlocks and lock waits.

    I tried the code from the Microsoft article in my previous E-mail, and it just added more UPDATE attempts to the same locking situation, thus amplifying the problem, so it isn't useful for a continually recurring situation like this.

    I have to head to work, but I will check your code this evening when I get back, and see what I can alter in mine to match. I'm okay with multiple TABS in SSMS (that's how I started out with concurrency in this system!).

    Thank you again,

    Aaron

  • Btw instead of setting allow page lock = off on the index, you could use a ROWLOCK table hint on the update statement.

    In all cases test thoroughly, because if you have some dml statements which needs pagelock on that table, and get a rowlock instead it could hurt performance.

  • You'll probably get some blocking with clustering + PAGLOCK because the jobs are still bound to share a page or tow, but the chance of deadlocking should be vastly reduced. ROWLOCK is a good idea, it has more overhead, but might eliminate the blocking/deadlocking chance entirely (not sure).

    [font="Times New Roman"]-- RBarryYoung[/font], [font="Times New Roman"] (302)375-0451[/font] blog: MovingSQL.com, Twitter: @RBarryYoung[font="Arial Black"]
    Proactive Performance Solutions, Inc.
    [/font]
    [font="Verdana"] "Performance is our middle name."[/font]

  • Barry,

    The script I posted takes the row-lock approach and sacrifices some measure of efficiency for concurrency. I may have missed the point with it, but we'll see what Aaron makes of it later I guess.

    Paul

  • Found a solution that allows multiple connections to be updating the same table, but of course, different rows without blocking occurring.

    The solutions is to include the following two states:

    1) In the trigger, a nested loop join must be specified from deleted/inserted table to the base table:

    FROM deleted AS Old

    INNER LOOP JOIN

    Revision2.PurchasesLatest Cur

    ON Old.ProductTypeID = Cur.ProductTypeID

    2) For the table, the unique clustered index must be defined on the join condition columns, which in this case, isthe ProductTypeID column.

    Other options tried that are not needed are:

    Disabling lock escalation on the table.

    Specifying hints for a specific index or the forceseek hint.

    Without the forcing the loop join, when the number of rows exceeds about 1% of the total rows in the table, a merge join is used, which cause as table intent exclusive lock, and the second connection will wait for the table lock to be released.

    Attached are various solutions tried and each solution uses a separate schema rather than diffferent object names.

    To test, in two SSMS windows run:

    Window 1:

    BEGIN TRAN

    exec Revision2.PurchasesLatest_Update_G1

    -- ROLLBACK

    Window 2:

    BEGIN TRAN

    exec Revision2.PurchasesLatest_Update_G2

    -- ROLLBACK

    SQL = Scarcely Qualifies as a Language

  • Carl,

    The script I posted earlier uses a similar approach. Try it out if you get a moment.

    BTW there is a dependency in your script on MASTER.dbo.Tally which I assume is the usual numbers table, but not everyone will have it. Also, the path for the CREATE DATABASE statement is specific to 2008 - but that's an easy fix for most people I guess. Finally, one of your examples uses FORCESEEK which is also 2008-specific.

    Because of the dependency on Tally, I haven't been able to run it yet, but may do so after work tonight.

    Cheers,

    Paul

  • Paul White (6/15/2009)


    Barry,

    The script I posted takes the row-lock approach and sacrifices some measure of efficiency for concurrency. I may have missed the point with it, but we'll see what Aaron makes of it later I guess.

    Paul

    Sorry Paul, I haven't read through all of the code in detail.

    [font="Times New Roman"]-- RBarryYoung[/font], [font="Times New Roman"] (302)375-0451[/font] blog: MovingSQL.com, Twitter: @RBarryYoung[font="Arial Black"]
    Proactive Performance Solutions, Inc.
    [/font]
    [font="Verdana"] "Performance is our middle name."[/font]

  • OH MY GOD! So simple, so elegant. So 99.25% on-time!!!!!!!!!!!!!!!!!! Every single job is meeting the 10 second interval within 750 milliseconds between each ProductTypeID pull which is only due to additional processing variance, not architecture. SQL Profiler demonstrates ZERO deadlocks or lock escalations with the INNER LOOP JOIN and ProductTypeID unique clustered index!!!!!!!!!!!!!

    THANK YOU GUYS!!!!!!!!!!!!!!!!!!!

  • @Aaron: that's great news!

    @Barry: no worries - I was just pointing it out in case you or anyone was particularly interested 😎

    Paul

  • Aaron & Paul White:

    I tried out Paul alternative solution by changing my posted trigger Revision2.PurchasesLatest_tau

    Carl's code

    FROMdeleted AS Old

    INNER LOOP JOIN

    Revision2.PurchasesLatest Cur -- WITH (FORCESEEK)

    ON Old.ProductTypeID = Cur.ProductTypeID

    ;

    Changed to

    FROMdeleted AS Old

    INNER JOIN

    Revision2.PurchasesLatest Cur

    ON Old.ProductTypeID = Cur.ProductTypeID

    OPTION (FORCE ORDER, LOOP JOIN , FAST 1 , MAXDOP 1)

    ;

    The differences are

    Removed the "LOOP" keyword ("INNER LOOP JOIN" to "INNER JOIN")

    Appended "OPTION (FORCE ORDER, LOOP JOIN , FAST 1 , MAXDOP 1) "

    Paul specified options "FAST 1" and "MAXDOP 1" but these options did not have any effect on the test cases I tried but a different schema or data might have an effect.

    The master.dbo.Tally table is the conventional "numbers" or "sequences" table and the SQL to create that table and populate is in Jeff Moden's articlehttp://www.sqlservercentral.com/articles/T-SQL/62867/

    SQL = Scarcely Qualifies as a Language

  • Oh, now I get it. LOOP join, yeah, with row locking that'll prevent any table escalations, and MAXDOP 1 makes a deadlock virtually impossible.

    Nice work guys.

    [font="Times New Roman"]-- RBarryYoung[/font], [font="Times New Roman"] (302)375-0451[/font] blog: MovingSQL.com, Twitter: @RBarryYoung[font="Arial Black"]
    Proactive Performance Solutions, Inc.
    [/font]
    [font="Verdana"] "Performance is our middle name."[/font]

  • The root cause is that for triggers, when the number of rows affected is significantly different that the prior execution of the trigger, SQL Server automatically creates a new execution plan. When the number of rows updated is less than about 2% of the table rows, then a nested loop is used but when it exceeds about 2%, a merge join is used. The merge join does an index scan and puts a table intent exclusive lock on the base table, causing any other action against the table to block.

    SQL = Scarcely Qualifies as a Language

  • Carl Federl (6/16/2009)


    When the number of rows updated is less than about 2% of the table rows, then a nested loop is used but when it exceeds about 2%, a merge join is used. The merge join does an index scan and puts a table intent exclusive lock on the base table, causing any other action against the table to block.

    That's pretty much it, yes. Though if every operation always took a table lock, in the same sequence, that would avoid deadlocking too - at the cost of serializing access to the resource (not ideal, I grant you).

    In fact, a hash join always has to fully scan both tables and will generally take a table lock from the outset. Depending on the data, a merge join may not read all rows from the outer table, if it reaches the end of the inner input before doing so. For this reason, merge may only require row or page locks instead of a table lock (on the outer table).

    The last thing I would say is that an intent exclusive lock is always taken on the containing page and the table even for a single row update - that's just how normal locking works. The intent lock is just there to allow the server to quickly determine if there are any exclusive locks at a lower level - an optimization for the lock manager. Other connections are free to read and update data, so long as different rows are involved:

    Books Online


    An intent exclusive (IX) lock is compatible with an IX lock mode because IX means the intention is to update only some of the rows rather than all of them. Other transactions that attempt to read or update some of the rows are also permitted as long as they are not the same rows being updated by other transactions.

    Paul

Viewing 15 posts - 31 through 44 (of 44 total)

You must be logged in to reply to this topic. Login to reply