transaction isolation problem or wrong approach?

Question

I was helping out some colleagues of mine with an SQL problem. Mainly they wanted to move all the rows from table A to table B (both tables having the same columns (names and types)). Although this was done in Oracle 11g I don't think it really matters.

Their initial naive implementation was something like

BEGIN
  INSERT INTO B SELECT * FROM A
  DELETE FROM A
  COMMIT;
END

Their concern was if there were INSERTs made to table A during copying from A to B and the "DELETE FROM A" (or TRUNCATE for what was worth) would cause data loss (having the newer inserted rows in A deleted).

Ofcourse I quickly recommended storing the IDs of the copied rows in a temporary table and then deleting just the rows in A that matched the IDS in the temporary table.

However for curiosity's sake we put up a little test by adding a wait command (don't remember the PL/SQL syntax) between INSERT and DELETE. THen from a different connection we would insert rows DURING THE WAIT.

We observed that was a data loss by doing so. I reproduced the whole context in SQL Server and wrapped it all in a transaction but still the fresh new data was lost too in SQL Server. This made me think there is a systematic error/flaw in the initial approach.

However I can't tell if it was the fact that the TRANSACTION was not (somehow?) isolated from the fresh new INSERTs or the fact that the INSERTs came during the WAIT command.

In the end it was implemented using the temporary table suggested by me but we couldn't get the answer to "Why the data loss". Do you know why?

Answer 1

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 -

SERIALIZABLE Specifies 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.