SQL Server 2005 and above - Atomicity of a stored procedure that contains TRUNCATE TABLE

I have a stored procedure that looks something like this:

TRUNCATE TABLE TableA;
INSERT INTO 
    TableA 
SELECT 
    a,b,c 
FROM 
    TableB;
UPDATE TableA SET a = ...;

TableA has no FK references so I can use TRUNCATE freely, and I use it because TableA has an autoinc column that I'd like to reset.

开发者_开发技巧

I need to ensure that at no point in time another QR or SP or UDF running in parallel will see TableA as empty (and TableB is never empty). I know I can use transactions, but I don't know if a simple BEGIN TRAN would do.

Do I need to set some special transaction level, or some locks or something? And if I use BEGIN TRAN and some error occurs inside the transaction, will the transaction automatically be dropped when my SP ungracefully exits or will the transaction stay and block further reads for other queries (this is something that bothers me about transactions in SPs in general)?

---

Assuming default READ COMMITTED transaction levels, both for your sp as well as readers, a simple BEGIN TRAN will do what you need. Readers will be blocked until your sp commits or rolls back. Your sp will automatically roll back if it ungracefully exits.

Note that you cannot protect yourself against other transactions using the READ UNCOMMITTED isolation level.