Concurrency Question

  • Paul, I think I found it, and shame on me.

    Both sessions are supposed to be trying to add 'USER X'. The code I posted has USER "Y" for session 2. Obviously there is no danger of a primary key violation unless both sessions are trying to insert the same thing. I will go back and edit the original code now. Apologies.

    __________________________________________________

    Against stupidity the gods themselves contend in vain. -- Friedrich Schiller
    Stop, children, what's that sound? Everybody look what's going down. -- Stephen Stills

  • "Let (s)he who is without pork chop, throw the first pork chop"

    😀

    Now, that's FUNNY!


    * Noel

  • Bob,

    No worries at all - don't give it a second thought.

    [my pointless code deleted at this point, lol]

    @noeld: thanks! It amused me.

    Paul

  • Okay, both the errant UserID and the "bugette" have been repaired.

    Would somebody please retry it?

    __________________________________________________

    Against stupidity the gods themselves contend in vain. -- Friedrich Schiller
    Stop, children, what's that sound? Everybody look what's going down. -- Stephen Stills

  • Bob,

    It works fine now. The primary key is safe! 😀

    Paul

  • Whew... that was the whole point in the first place. 😛 Thanks, Paul !!

    Jamie, please take all these digressions in stride. One of the disconcerting, yet great, things about SSC is that discussions on a topic may carry on long after the original question has been answered. Over time, these discussions may turn out to be wonderful seminars. But it's your thread so don't let us "hijack" it, if you still haven't gotten a satisfactory answer to your question.

    __________________________________________________

    Against stupidity the gods themselves contend in vain. -- Friedrich Schiller
    Stop, children, what's that sound? Everybody look what's going down. -- Stephen Stills

  • Jamie Julius (5/7/2009)


    It seems to me that the proposed SQL would be considered an implicit transaction. It would be run under the default isolation mode of "Read committed". I don't think this is enough to prevent two concurrent sessions from both inserting the same UserID. If they each do the SELECT part at the same time, they will both conclude that the UserID is not in the table. I would be happy to be proved wrong about this.

    Jamie,

    You are correct that two concurrent transaction could both conclude, from a SELECT at READ COMMITTED that no row exists, and both would attempt an INSERT.

    Absent a unique constraint, index, primary key, or similar...you could end up with two INSERTS.

    The window of mis-opportunity is usually very small, however.

    In the example we are all discussing (trying to detect the existence of a single row) the possibility is vanishingly small unless the table is very large and there are a huge number of concurrent transactions, and the plan involves an allocation-order scan.

    Adding a (non-unique) index to the searched column reduces the chances even further. Adding a unique index (as you probably should for anything where it is important to guarantee uniqueness!) removes the problem entirely (of course).

    The unique index is also the only thing (aside from SERIALIZABLE) that would prevent duplicates when READ_COMMITTED_SNAPSHOT is enabled.

    It is possible to over-analyze this sort of thing. While SERIALIZABLE would technically work, it achieved this by running things serially (hence the name) and concurrency of changes disappears completely within its scope. Deadlocks will also feature highly in your task list if you try to run everything at that level.

    Interesting technical debates aside, the advice you have been offered is sound (as one would expect from the people involved). Step back from the technicalities a bit, and consider what it is you need to guarantee.

    If it is that a user-id and time-slot combination should be unique, create the appropriate constraint.

    If it is just the need to check if a row exists to decide whether to do an UPDATE or INSERT:

    1. Create the constraint to guarantee uniqueness.

    2. Try the UPDATE (this will be more common than the INSERT anyway)

    3. Check @@rowcount (or OUTPUT the primary key to a table variable as part of the UPDATE - see Books Online for syntax)

    3. If no rows were affected, do the INSERT.

    4. Catch any error thrown by the INSERT for the rare case when someone snuck in ahead of you, and do the UPDATE instead (step 2).

    This approach maximizes concurrency, while minimizing locking and error-handling overhead.

    Paul

  • Paul White (5/8/2009)


    ...

    You are correct that two concurrent transaction could both conclude, from a SELECT at READ COMMITTED that no row exists, and both would attempt an INSERT.

    Absent a unique constraint, index, primary key, or similar...you could end up with two INSERTS.

    ...

    Adding a unique index (as you probably should for anything where it is important to guarantee uniqueness!) removes the problem entirely (of course)...

    There's no unique index/constraint on UserID? Ah, now that's an important fact I should have picked up on earlier. Now this whole discussion makes more sense. I suppose someone has already suggested an Identity attribute on this column?

    [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]

  • Hey Barry,

    In my example, the userID column was the primary key. I didn't stress this because I thought the original fear was the possibility of insertion of two identical values. I never thought of NOT having a unique index on a column where this was a concern. But that does explain a lot about the focus on using SELECTS to make the INSERT conditional.

    Paul, can we agree that if a unique index exists over the column(s) to be inserted, that SQL will return an error for any subsequent "duplicate" inserts regardless of the transaction isolation level? Therefore, Jamie doesn't have to be concerned about anything other than creating such an index (or adding a constraint) and then providing logic to catch the error.

    Bob

    __________________________________________________

    Against stupidity the gods themselves contend in vain. -- Friedrich Schiller
    Stop, children, what's that sound? Everybody look what's going down. -- Stephen Stills

  • Hi

    Nothing technical to add from my side. I just have to say thank you all for teaching me about many things I didn't know before!

    Taking my head of to all of you.

    BTW1: What do you people like so much on throwing with meat??? :w00t:

    BTW2: Who writes an article about this? :laugh:

    Greets

    Flo

  • Blame Jeff Moden for the proliferation of pork chop projectiles. 😛

    I much prefer slapping people around with a large trout.

    It's an texture thing.

    I also like the sound better.

    Presumably, Barry telekinetically strangles people using The Dark Side of The Force.

    __________________________________________________

    Against stupidity the gods themselves contend in vain. -- Friedrich Schiller
    Stop, children, what's that sound? Everybody look what's going down. -- Stephen Stills

  • Paul White (5/8/2009)


    Jamie Julius (5/7/2009)


    It seems to me that the proposed SQL would be considered an implicit transaction. It would be run under the default isolation mode of "Read committed". I don't think this is enough to prevent two concurrent sessions from both inserting the same UserID. If they each do the SELECT part at the same time, they will both conclude that the UserID is not in the table. I would be happy to be proved wrong about this.

    Jamie,

    You are correct that two concurrent transaction could both conclude, from a SELECT at READ COMMITTED that no row exists, and both would attempt an INSERT.

    Absent a unique constraint, index, primary key, or similar...you could end up with two INSERTS.

    The window of mis-opportunity is usually very small, however.

    . . .

    Paul

    Thank you, Paul.

    Basically, this is all I wanted confirmed in my pursuit to understand what SQL can guarantee and what it cannot. I appreciate all the practical advise provided by the many contributors to this post. I apologize if I offended anyone by persisting with my question.

    Jamie

  • Jamie Julius (5/8/2009)


    I apologize if I offended anyone by persisting with my question.

    Hi Jamie

    Completely nothing to apologize from my side.

    Greets

    Flo

  • Bob Hovious (5/8/2009)


    Blame Jeff Moden for the proliferation of pork chop projectiles. 😛

    I much prefer slapping people around with a large trout.

    It's an texture thing.

    I also like the sound better.

    Presumably, Barry telekinetically strangles people using The Dark Side of The Force.

    I also saw first time in a post of Jeff. But since now I never thought there is a real story about the pork chops (just googled for fun)

    Here we usually say "throw eggs..." 😀

  • No need to apologize at all, Jamie. I never saw anything in any of your posts that could be considered rude, and if you don't feel your question has been answered, or correctly understood, you should persist.

    I still recommend the book.

    Flo, at one point there was actually a discussion about different types of hypothetical pork chop launchers. If memory serves, the possibilities included trebuchets, slingshots, and homemade cannon. 🙂

    Hope everyone has a great weekend!

    __________________________________________________

    Against stupidity the gods themselves contend in vain. -- Friedrich Schiller
    Stop, children, what's that sound? Everybody look what's going down. -- Stephen Stills

Viewing 15 posts - 61 through 75 (of 92 total)

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