Re: truncate table

From: Maxim Demenko <mdemenko_at_gmail.com>
Date: Thu, 26 May 2005 00:23:54 +0200
Message-ID: <d72ttp$l51$03$1@news.t-online.com>

Matthias Hoys schrieb:

> <Bullseye> wrote in message 
> news:efo991dss2qh10tlfeanurdpjulhomsif8_at_4ax.com...
> 

>>Hi!
>>
>>I have a problem - Can anyone help?
>>
>>I have a requirement to lock a table (to deny all write access to it),
>>update records in another table, and then to truncate the "locked"
>>table before allowing access to it.
>>
>>The problem is that the truncate table issues a commit (which is fine)
>>but the commit releases my lock and allows queued transactions to
>>commit BEFORE actionning the truncate (Of it seems to!).
>>
>>How can I ensure that the truncate table is executed before any other
>>transactions are committed bu other processes?
>>
>>TIA

> 
> 
> SELECT ... FROM ... FOR UPDATE locks the entire table.
> 
> 
> HTH
> Matthias
> 
> 
> 

Apparently select for update doesn't prevent from insert ( as i understood the OP , all write access should be denied) into table, so LOCK TABLE IN EXCLUSIVE MODE would more appropriated at this place. But , regarding the OP, as TRUNCATE is issued after an implicit COMMIT, i would not know, how to prevent queued transaction to be executed ... Maybe , a only one possibility will be to DELETE instead of TRUNCATE.

Best regards

Maxim Received on Wed May 25 2005 - 17:23:54 CDT