I've got two identical tables, Table A and Table B.  Users are
constantly entering data into Table A.  I want to lock the data entry,
move all the data cleanly from A to B, delete everything from table A,
then unlock the table again.

Is below the best way to do this, using the HOLDLOCK and TABLOCKX table
hints on the from?  Should I separate out the insert & delete into two
different transactions?

BEGIN TRAN
    INSERT INTO TableB(col1, col2, col3)
    SELECT col1, col2, col3 FROM TableA WITH (HOLDLOCK, TABLOCKX)

    DELETE TableA
COMMIT TRAN

you could use  SET TRANSACTION ISOLATION LEVEL SERIALIZABLE


http://sqlservercode.blogspot.com/



Show quoteHide quote

BOL say that SET TRANSACTION ISOLATION LEVEL SERIALIZABLE would be in
effect for the "sesson".  The context here is that I am calling a SP
from a job.  Inside the SP would be this code below - after the SP
finishes, is this the end of that "session"?  Or does the session last
through the end of the job run?

create procedure proc1
as
SET TRANSACTION ISOLATION LEVEL SERIALIZABLE
BEGIN TRAN
        INSERT INTO TableB(col1, col2, col3)
        SELECT col1, col2, col3 FROM TableA

        DELETE TableA
COMMIT TRAN
go

from BOL under Remarks
Only one of the options can be set at a time, and it remains set for that
connection until it is explicitly changed

http://sqlservercode.blogspot.com/


Show quoteHide quote

No.  That won't work. Another session could still insert a row after your
INSERT but before the DELETE.  The OP had it right with TABLOCKX.

David

If there are no tables related to Table A (no foreign key constraints), then
instead of DELETE TableA, use TRUNCATE TABLE.
In this case WITH(HOLDLOCK, TABLOCKX) is the correct way to go; however, I
would add error handling so that if an error occurs during the INSERT, the
DELETE or TRUNCATE will not occur.  Some errors cause the batch to be
terminated, some errors cause the connection to be terminated, and some
errors cause the server to be terminated; but a constraint violation only
causes the offending statement to be terminated.  Without error handling,
the INSERT may fail but the DELETE may not, and consequently, you'll lose
data.


<unc27***@yahoo.com> wrote in message
Show quoteHide quote

Thanks for the quick replies - I think this is great information - I've
modified my procedure to reflect your comments....below is the SQL I'll
be using.

BEGIN TRAN
    INSERT INTO TableB(col1, col2, col3)
    SELECT col1, col2, col3 FROM TableA WITH (HOLDLOCK, TABLOCKX)

    IF @@ERROR = 0
        BEGIN
            DELETE TableA
        END

COMMIT TRAN

Why does update run differently standalone vs in procedure?
SELECT different columns based on the value of another column
Question about referencing UDFs
Torn over a normalizing decision
Mulitple Create Views in Query Batch