CODEWITHSUNDEEP
sql-servert-sql
cost
cost

Reputation: 4480

Why is my SQL transaction not executing a delete at the end?

I've got a simple SQL command that is supposed to read all of the records in from a table and then delete them all. Because there's a chance someone else could be writing to this table at the exact moment, I want to lock the table so that I'm sure that everything I delete is also everything I read.

BEGIN TRAN T1; 
SELECT LotID FROM fsScannerIOInvalidCachedLots WITH (TABLOCK, HOLDLOCK); 
DELETE FROM fsInvalidCachedLots; 
COMMIT TRAN T1;

The really strange thing is, this USED to work. It worked for a while through testing, but now I guess something has changed because it's reading everything in, but it's not deleting any of the records. Consequently, SQL Server is spinning up high CPU usage because when this runs the next time it takes significantly longer to execute, which I assume has something to do with the lock.

Any idea what could be going on here? I've tried both TABLOCK and TABLOCKX

Update: Oh yea, something I forgot to mention, I can't query that table until after the next read the program does. What I mean is, after that statement is executed in code (and the command and connection are disposed of) if I try to query that table from within Management Studio it just hangs, which I assume means it's still locked. But then if I step through the calling program until I hit the next database connection, the moment after the first read the table is no longer locked.

Upvotes: 0

Views: 519

Answers (1)

Paul Williams
Paul Williams

Reputation: 17030

Your SELECT retrieves data from a table named fsScannerIOInvalidCachedLots, but the delete is from a different table named fsInvalidCachedLots.

If you run this query in set xact_abort off, the transaction will not be aborted by the error from the invalid table name. In fact, select @@trancount will show you that there is an active transaction, and select xact_state() will return 1 meaning that it is active and no error has occurred.

On the other hand, with set xact_abort on, the transaction is aborted. select @@trancount will return 0, and select xact_state() will return 0 (no active transaction).

See @@trancount and xact_state() on MSDN for more information about them.

Upvotes: 1

Related Questions