Hi HB,
To answer your questions:
Q: 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).
A: Your understanding is partially correct. Table level locks will be
acquired under some circumstances by SQL Server, depending on multiple
factors like the kind of query, the indexes on the table, the amound of data
on the table, the transaction isolation level etc. In the optimal case, only
the rows affected by the queries within the transaction will be locked.
Q: In such case, if there are multiple processes call sp_A during
simultaneously, what will happen?
A: This is processes contending for shared resources, in this case a table
or row level lock. Normally they will block until they either acquire the
resource, or the user cancels the query or the client disconnects. SQL
Server handles this for you. You can adjust the lock timeout setting (SET
LOCK_TIMEOUT) to play with this (not recommended however.)
Look in SQL Server Books Online and on MSDN
(http://support.microsoft.com/?id=224453 is one such link.) for more details
on the general concepts. Feel free to re-post for any specific issues.
Thanks,
Arvind.
--
*-*-*-*-*-*-*-*-*-*-*-*-*-*-*-*-*-*-*-*-*-*-*-*-*-*-*-*-*-*-*-*-*-*-*-*-*-*-
*-*-*-*-*-*-*-*-*-*-*-*-*-*-*-*-*-*-*-*-*-*-*-*-*-*-
This posting is provided "AS IS" with no warranties, and confers no rights.
*-*-*-*-*-*-*-*-*-*-*-*-*-*-*-*-*-*-*-*-*-*-*-*-*-*-*-*-*-*-*-*-*-*-*-*-*-*-
*-*-*-*-*-*-*-*-*-*-*-*-*-*-*-*-*-*-*-*-*-*-*-*-*-*-
Quote:> 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