transaction isolation problem or wrong approach?
Depending on your isolation level, selecting all the rows from a table does not prevent new inserts, it will just lock the rows you read. In SQL Server, if you use the Serializable isolation level then it will prevent new rows if they would have been including in your select query.
http://msdn.microsoft.com/en-us/library/ms173763.aspx -
SERIALIZABLESpecifies the following:
Statements cannot read data that has been modified but not yet committed by other transactions.
No other transactions can modify data that has been read by the current transaction until the current transaction completes.
Other transactions cannot insert new rows with key values that would fall in the range of keys read by any statements in the current transaction until the current transaction completes.
I can't speak to the transaction stability, but an alternate approach would be to have the second step delete from the source table where exists (select ids from target table).
Forgive the syntax, I have not tested this code, but you should be able to get the idea:
INSERT INTO B SELECT * FROM A;DELETE FROM A WHERE EXISTS (SELECT B.<primarykey> FROM B WHERE B.<primarykey> = A.<primarykey>);That way you are using the relational engine to enforce that no newer data will be deleted, and you don't need to do the two steps in a transaction.
Update: corrected syntax in subquery
This can be achieved in Oracle using:
Alter session set isolation_level=serializable;This can be set in PL/SQL using EXECUTE IMMEDIATE:
BEGIN EXECUTE IMMEDIATE 'Alter session set isolation_level=serializable'; ...END;