Results 1 to 10 of 10
  1. #1
    Join Date
    Jul 2008
    Posts
    3

    Unanswered: Are stored procedures atomic?

    Hello!

    If I have a multiple statements stored procedure do I assume that SQL Server will execute the whole procedure atomically? For Example if I have the following scenario:

    Stored Procedure 1 (SP1:
    ------------------
    sql statement .... --(st11)
    sql statement ... --(st12)

    Stored Proceudre 2 (SP2):
    --------------------
    sql statement .... --(st21)
    sql statement ... --(st22)

    So when I execute both stored procedures at random (simultanously). If SP1 happens to execute first and st11 is executed, does that mean st12 will run before st21 and st22? or do I need any type of locking to enforce that?

    I searched BOL but couldn't find any help on this subject. I also read (online) about the key words 'begin atomic' in DB2 is there anything like that in SQL Server.

    Your help is appreciated.

  2. #2
    Join Date
    Feb 2004
    Location
    In front of the computer
    Posts
    15,579
    Provided Answers: 54
    Individual spids within SQL Server are single-threaded. That means that when you execute any SQL script or stored procedure, the statements within that script or procedure are always executed in order. If you start executing sp1, you can assume that st11 will execute, then st12 (assuming you don't have any conditional logic that would change the flow of control). If you execute sp1 and sp2 at the same time, you can assume that st11 will still execute before st12, but there is no explicit relationship at all between st11 and st21.

    -PatP

  3. #3
    Join Date
    Jul 2008
    Posts
    3
    Thank you very much Pat! That was very helpful.

  4. #4
    Join Date
    Jul 2003
    Location
    San Antonio, TX
    Posts
    3,662
    Technically, it's not the nature of the spids that are involved in processing of the statements, - it's the nature of how the statements themselves are processed. That's the question! Spids (contrary to popular believe) may or may not be single-threaded, but the question was about whether statements from different procs can be processed "randomly" or asynchronously.

    The answer is simple, - each procedure will be executed synchronously in terms of its contents. It means that the optimizer delivers the plans for each statement in a sequential order as the staement appears in the code of the procedure. But, if a staement is determined to be processed in parallel, - it will, but only when its turn comes up in a batch, after executing the one above it.

    So, technically, "individual spids within SQL Server are" NOT single-threaded, or at least they are not guarateed to be single-threaded. Their execution is guaranteed to be in a sequential order of their appearance in the source code.
    "The data in a record depends on the Key to the record, the Whole Key, and
    nothing but the Key, so help me Codd."

  5. #5
    Join Date
    Feb 2004
    Location
    In front of the computer
    Posts
    15,579
    Provided Answers: 54
    I disagree with rdjabarov, but I'm pretty sure that it is because he's confused two parts of the SQL engine that are closely related. The spid itself (system spids are an exeception, but for this discussion a spid is the part of SQL Server that translates and executes Transact-SQL for users) is inherantly single threaded. A user spid will only generate and submit the plan for one SQL statement at a time to the underlying engine for execution.

    Once the plan is delivered to the engine, many factors can come into play to determine if it will be executed in parallel (MAXDOP, available resources, plan structure, etc). This execution stage can often be executed in parallel and is reported (via system functions, sp_who, etc) as though the spid were parallel too.

    I stand by my original statement, a user spid is single threaded although the SQL engine that the spid uses to execute statements can be multi-threaded.

    -PatP

  6. #6
    Join Date
    Jul 2003
    Location
    San Antonio, TX
    Posts
    3,662
    Not sure what two parts you're referring to, that I am confused about. Can you clear it out for me?

    The spid, or a session_id, is not a thread, and it cannot be measured in terms of threads. This goes to both user and system spids. User and system threads are a totally different thing, and I see how Pat could get those two mixed up. User-mode thread operates on memory, while system-(or kernel-)mode thread operates on all other resources, including memory, but "from a different angle" if you will. It is not easy to determine how many kernel-mode threads will be involved to execute a statement even if it is not processed in parallel.

    The confusion goes even further, and I cannot make any sense out of the last statement, other than to say for clarity that SQL Server is a single-process multi-threaded (as opposed to Oracle which is multi-process multi-threaded) environment.

    To recap, statements within a procedure are executed sequentially, one after another. I think Pat introduced a distraction by talking about threads. Query Optimizer dictates the order of processing, which is sequential. So to answer the question about "atomic" nature from the original post, - no, there is no guarantee that st21 will be executed after st12, unless you introduce a common resource that you can use as a basis for a locking mechanism. A prime example of it is app_lock. In this case, you can either completely prevent execution of another proc, or delay its execution until the first proc finishes. Then you can accomplish atomicity you're talking about.
    "The data in a record depends on the Key to the record, the Whole Key, and
    nothing but the Key, so help me Codd."

  7. #7
    Join Date
    Feb 2004
    Location
    In front of the computer
    Posts
    15,579
    Provided Answers: 54
    The original poster has what they want, so I'm fine with that.

    I'm in a foul mood so I refuse to be baited into anything with anyone today, but thanks for trying!

    -PatP

  8. #8
    Join Date
    Jan 2003
    Location
    Nottinghamshire, UK
    Posts
    364
    Just to be clear for Fabienne's 2nd post here

    Fabienne are you talking about SP1 & SP2 being executed randomly/simultaneously/at the same time
    **on a multi user system by different users?**

    if so then are you just doing selects or are you doing inserts/updates/deletes on shared tables?
    "Everything should be made as simple as possible, but not simpler." - Albert Einstein
    "Everything should be made as complex as possible, so I look Cleverer." - Application Developer

  9. #9
    Join Date
    Jul 2008
    Posts
    3
    Quote Originally Posted by GWilliy
    Just to be clear for Fabienne's 2nd post here

    Fabienne are you talking about SP1 & SP2 being executed randomly/simultaneously/at the same time
    **on a multi user system by different users?**
    Yes.

    Quote Originally Posted by GWilliy
    if so then are you just doing selects or are you doing inserts/updates/deletes on shared tables?
    inserts and updates.

    Quote Originally Posted by Pat
    The original poster has what they want, so I'm fine with that.
    Yes, absolutely. Although rdjabarov's posts are very informative they are a little over my head as an sql newbie.

    Thank you all!

  10. #10
    Join Date
    Jan 2003
    Location
    Nottinghamshire, UK
    Posts
    364
    OK Fabienne Thanks

    Not knowing your particular challenge, I was just considering the following concurrency situation.

    Timeline of events:

    UserA Executes SP1
    UserA sql statement .... --(st11)
    UserA sql statement .... --(st12)
    UserB Executes SP1
    UserB sql statement .... --(st11)
    UserA Executes SP2
    UserA sql statement .... --(st21)
    etc..........................

    If SP1 happens to execute first and st11 is executed, does that mean st12 will run before st21 and st22?
    If you meant to say "could the DB Execute" instead of
    So when I execute both stored procedures at random (simultanously).
    Then the Answer would be NO!
    As shown above It does not mean that after executing st11, st12 will always run before st21 and st22?"

    Classic!!

    You probably/may have to prevent the timeline scenario from happening, but could'nt say for sure unless you posted the Sql st's

    especially any TRANS & NOLOCK Usage etc.

    Read BOL about isolation levels

    Methinks Pats Original answer was correct given your original post !!!!

    GW
    "Everything should be made as simple as possible, but not simpler." - Albert Einstein
    "Everything should be made as complex as possible, so I look Cleverer." - Application Developer

Posting Permissions

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