Prove that Truncate issued an observable commit command

zhaozj2021-02-16  62

In general, we usually believe that Truncate's pseudo code is: commit; ---------- 1TRUNCATE; commit; ------------ 2, we have questions, commit Is it indivisible to 1 and Truncate? Is it atomic? Then let's take a look at the demonstration of the following: SQL> Create Table T As Select * from all_Objects where rownum <11; table created.sql> delete from t where rownum = 1; 1 row deled.sql> session 2: SQL> Lock Table t in Exclusive mode; It is obvious, session 2 is here to stay here session 1: SQL> Create Table T As SELECT * ALL_OBJECTS WHERE ROWNUM <11; Table Created.SQL> Delete from T where rownum = 1; 1 Row deleted.sql> commit; commit completion.sql> Review session 2sql> lock table t in Exclusive mode; table (s) locked.sql> Plock successfully We will perform DELETE operations on Session 1 SESSION 1: SQL > CREATE TABLE TAS SELECT * ALL_OBJECTS WHERE ROWNUM <11; Table Created.sql> Delete from T where rownum = 1; 1 row deled.sql> commit; commit complete.sql> delete from t where rownum = 1; then The next step we go to Session 2 to try TruncateSession 2: SQL> Lock Table T IN EXCLUSIVE; TABLE (S) Locked.SQL> Truncate Table T; Truncate Table T * Error At line 1: ORA-00054: Resource Busy and acquire with nowait specifiedsql> Discover truncate failed, cut to session 1 Take a look at SQL> Create Table T As SELECT * ALL_OBJECTS WH Ere Rownum <11; Table Created.SQL> Delete from T where rownum = 1; 1 row deled.sql> commit; commit complete.sql> delete from t where rownum = 1; 1 row deleted.sql> Discover session 1 deletion Actually, the Truncate of Session 2 failed. This shows that after the session 2 truncate issued Commit 1, TRUNCATE has not been executed, before this Delete operation is blocked by the Lock Table but the time is before Truncate, so the session session is in front, thus Leading truncate failed.

转载请注明原文地址:https://www.9cbs.com/read-17490.html

New Post(0)