How to do locking in SQL Server like MYSQL

Question

Hi all,

I'm converting a webapp from mysql to SQL Server. Now I want to convert the following code (this is a simplified version):

LOCK TABLES media WRITE, deleted WRITE;
INSERT INTO deleted (stuff) SELECT stuff FROM media WHERE id=1 OR id=2;
DELETE FROM media WHERE id=1 OR id=2;
UNLOCK TABLES;

Because I'm copying stuff which is going to be deleted I want to make sure any reads to 'media' or 'deleted' will wait until this whole operation is ready. Otherwise these reads will see stuff that isnt there anymore a sec later.

How can I replicate this behavior in SQL Server? I read some pages on transactions and isolation levels but I can't figure out if I can disable any read to table 'media' and 'deleted' (or on row-level).

Thanx!

Answer 1

As a DB-agnostic approach, you might consider having a "deleted" or "inactive" column indicating whether or not results should be returned to users. For example, you could use an integer for the column, excluding the record from user's view if the value of the column is not zero. So, instead of the select and insert above, you could do (all examples are in the MySQL SQL dialect):

UPDATE media SET inactive=1 WHERE stuff=1 OR stuff=2;

This would exclude the records from user view. You could then copy the inactive records to the "deleted" table and delete them from the media table if desired, based on the time you last updated inactive records:

INSERT INTO deleted (stuff) SELECT stuff FROM media WHERE inactive = 1;
DELETE from media WHERE inactive <= 1;

The integer could be used to identify the "inactive job" that "deleted" the records.

Based on how you've described the schema, this scenario doesn't quite match what the locking approach because the "media" table could be modified during the execution of the UPDATE statement. That could be solved (or at least mitigated) if there were a column, such as a time stamp, that could be used to further define the records to mark inactive.

Answer 2

You could use lock hints in your query. If you specify a table lock and hold it until the end of the transaction, that should be equivalent.

begin transaction;

INSERT INTO deleted
   SELECT stuff FROM media WITH (tablock holdlock)
   WHERE id = 1 or id = 2;

DELETE FROM media where id = 1 or id = 2;

commit;