Help: simultaneous calls of a SP containing BEGIN TRAN....COMMIT TRAN

Help: simultaneous calls of a SP containing BEGIN TRAN....COMMIT TRAN

Post by hb » Fri, 23 May 2003 20:55:31



Hi,

I have a stored procedure sp_A that contains BEGIN TRAN....COMMIT TRAN.

What I understand is that the tables inside the BEGIN TRAN....COMMIT TRAN
block will be locked completely during the execution of sp_A (my
understanding could be wrong).
That means no other process can write to those tables used in this block
until sp_A
finishes.

In such case, if there are multiple processes call sp_A during
simultaneously, what
will happen? Since sp_A can only run for one process at a time, will the
others calls
be put in a queue in SQL Server or get a error message?

Plus, will the following process lock a single row or the whole table?
   UPDATE Book
   SET bookName='SQL Server Programming'
   WHERE bookID=123
And, will it be rollback automatically if it fails?

Thank you

hb

 
 
 

Help: simultaneous calls of a SP containing BEGIN TRAN....COMMIT TRAN

Post by Tom Morea » Fri, 23 May 2003 21:05:03


The tables inside the BEGIN TRAN/COMMIT TRAN block are not necessarily locked completely.  It depends on how many rows you are affecting and if the table has a unique index (e.g. a primary key).  In the majority of cases, you will be locking only a small portion of the table.  The rest of the table can then be accessed by other processes.  If there is a conflict, then processes waiting for resource will queue up until the resources are available.

In your example, assuming the BookID is the primary key, it will block access only to that one row.  You will have to put a ROLLBACK into your code to handle failure of the UPDATE.

--
Tom

---------------------------------------------------------------
Thomas A. Moreau, BSc, PhD, MCSE, MCDBA
SQL Server MVP
Columnist, SQL Server Professional

www.pinnaclepublishing.com/sql

Hi,

I have a stored procedure sp_A that contains BEGIN TRAN....COMMIT TRAN.

What I understand is that the tables inside the BEGIN TRAN....COMMIT TRAN
block will be locked completely during the execution of sp_A (my
understanding could be wrong).
That means no other process can write to those tables used in this block
until sp_A
finishes.

In such case, if there are multiple processes call sp_A during
simultaneously, what
will happen? Since sp_A can only run for one process at a time, will the
others calls
be put in a queue in SQL Server or get a error message?

Plus, will the following process lock a single row or the whole table?
   UPDATE Book
   SET bookName='SQL Server Programming'
   WHERE bookID=123
And, will it be rollback automatically if it fails?

Thank you

hb

 
 
 

Help: simultaneous calls of a SP containing BEGIN TRAN....COMMIT TRAN

Post by hb » Sat, 24 May 2003 14:48:38


Tom,

Your information is very helpful. Thank you very much!

HB

  The tables inside the BEGIN TRAN/COMMIT TRAN block are not necessarily locked completely.  It depends on how many rows you are affecting and if the table has a unique index (e.g. a primary key).  In the majority of cases, you will be locking only a small portion of the table.  The rest of the table can then be accessed by other processes.  If there is a conflict, then processes waiting for resource will queue up until the resources are available.

  In your example, assuming the BookID is the primary key, it will block access only to that one row.  You will have to put a ROLLBACK into your code to handle failure of the UPDATE.

  --
  Tom

  ---------------------------------------------------------------
  Thomas A. Moreau, BSc, PhD, MCSE, MCDBA
  SQL Server MVP
  Columnist, SQL Server Professional

  www.pinnaclepublishing.com/sql


  Hi,

  I have a stored procedure sp_A that contains BEGIN TRAN....COMMIT TRAN.

  What I understand is that the tables inside the BEGIN TRAN....COMMIT TRAN
  block will be locked completely during the execution of sp_A (my
  understanding could be wrong).
  That means no other process can write to those tables used in this block
  until sp_A
  finishes.

  In such case, if there are multiple processes call sp_A during
  simultaneously, what
  will happen? Since sp_A can only run for one process at a time, will the
  others calls
  be put in a queue in SQL Server or get a error message?

  Plus, will the following process lock a single row or the whole table?
     UPDATE Book
     SET bookName='SQL Server Programming'
     WHERE bookID=123
  And, will it be rollback automatically if it fails?

  Thank you

  hb

 
 
 

1. Help: multiple calls and BEGIN TRAN....COMMIT TRAN

Hi,

I have a stored procedure sp_A that contains BEGIN TRAN....COMMIT TRAN.

What I understand is that the tables inside the BEGIN TRAN....COMMIT TRAN
block will be locked completely during the execution of sp_A (my
understanding could be wrong).
That means no other process can write to those tables used in this block
until sp_A
finishes.

In such case, if there are multiple processes call sp_A during
simultaneously, what
will happen? Since sp_A can only run for one process at a time, will the
others calls
be put in a queue in SQL Server or get a error message?

Plus, will the following process lock a single row or the whole table?
   UPDATE Book
   SET bookName='SQL Server Programming'
   WHERE bookID=123
And, will it be rollback automatically if it fails?

Thank you

hb

2. Generic Looping

3. Begin Tran-Commit Tran

4. Oracle HR Payroll module Project Manager needed

5. BEGIN TRAN and COMMIT TRAN from a three tier system

6. How to delete a record with the key which contains "%20"(space) in the

7. begin tran / commit tran

8. OCP value - Serious.

9. begin tran and commit tran

10. Begin, Commit tran - tran count not matched

11. begin/end begin tran/commit in while

12. Do I need BEGIN/COMMIT TRAN here?

13. Programmatic BEGIN TRAN, ROLLBACK and COMMIT