August 23, 2007 at 11:14 am
Hello,
Some suggestion from you:
A Table should be read and then purged it to fill again later:
the process is:
select * form T1
Delete from T1
what is the best way (also in term of performance) to have that from the select and to the end of delete statements, no external sources can modify or insert new records?
Thank
August 23, 2007 at 11:52 am
encapsulate whatever you're doing in a transaction, and then use a locking hint to prevent updates.
UPDLOCK looks like the one, and be sure to specify to do it at the table level (TABLOCK).
something like:
BEGIN transaction MyTrans
select * from T1 with (UPDLOCK, TABLOCK)
<...other actions....>
delete from T1
COMMIT transaction MyTrans
Now - this should ensure no changes from beginning to end, but will block anything else that tries to access that table. So - it's safe, but can sometimes cause perf issues due to blocking.
----------------------------------------------------------------------------------
Your lack of planning does not constitute an emergency on my part...unless you're my manager...or a director and above...or a really loud-spoken end-user..All right - what was my emergency again?
August 24, 2007 at 5:57 am
Add a HOLDLOCK hint to retain the read locks through the transaction:
BEGIN TRANSACTION
SELECT ...
FROM t1 WITH(TABLOCKX, HOLDLOCK)
-Eddie
Eddie Wuerch
MCM: SQL
August 24, 2007 at 8:44 am
If the table contains unique keys, you can keep the integrity of your extract without having to hold up other users:
- select keys to a temporary table;
- select the data on the join ; and
- delete on the join.
August 24, 2007 at 1:36 pm
Thank everybody,
I wiil try your suggestions.
August 24, 2007 at 3:17 pm
Or
SET TRANSACTION ISOLATION LEVEL SERIALIZABLE
BEGIN TRANSACTION
SELECT * from table
COMMIT TRANSACTION
SET TRANSACTION ISOLATION LEVEL READ COMMITTED
* Noel
Viewing 6 posts - 1 through 5 (of 5 total)
You must be logged in to reply to this topic. Login to reply