How do I atomically replace a set of rows?

Question

I have a table of products which is populated from a file. When the file is updated I need to replace all the rows in the table with the new data.

The simple way to do this is to delete all the existing rows and then insert the new rows. However I am concerned that if one transaction is reading the rows at the same time as the inserts then the transaction could end up read rows from both sets.

So how do I stop this? If I use SERIALIZABLE transactions then I reckon that in this situation there could be a deadlock.

mvsagar · Answer 1 · 2013-09-09T06:46:11.473

1

There can be a deadlock only if two transactions use at least two resources(tables/rows) each. Is this the case in your transactions? Also deleting all rows from a table is inefficient. You may have to check if you can use TRUNCATE statement and its implications on transactions.

@IanWarburton, as @steve suggested you do not want to use TRUNCATE statement rather stick to DELETE statements! Here is a solution that might work for you. RDBMSs usually have some way of handling deadlocks. In SQL server, it is possible to set deadlock priority for a session. Idea is to set high priority for the session (main txn) which deletes existing rows and inserts new rows by reading the file you mentioned. For all other session transactions(other txns), set low priority so that these transactions will be made victim in case of deadlocks. This gives the main txn to execute to completion. You need to use SERIALIZABLE isolation level for the main txn. Other txns receive an error (1205) on deadlock, hence rollback their transaction and restart them.

You may refer to http://technet.microsoft.com/en-us/library/ms178104(v=sql.105).aspx for more information on deadlock detection and handling.

edited Sep 09 '13 at 06:46

answered Sep 08 '13 at 15:00

mvsagar

1,583
1
15
16

I'm not worried about inefficiency. Its an uncommon operation. What do you mean a deadlock can only happen with two tables? – Ian Warburton Sep 08 '13 at 15:01
Refer to http://stackoverflow.com/questions/2774935/what-is-deadlock-in-a-database for information on deadlock. – mvsagar Sep 08 '13 at 16:10
There's nothing in that link about a deadlock requiring two transactions to use at least two tables each. – Ian Warburton Sep 08 '13 at 16:23
Using truncate is bad advice. Truncate is a ddl statement and will implicitly commit when it completes. Inserting the new rows will be a second transaction. – steve Sep 09 '13 at 03:06
@steve, I have rolled back my advice! Thanks. – mvsagar Sep 09 '13 at 06:47
@steve: that's wrong. `TRUNCATE` is transactional in SQL Server (and other DBMS) and *can* be rolled back. See here for an example: http://sqlfiddle.com/#!3/09c03/1 – a_horse_with_no_name Sep 09 '13 at 06:49
The above statement is not correct. This depends on the dbms implementation of the truncaye command. SQL Server and Postgres allow you to rollback. However Oracle and MySQL do an implicit commit. So depending on the dbms you are using and if you need to be platform neutral you have to make the correct decision. – steve Sep 10 '13 at 04:01

Ian Warburton · Accepted Answer · 2013-09-09T17:18:30.393

How about this...

using (var transaction = new TransactionScope())
{
   Context.Database.ExecuteSqlCommand("DELETE Items WITH (TABLOCKX)");

   SqlBulkCopyHelper.BulkInsert(Context.Database.Connection.ConnectionString, "Items", items);
}

Where 'Context' is an Entity Framework DbContext and SqlBulkCopyHelper I got from here.

TransactionScope defaults to SERIALIZABLE so I think the table lock will be held for the duration.

How do I atomically replace a set of rows?

2 Answers2