Re: Re-enabling locks on any one table requires NO DML activity anywhere in the DB
Date: Tue, 31 Jan 2017 23:16:52 +0000
Message-ID: <MMXP123MB0911896FBF6C5534B6FD98E3A54A0_at_MMXP123MB0911.GBRP123.PROD.OUTLOOK.COM>
The point is that ANY active transaction MAY have uncommitted changes on the table in question and the enabling session won't be able to see them because TM locks on the table won't have been created, so the session trying to enable locking has to wait for all current transactions to commit so that it knows there are no active transactions on the table. (Session which try to modify the table after the enable starts will wait on a KGL Lock, so won't introduce new waits - but if they had a transaction already active they're effectively in a deadlock and will wait until the enabler times out.
Regards
Jonathan Lewis
From: oracle-l-bounce_at_freelists.org <oracle-l-bounce_at_freelists.org> on behalf of Chris Taylor <christopherdtaylor1994_at_gmail.com> Sent: 31 January 2017 22:18:24
To: ORACLE-L
Subject: TIL: Re-enabling locks on any one table requires NO DML activity anywhere in the DB
This caught me completely off guard today. Maybe I should have known, but Oracle requires NO DML activity occurring anywhere in the DB to re-enable table locks on any one table.
That really surprised me since if the table being operated on has DISABLED LOCKS, then how does other locking activity in the DB somehow interfere?
The support document says there's now no way to know if anyone is updating the table... but if you disabled locking, then how could that even happen?
From the Support site:
Doc ID 857975.1
The session attempting to enable the table lock must wait until ALL active DML transactions in the database have completed before locking the table. Once the DISABLE TABLE LOCKS feature has been used, then you stop Oracle from taking out ANY TM locks when the table is updated. If the user tries to ENABLE table locking again, Oracle has no way of knowing if anyone is actually updating the table at this time (because there are no TM locks!). The only solution is to wait for all active transactions to complete. This is expected behavior and not a bug. This is documented in the following manual:
To re-enable table locks you need to ensure there are no current active DML transactions in the database by other users. The easiest way to achieve this is to shutdown the database and startup it in restricted mode. Then, try to enable the locking.
-- http://www.freelists.org/webpage/oracle-lReceived on Wed Feb 01 2017 - 00:16:52 CET