【Oracle學習06】DML與併發性,UNDO,死鎖

NO IMAGE

【Oracle學習06】DML與併發性,UNDO,死鎖

文章來源: 陶老師運維筆記-微信公眾號

關係數據庫中的數據由SQL的數據操作語言(Data Manipulation Language ,DML)命令進行行管理。
DML包括INSERT,UPDATE,DELETE,MERGE本節也會討論和DML緊密相關的事務控制語句COMMIT和ROLLBACK。

說明: 示例數據來自oracle自帶的hr​數據庫。文中內容主要摘錄於OCP考試指南,侵刪。

6.1 數據操作語言(DML)語句

嚴格說有5種DML(Data Manipulation Language)命令:

  • SELECT
  • INSERT
  • UPDATE
  • DELETE
  • MERGE

6.1.1 INSERT

語法:

insert into table values (value[,value...]);

示例:

create table emp_copy as select * from employees where 1=2;
insert into emp_copy select * from employees;
desc emp_copy;
select * from user_tab_columns where table_name='EMP_COPY';
##insert 可以條語句將多行插入多個表中。 
insert all when department_id<80 then
into emp_a(xxx) values (employee_id,department_id,salary)
when department_id>=80 then
into emp_b(xxx) values(employee_id,department_id,hire_date)
select department_id,department_id,salary,hire_date 
from employess ;

說明: insert 可以用同時插入多個表中,如示例。

6.1.2 UPDATE

UPDATE 中SET子句中使用更新列的子查詢必須為標量值。即只能是某數據類型的單值(不能是多個值),否則會失敗。

SQL> select salary*1.2 from employees where employee_id=206;
SALARY*1.2
----------
9960
#如下update成功
update emp_copy set salary = (select salary*1.2 from employees where employee_id=206);
#如下將失敗
update emp_copy set salary = (select salary*1.2 from employees where last_name like 'A%');
錯誤報告 -
ORA-01427: 單行子查詢返回多個行
#如下slary將為null,因為沒有last_name= 'A%'
select salary*1.2 from employees where last_name = 'A%'; 
update emp_copy set salary = (select salary*1.2 from employees where last_name = 'A%');

6.1.3 DELETE

#若無合適的記錄,即0條記錄刪除,也是成功。
delete from emp_copy where last_name = 'A%';
0 行已刪除。

6.1.4 TRUNCATE

  • TRUNCATE不是DML命令,而是DDL命令
  • Delete 等DML影響數據時,它們插入,更新,刪除是事務的一部分。而DDL是做為事務來執行,無法回滾。
  • 和DML相比,DDL執行速度更快。Truncate是瞬間完成,會重置高水位線(high water mark)。
  • 若是表中有活動的DML,則DDL會失敗。事務會中斷DDL命令,直到用COMMIT或者RoallBack終止DML為止。
#
TRUNCATE table emp_copy;
錯誤報告 -
ORA-00054: 資源正忙, 但指定以 NOWAIT 方式獲取資源, 或者超時失效
#kill session後,可以
TRUNCATE table emp_copy;
select * from emp_copy;

說明:
錯誤現象:TRUNCATE可能出錯,ORA-00054: 資源正忙, 但指定以 NOWAIT 方式獲取資源, 或者超時失效。
原因: 存在沒commit提交DML,也沒rollback回滾。
處理:blog.csdn.net/czh500/arti…

select l.session_id,o.owner,o.object_name from v$locked_object l,dba_objects o where l.object_id=o.object_id;
#session_id = 57,55
SELECT sid, serial#, username, oSUSEr,terminal,program ,action, prev_exec_start FROM v$session where sid in (57,55);
alter system kill session '57';

6.1.5 MERGE

Merge命令可以實現若是沒有匹配行,就insert,若是存在就更新此行。

MERGE INTO table_name alias1
USING (table | view | sub_query) alias2
ON (join condition)
WHEN MATCHED THEN
UPDATE table_name SET col1 = col_val1, col2 = col2_val
WHEN NOT MATCHED THEN
INSERT (column_list) VALUES (column_values);

操作alias1表的數據,其操作數據的來源為 alias2中數據的記錄數,通過on括號中的條件進行過濾,當alias1和alias2中的數據通過on條件進行過濾後,如果存在就執行更新操作,如果不存在就執行插入操作。

6.1.6 DML語句失敗

失敗原因

  • 語法錯誤
  • 引用不存在的對象或列
  • 訪問權限
  • 約束違背
  • 空間問題

約束違背會導致DML語句失敗。例如Insert 多條記錄,其中有一條記錄包括了重複鍵,將返回一個語句失敗,此失敗將觸發撤消所有全部已成功的插入!

6.2 控制事務

事務:
由一條名多個DML語句組件,後面緊跟ROLLBACK 或COMMIT命令,可以在事務內部使用SAVEPOINT命令給出控制程度。

6.2.1 數據庫事務

Oracle確保事務完整性的機制是撤消段和重做日誌文件的組件。

事務特性:

  • 原子性 (Atomicity) : 事務包含的所有操作要麼全部成功,要麼都不完成,回滾。
  • 一致性(Consistency): 事務開始前和結束後,數據庫的完整性約束沒有被破壞。查詢的結果必須和查詢開始的狀態一致。 Oracle用撤消段來保證。
  • 隔離性(Isolation): 一個用戶併發訪問數據庫時,不能被其他事務的操作所幹擾,多個併發事務之間要相互隔離。未完成提交的事務,是不可見的。
  • 持久性(Durability): 持久性是指一個事務一旦被提交了,那麼對數據庫中的數據的改變就是永久性的,即便是在數據庫系統遇到故障的情況下也不會丟失提交事務的操作。

Oracle用撤消段來保證事務的隔離性,一致性和原子性。

6.2.2 執行SQL語句

事務的開始和結束
事務的開始和結束會話發出DML命令時,就開始事務。事務持續執行任何數量的DML命令,COMMIT或者ROLLBACK語句為止。

只有提交的變更才會變得永久,才會對事務不能嵌套,SQL標準不允許用戶啟動一個事務,然後在終止第一個事務之個事務。
使用 PL/SQL( Oracle專有的第三代語言)可以這樣做,但它不是行業標顯式事務控制語句有 COMMIT、 ROLLBACK和 SAVEPOINT。

除了用戶發出的COMMIT或者 ROLLBACK之外,還有一些情況會隱式終止事務:

  • 發出DDL(CREATE,ALERT,DROP)或者DCL(GRANT/REVOKE)語句
  • 退出用戶工具(SQL*Pus、 SQL Developer或者其他工具)
  • 客戶會話終止
  • 系統崩潰

6.2.3 事務控制: COMMIT, ROLLBACK,SAVEPOINT,SELECT FOR UPDATE

www.cnblogs.com/toughhou/p/…

1. COMMIT

COMMIT發生的操作是LGWR進程將日誌緩衝區的內容刷新到磁盤。 DBWn進程完全沒有執行任何操作。
任何DDL,DCL都將提交當前的事務。

2.ROLLBACK命令

ROLLBACK [TO SAVEPOINT savepoint];

3.SAVEPOINT命令

只有事務內才可以看到SAVEPOINT位置。
語法:

SAVEPOINT identifier
ROLLBACK [WORK] TO SAVEPOINT identifier
RELEASE SAVEPOINT identifier

示例:

CREATE TABLE TMP003(ID NUMBER,"DESC" VARCHAR2(200));
INSERT INTO TMP003 VALUES(1,'第一條記錄');
#設置保存點bk
SAVEPOINT bk;
INSERT INTO TMP003 VALUES(2,'第二條記錄');
select * from TMP003
#回滾到保存點bk
rollback to bk;
commit ;
select * from TMP003;
1 第一條記錄

4. SELECT FOR UPDATE

多用戶下如下情況下可能會出現異外的情況。

【Oracle學習06】DML與併發性,UNDO,死鎖

此例中用戶1,雖然開始select 看到id=5,但是最後因為id=5被另一事務delete了,故update將只更新0行記錄。
解決此問題方法之一可提前鎖定用戶感興趣的行。 會鎖定檢索的行,除了發出命令的會話外,其它會話都不能改變它們。 代價是其它的會話或要修改只能等待(此時其它會話可以查詢)。

select * from regions for update;

5.自動提交

自動提交有時叫隱式提交。如DDL,SQLPlus退出等。原因是DDL源碼及SQLPLUS退出中已嵌入了一條COMMIT命令。 直接點SQLPLUS 右角的‘X’關閉,則不會COMMIT, 將回滾事務。

6.3 回滾和撤消

回滾數據和撤消數據,功能相同,但是管理方式不同。

回滾:

回滾事務指用撤消段中的數據來構造一個與事務發生前相同的數據映象。
根據UNDO_MANAGEMENT的參數設置不同,Oracle要麼使用撤消段,要麼使用回滾段。兩者是予盾衝突的。

撤消數據將保持到生成它的事務以COMMIT或ROLLBACK結束為止

select value from v$parameter where name='undo_management';
VALUE
AUTO;
#若不是AUTO,則修改
alter system set undo_management= auto scope = spfile;
#查看撤消表空間
SQL> select tablespace_name from dba_tablespaces where contents='UNDO';
TABLESPACE_NAME
------------------------------------------------------------
UNDOTBS1
select value from v$parameter where name='undo_tablespace';
#查看撤消段大小
select tablespace_name,segment_name,segment_id,status from dba_rollback_segs;
select usn,rssize from v$rollstat;
#查看近來數據庫生成的撤消數據量
alter session set nls_date_format='dd-mm-yy hh24:mi:ss';
#select value from v$parameter where name='db_block_size'; --8192
select begin_time,end_time,
(undoblks * (select value from v$parameter where name='db_block_size')) undo_bytes 
from v$undostat;

6.4 監視和管理撤消數據

撤消段的一個主要特性是其被自動管理。需要保證有足夠的撤消空間。

撤消管理的參數:
有四個初始化參數可控制撤消:

  • UNDO_MANAGEMENT : 默認AUTO,表示啟用撤消段的工作。
  • UNDO_TABLESPACE
  • UNDO_RETENTION : 撤消數據的保留時間相關
  • TEMP_UNDO_ENABLED : 存儲全局臨時表上DML生成的撤消數據。
SQL> show parameters undo;
NAME              TYPE    VALUE    
----------------- ------- -------- 
temp_undo_enabled boolean FALSE    
undo_management   string  AUTO     
undo_retention    integer 900      
undo_tablespace   string  UNDOTBS1 
#
select begin_time,end_time,undoblks,activeblks,unexpiredblks,maxquerylen,txncount from v$undostat;

創建和管理撤消表空間(Undo Tablespaces):

create undo tablespace jinlian_undo datafile  '/u01/app/oracle/oradata/orcl/jinlian_undo.dbf' size 20 M;
select tablespace_name,status,contents from dba_tablespaces where contents = 'UNDO';
TABLESPACE_NAME                STATUS    CONTENTS
------------------------------ --------- ---------
UNDOTBS1                       ONLINE    UNDO
JINLIAN_UNDO                   ONLINE    UNDO
#
alter database datafile '/u01/app/oracle/oradata/orcl/jinlian_undo.dbf' autoextend on ;
select file_id,file_name,tablespace_name,autoextensible from dba_data_files where tablespace_name like 'JIN%';
#查詢表空間情況
select tablespace_name,contents,retention from dba_tablespaces;
#
select name,value from v$parameter where name like '%undo%';
alter system set undo_retention = 1200; #正常是900s,15分鐘
select name,value from v$parameter where name like '%undo%';

6.5 撤消數據和重載數據的區別

  • 撤消是臨時的,而重做是永久的。撤消數據至少在生成它的事在期間是存在的,但可能不長於這段。最終,總是會被覆蓋。重做時間是不確定的,首先存在於聯機文件中,之後存在於歸檔日誌文件.
  • 撤消在邏輯層上進行,而重做在物理層上進行。
  • 撤消可以反轉更改,而重做可以重複更改。撤消數據提供了反轉已提交事務的功能。重做提供了重放已丟失工作的功能
  • 撤消位於表空間,而重做位於文件中。撤消數據是數據庫中的一個段結構,重做寫入操作系統文件.
  • 撤消與重做不是相反的。不同的功能。撤消關乎事務的完整性,而重做的作用是防止數據丟失。

閃回查詢

閃回查詢允許用戶查看數據庫在以前某個時間的狀態。最簡單就是用帶as of子句查詢SQL。

select employee_id,salary from employees where employee_id=206;
update employees set salary=2600 where employee_id=206;
select * from employees as of timestamp (systimestamp - 10/1440)  where employee_id=206  ;

一個常用的場景是誤刪除,可以反轉此操作,如下。

delete from employees where employee_id=206;
#閃回插入,這些是從撤消段UNDO中檢索出來
insert into employees ( select * from employees as of timestamp (systimestamp - 10/1440)  where employee_id=206 ) ;

6.6 鎖定機制和數據併發管理

藉助於記錄和表鎖機制,可以實現併發訪問的串行化。Oracle數據庫中的鎖是完全自動的。

6.6.1 共享鎖與排他鎖

若是某一會話正在更新某一行,則此行會被鎖定,防止其它的會話對其進行修改,但其它會話可以執行讀操作。 只有commit或rollback命令結束事務,此鎖才會解除。
這鎖是一個排他(execlusive)鎖:
行記錄中第一申請排他鎖的會話得到鎖,其它會話寫操作必須等待。 對一行或表上的排他鎖來說,每次只能有一個會話得到這個排他鎖。

共享鎖(shared):

共享鎖置於整個表,多個會話可以獲得同一個表上的共享鎖。 **表上的排他鎖需要執行DDL語句。**如果其它會話已在表上放置了共享鎖,則此會話就無法對此對象執行修改操作。

在表上放置共享鎖的目的是防止其他會話得到此表的排他鎖(存在共享鎖,其它會話就得到排他鎖)

  • 所有DML語句至少需要兩種鎖: 1)更改記錄上的排他鎖。 2)包含受影響記錄的表上的共享鎖。
  • DDL語句需要所涉及對象上的排他鎖。只有針對表上的所有DML事務結束,且行上的排他鎖,表上共享鎖都解除後,才能獲得執行DDL命令所需的排他鎖。

任何未提交的Insert,update,delete命令都會導致辭表上的任何DDL立即失敗。

【Oracle學習06】DML與併發性,UNDO,死鎖

6.6.2 排隊機制

請求鎖定需要排隊。若某會話請求鎖定,但是已有其它會話鎖定了行或對象,導致無法獲取對象所需要的鎖,哪麼這個會話將會等待。而當使用鎖定的會話解除鎖定時,下一個會話將獲得授權,這種機制叫”排隊(enqueue)”。
SELECT xx FOR UPDATE會採用排他模式來選擇和鎖定記錄。

6.6.3 自動和手動鎖定

1)自動鎖定:

無論執行什麼DML語句,在執行過程中,會話會自動在表上加一個共享鎖,在影響行上施加獨佔鎖。事務用commit/rollback時,鎖會自動釋放。
執行DDL語句時,會話會自動在整個對象上施加一獨佔鎖,這個鎖在DDL整個過程中存在,執行完畢合,自動釋放。
DDL在內部實現上實際上是對數據字典進行了DML語句。

2)手動鎖定

lock table table_name in mode_name mode;
#

鎖定模式:
有5種模式,相互間的兼容性見表。

【Oracle學習06】DML與併發性,UNDO,死鎖

DML語句需要所涉及對象上的共享鎖,以及涉及行上的排他鎖。

6.7 監控和解決鎖爭用(死鎖等)

鎖定爭用:
當會話請求一行或對象上的鎖,需要等待其它會話的鎖釋放,此會話將掛起。這現象叫鎖定爭用。 會導致數據庫性能急劇惡化。
死鎖(deadlock)是鎖定爭用的一種特殊情況。

6.7.1 鎖定爭用的原因

  • 多業務同時對相同的記錄進行寫訪問。解決方案是重新設計業務流程開發更好的業務模型。
  • 長事務引起。 更新某行,但不提交,會導致時間很長的事務。 如DBA在沒有結束事務時就出去吃飯。
  • 編寫不好的批處理過程。 如財務軟件計算總賬
  • 過高的鎖級別。如select xxx for update。
  • 不當的鎖操作。 如為了解決可重複讀,用了LOCK FOR UPDATE,但可以用set transaction read only更好。

6.7.2 檢測鎖定爭用

  • Database Express 是一處圖形界面
  • 可以在V$SESSION視圖中查看。 當前的會話顯示為一行,唯一標識符是列的會話標識符(SID)
    【Oracle學習06】DML與併發性,UNDO,死鎖
select * from  hr.employees where employee_id=206 for update ;
update hr.employees set SALARY=1000 where employee_id=206 ;
#
select waiter.username "Bocked session",waiter.sid , blocker.username "blocking session", blocker.sid,blocker.serial# 
from v$session waiter join v$session blocker on ( waiter.blocking_session=blocker.sid ) order by blocker.sid,waiter.sid;
#
alter system kill session 'sid_xx, serial#';

6.7.3 解決鎖定爭用

DBA可以止佔用過多鎖定的會話來解決爭用問題。

alter system kill session 'sid, serial#';

6.7.4 死鎖:一種特殊情況

兩個會話相互阻塞,這兩個會話都在等待另一個會話釋放其鎖定,這種場景稱為”死鎖”。其通常和不當設計有關。 oracle會把死鎖的信息寫入警報日誌。

死鎖:

是指兩個或兩個以上的進程在執行過程中。因爭奪資源而造成的一種互相等待的現象,若無外力作用,它們都將無法推進下去。此時稱系統處於死鎖狀態或系統產生了死鎖,這些永遠在互相等待的進程稱為死鎖進程。

死鎖的四個必要條件:
(1) 互斥條件:一個資源每次只能被一個進程使用。
(2) 請求與保持條件:一個進程因請求資源而阻塞時,對已獲得的資源保持不放。
(3) 不剝奪條件:進程已獲得的資源,在末使用完之前,不能強行剝奪。
(4) 循環等待條件:若干進程之間形成一種頭尾相接的循環等待資源關係。

構建死鎖:

【Oracle學習06】DML與併發性,UNDO,死鎖

#會話1:
create table lockdemo as select * from all_users;
select * from lockdemo where username in ('SYS','SYSTEM');
#會話2: 
update lockdemo set user_id=99 where username='SYS';
select * from lockdemo where username='SYS';
#會話3:
update lockdemo set user_id=99 where username='SYSTEM';
#會話2:
update lockdemo set user_id=99 where username='SYSTEM';
在行: 3 上開始執行命令時出錯 -
update lockdemo set user_id=99 where username='SYSTEM'
錯誤報告 -
ORA-00060: 等待資源時檢測到死鎖
##會話3:
update lockdemo set user_id=99 where username='SYS';

3秒種後,將檢查到死鎖,並第一個會話會被釋放。

在行: 3 上開始執行命令時出錯 -
update lockdemo set user_id=99 where username='SYSTEM'
錯誤報告 -
ORA-00060: 等待資源時檢測到死鎖

【Oracle學習06】DML與併發性,UNDO,死鎖

死鎖解除:

【Oracle學習06】DML與併發性,UNDO,死鎖

#-查看
select username,sid,blocking_session, serial# from v$session where blocking_session is not null; #
-查找死鎖會話id
select * from v$locked_object;
select session_id from v$locked_object;
--根據會話id查找死鎖對象
select sid,serial#,username,osuser fromv $session where sid=58
--清除死鎖
#alter system kill session 'sid_no,servial#';
alter system kill session '58,27232';

kill會話結果

#alter system kill session '58,27232';
#另一個窗口會話58將被kill
SQL> update lockdemo set user_id=99 where username='SYS';
update lockdemo set user_id=99 where username='SYS'
*
ERROR at line 1:
ORA-00028: your session has been killed

6.8 知識回顧

DML數據操作語言:

  • TRUNCATE不是DML命令,但是可以刪除表中全部的記錄。
  • TRUNCATE立即生效,並且無法回滾。
  • INSERT 可以將一些行,同時插入多個表中。
  • 在提交之前,INSERT,UPDATE,DELETE並不是永久生效。

控制事務:

  • 事務是邏輯工作單元,可能由幾個DML語句組成
  • 在提效之前,事務對其他會話不可見。
  • 在提交之前,可以回滾事務。
  • SAVEPOINT讓會話回滾部分事務。

DML和撤消數據生成:

  • 所有DML命令都生成撤消和重做內容
  • 重做保護對段(撤消段和數據段)所做的所有更改。
  • 服務器進程從數據文件讀取信息,DBWn針對數據文件執行寫操作。

監控和管理撤消數據:

  • 實例將在一個指定的撤消表空間中使用撤消段。
  • 可以存在多個撤消表空間,但每次只能使用一個。
  • 撤消表空間應該有足夠的存儲量,以便最高效撤消生成速度。
  • 撤消表空間數據文件與其他數據文件類似

撤消數據和重做數據區別:

  • 撤消操作保護事務
  • 重做操作保護塊的變更,兩者不是相反,而是互補關係。

配置撤消保留時間:

  • 撤消數據將保持到生成它的事務以COMMIT或ROLLBACK結束為止,這是”active(活動)”撤消。
  • 撤消數據在進入不活動狀態後,會保留一段時間,滿足長期運行的查詢的任何讀一致性要求,這是”unexpired(未過期)”撤消。
  • “expired(過期)撤消”是讀一致性不再需要的數據,在重用撤消段的空間時,將隨時重寫”expired” 撤消。
  • undo_retention參數,配置了撤消保留時間。但是若是撤消空間不足,就可能達不到此目標。

鎖定機制和數據併發管理

  • 默認的鎖定級別是行級別
  • 鎖定對於所有DML命令是必需的,對於SELECT 是可選的。
  • DML語句需要所涉及對象上的共享鎖,以及涉及行上的排他鎖。
  • DDL鎖定需要受影響對象上的排他鎖。

監視和解決鎖定衝突

  • 查詢v$session視圖或使用Database Express可以識別出行鎖定導致的阻塞。
  • 終止阻塞的事務或停止阻塞的會話,可以解決鎖定衝突。
  • 死鎖將自動解決。

參考:


【Oracle學習06】DML與併發性,UNDO,死鎖

相關文章

面試常問的PECS原則,到底是什麼鬼?

聊聊rocketmq的RemotingTooMuchRequestException

HTMLEmail的編寫

「從模板消息改版訂閱消息」小程序推送