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
by