none
blocking in ado.net RRS feed

  • Question

  • Will blocking occur if I am loading data into the table using one stored procedure , while UI Developer is testing the interface and queering the same table but with different SP?
    • Moved by edhickey Thursday, February 24, 2011 4:54 PM (From:.NET 3.0/3.5 Windows Workflow Foundation)
    Thursday, February 24, 2011 2:29 AM

Answers

  • If you mean "locking" as in "row locks" on SQL Server, then yes. As the clients complete for the data, those attempting to change the data ask for update locks on the 8K pages containing the rows being changed. This might be one page or span the entire table. It depends on the scope of the query. This lock is held until the change is completed. While these locks are in place, other clients are prevented from acccesing the page(s). The same is true for queries--they request "shared" locks which are not granted if changes are being made on the tables.

    Generally, these locks last but for an instant and the system does what it can to keep them to a minimum. However, there are situations (all too common) where applications fetch too many rows (thus preventing changes) or change too many rows at once (thus preventing SELECT operations). There are also situations where one client can start and update but block while waiting for a row that's locked by some other client (even itself). This "deadly embrace" situation can be automatically resolved but after the system waits for it to resolve on its own.

    See http://www.sqlteam.com/article/introduction-to-locking-in-sql-server fmi. 


    __________________________________________________________________
    William Vaughn
    Mentor, Consultant, Trainer, MVP
    http://betav.com
    http://betav.com/blog/billva
    http://www.hitchhikerguides.net

    “Hitchhiker’s Guide to Visual Studio and SQL Server (7th Edition)”

    Please click the Mark as Answer button if a post solves your problem!

    Thursday, February 24, 2011 11:08 PM
    Moderator

All replies

  • I'm going to assume you meant querying. There shouldn't be any blocking unless you are hitting some limit at the database level. Each of you will have a different connection to they database and queries will be processed concurrently.
    Paul ~~~~ Microsoft MVP (Visual Basic)
    Thursday, February 24, 2011 7:07 PM
  • If you refer to client side code which loads data into DataTable using stored procedure then yes, it will be blocked unless you call stored procedure asynchronously. By default, it is synchronous mode and it blocks thread from which if was executed. If you call it from the UI thread then it will be blocked.
    Val Mazur (MVP)

    http://www.xporttools.net

    Thursday, February 24, 2011 10:43 PM
    Moderator
  • If you mean "locking" as in "row locks" on SQL Server, then yes. As the clients complete for the data, those attempting to change the data ask for update locks on the 8K pages containing the rows being changed. This might be one page or span the entire table. It depends on the scope of the query. This lock is held until the change is completed. While these locks are in place, other clients are prevented from acccesing the page(s). The same is true for queries--they request "shared" locks which are not granted if changes are being made on the tables.

    Generally, these locks last but for an instant and the system does what it can to keep them to a minimum. However, there are situations (all too common) where applications fetch too many rows (thus preventing changes) or change too many rows at once (thus preventing SELECT operations). There are also situations where one client can start and update but block while waiting for a row that's locked by some other client (even itself). This "deadly embrace" situation can be automatically resolved but after the system waits for it to resolve on its own.

    See http://www.sqlteam.com/article/introduction-to-locking-in-sql-server fmi. 


    __________________________________________________________________
    William Vaughn
    Mentor, Consultant, Trainer, MVP
    http://betav.com
    http://betav.com/blog/billva
    http://www.hitchhikerguides.net

    “Hitchhiker’s Guide to Visual Studio and SQL Server (7th Edition)”

    Please click the Mark as Answer button if a post solves your problem!

    Thursday, February 24, 2011 11:08 PM
    Moderator
  • Hi Val8282,

     I am writing to check the status of the issue on your side.  Would you mind letting us know the result of the suggestions? 
     
    If you need further assistance, please feel free to let me know.   I will be more than happy to be of assistance.

    Have a nice day.


    Alan Chen[MSFT]
    MSDN Community Support | Feedback to us
    Get or Request Code Sample from Microsoft
    Please remember to mark the replies as answers if they help and unmark them if they provide no help.

    Tuesday, March 1, 2011 1:16 AM
    Moderator