2

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)?

marc_s
  • 732,580
  • 175
  • 1,330
  • 1,459
Boris B.
  • 4,933
  • 1
  • 28
  • 59

1 Answers1

2

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.

Mark S. Rasmussen
  • 34,696
  • 4
  • 39
  • 58
  • RE: "Your sp will automatically roll back if it ungracefully exits.". You would need [`XACT_ABORT ON`](http://stackoverflow.com/questions/1150032/what-is-the-benefit-of-using-set-xact-abort-on-in-a-stored-procedure) to ensure this. – Martin Smith May 18 '11 at 10:10
  • Good point. Depending on the actual error, it may not halt, though it will only commit if you explicitly call COMMIT. – Mark S. Rasmussen May 18 '11 at 10:12
  • 3
    The last part is a bit off, trun behaves differently from delete. It obtains an X lock on a table as would delete eventually escalate to, truncs and then retains a Sch-M lock on the object (having "rebuilt" its PK from ground up), effectively preventing any (including read uncommited tran) from accessing it until commited. – mmix May 18 '11 at 10:43
  • I did not know that TRUNCATE takes a schema lock, interesting! – Mark S. Rasmussen May 18 '11 at 10:45
  • As a rule of thumb, any operation which rebuilds/recreates a clustered index online retains a Sch-M until commit. Although it is kind of difficult to see it in TRUNC because its usually referred to as mass delete, rather then kill&create. – mmix May 18 '11 at 10:51