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

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

Post by hb » Thu, 22 May 2003 20:17:16



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: multiple calls and BEGIN TRAN....COMMIT TRAN

Post by Arvind Shyamsundar_MSF » Fri, 23 May 2003 10:11:53


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


 
 
 

1. Help: simultaneous calls of a SP containing 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. JDBC Driver for Oracle8

3. begin tran / commit tran

4. Error analyzing query (62)

5. Begin Tran-Commit Tran

6. Parse quotes

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

8. begin tran and commit tran

9. Begin, Commit tran - tran count not matched

10. begin/end begin tran/commit in while

11. Programmatic BEGIN TRAN, ROLLBACK and COMMIT

12. Begin Tran / Commit in ASP?