Combining queries for atomicity

Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

All Forums

SQL Server 2000 Forums

SQL Server Development (2000)

Combining queries for atomicity

Author

Topic

vleino
Starting Member

1 Post

Posted - 2004-06-28 : 07:31:59

I'm looking for a way to retrieve tuples from the DB and then immediately delete them. How could I perform these two basic SQL queries so that they would appear to be atomic together?
SELECT * FROM table WHERE condition=0;
DELETE * FROM table WHERE condition=0;

As a workaround I came up with a flagging field with which I would use an INSERT query before the SELECT query and then use the flag as a condition for both SELECT and DELETE queries, but if possible, I would like to do this without such kludges.

Thanks a bunch,
Ville

RickD
Slow But Sure Yak Herding Master

3608 Posts

Posted - 2004-06-28 : 07:44:13

What exactly are you trying to do? Why do you need to select the record before deleting it?!?!

Oh and it's not Delete * from... unless you're using Access... It's DELETE FROM...

mohdowais
Sheikh of Yak Knowledge

1456 Posts

Posted - 2004-06-28 : 07:44:51

You can use locking hints in the SELECT statement or change the Transaction Isolation level to Seriablizable. Example using a locking hint:


BEGIN TRANSACTION
SELECT * FROM tableName WITH (holdlock) WHERE condition = 0
DELETE * FROM tableName WHERE condition = 0
COMMIT TRANSACTION

The above code will retain the lock on the rows that were returned by the select for this transaction, and the lock is held until the transaction is complete. These locking hints must be used with caution though, as they can dramatically affect scalability of the application. SQL Server was designed for concurrent access, holding locks for extended periods could cause deadlocks.

OS

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources