在剛剛過(guò)去不久的第七屆數(shù)據(jù)技術(shù)嘉年華上，性能優(yōu)化專家懷曉明老師進(jìn)行了Oracle性能優(yōu)化的主題分享。在他多年的優(yōu)化生涯中，一直遵守的優(yōu)化理念是，平衡是唯一的核心。我們整理了懷老師大會(huì)的演講內(nèi)容，今天一起來(lái)學(xué)習(xí)，如何在實(shí)踐中應(yīng)用這一理念并實(shí)現(xiàn)有效的性能優(yōu)化。

演講實(shí)錄

優(yōu)化的核心思想是平衡。在數(shù)據(jù)庫(kù)的運(yùn)行中，平衡取決于三個(gè)方面：

需求：指的是要做什么；

資源：是系統(tǒng)中所能提供的內(nèi)容；

實(shí)現(xiàn)：指的是為了滿足需求，應(yīng)該如何利用提供的資源。

只有三者達(dá)到平衡，系統(tǒng)才能夠高效地運(yùn)行。

今天的內(nèi)容將會(huì)通過(guò)Oracle 中一個(gè)很具體的等待事件 TX行鎖來(lái)剖析數(shù)據(jù)庫(kù)的平衡。

什么是TX行鎖？

等待事件： enq: TX - row lock contention

enq代表的意思是enqueue,事實(shí)上代表的是入隊(duì)這一個(gè)動(dòng)作。

contention指的是爭(zhēng)用，所以一般意義上的行鎖，其實(shí)指的是行鎖爭(zhēng)用。

不管是在Oracle數(shù)據(jù)庫(kù)還是其他關(guān)系型數(shù)據(jù)庫(kù)，在修改一條記錄的時(shí)候一定會(huì)產(chǎn)生行鎖。其目的是為了保證數(shù)據(jù)的一致性，如果行鎖長(zhǎng)久不能得到釋放，當(dāng)其他進(jìn)程想要使用的時(shí)候，就會(huì)產(chǎn)生爭(zhēng)用。這種情況一般發(fā)生在先行的會(huì)話事務(wù)沒(méi)有結(jié)束的時(shí)候。

TX行鎖發(fā)生的常見(jiàn)場(chǎng)景：

1、當(dāng)前會(huì)話要更新或刪除的記錄，已經(jīng)被其他會(huì)話更新或刪除。

2、對(duì)于表上有唯一索引的情況，多個(gè)會(huì)話插入或更新為相同的鍵值。

3、對(duì)于表上有位圖索引的情況，多個(gè)會(huì)話即使更新不同記錄，只要這些記錄在位圖索引上的鍵值相同，也會(huì)產(chǎn)生行鎖。

一般我們可能認(rèn)為在發(fā)生行鎖的時(shí)候，幾個(gè)SQL的語(yǔ)句是一樣的，事實(shí)上這種理解是錯(cuò)誤的。我舉一個(gè)簡(jiǎn)單的例子：

首先在表上找到j(luò)ob為manager的記錄，有三條：

select empno from emp where job='MANAGER';

--顯示7566/7698/ 7782 三條記錄。

之后在會(huì)話1 將部門ID為10的員工的記錄刪除掉

sess1：delete from emp where deptno=10;

--?7782/7839/ 7934 三條記錄被刪除，但并未提交。其中7782的記錄剛好是job為manager的。

接下來(lái)在session2做一個(gè)delete的操作，此時(shí)就會(huì)被hang住。

sess2：delete from empwherejob='MANAGER';

那么hang的情況說(shuō)明時(shí)候會(huì)結(jié)束呢？

--if sess 1 rollback, 7566/ 7698/7782將被刪除 --if sess 1 commit, 7566/ 7698將被刪除

也就是說(shuō)只有資源被釋放，系統(tǒng)才會(huì)解除TX行鎖。

TX行鎖的危害：會(huì)導(dǎo)致其他會(huì)話的相關(guān)業(yè)務(wù)操作hang住

1、業(yè)務(wù)操作長(zhǎng)時(shí)間無(wú)法完成

用戶投訴

2、會(huì)導(dǎo)致會(huì)話積壓

數(shù)據(jù)庫(kù)連接池逐漸被占滿

• 應(yīng)用獲取不到數(shù)據(jù)源無(wú)法創(chuàng)建新的數(shù)據(jù)庫(kù)連接
  
• 或操作系統(tǒng)CPU、內(nèi)存資源逐漸耗盡，無(wú)法創(chuàng)建新的數(shù)據(jù)庫(kù)連接
  

3、會(huì)導(dǎo)致產(chǎn)生其他爭(zhēng)用，如bufferbusy wait, ITL contention等

TX行鎖的解決方案：

1、先行會(huì)話需要結(jié)束事務(wù)（transaction）：commit或者rollback

2、強(qiáng)制結(jié)束先行會(huì)話：kill session。

真實(shí)案例深入解析

來(lái)自雙11的真實(shí)案例：雙11早08:45，我方接到客戶反映，在當(dāng)天凌晨04:00~08:00，enq:TX - row lock contention等待嚴(yán)重。

當(dāng)用戶在投訴數(shù)據(jù)庫(kù)嚴(yán)重的行鎖問(wèn)題的時(shí)候，我們首先會(huì)想到，在發(fā)生TX行鎖時(shí)，由于資源久久得不到釋放，系統(tǒng)中會(huì)話積壓，導(dǎo)致DBtime會(huì)變得很高。

從圖上看出，在問(wèn)題發(fā)生的前兩條開(kāi)始采樣，DBtime一直處于相對(duì)較低的狀態(tài)，大概是100。在故障點(diǎn)，DBtime超過(guò)了800。

問(wèn)題初現(xiàn)：從11月11日約00:00開(kāi)始

高峰時(shí)刻：11月11日凌晨04：00達(dá)到峰值。

高峰值：該時(shí)刻DBTimes峰值為835.86，是該節(jié)點(diǎn)平日壓力的十幾倍，可見(jiàn)問(wèn)題十分嚴(yán)重。

接下來(lái)我們查看了當(dāng)時(shí)的AWR的報(bào)告。在AWR報(bào)告里面，我們首先要關(guān)注的是等待事件。我們看到其中TX行鎖占用了大部分的等待時(shí)間，因此初步推斷行鎖就是導(dǎo)致故障的原因。

那么具體的行鎖在什么地方呢？

通過(guò)top SQL查行鎖的話，可能會(huì)比較困難。推薦大家通過(guò)segment部分進(jìn)行查詢。在segment模塊，有一個(gè)專門針對(duì)行鎖的統(tǒng)計(jì)， segment by row lock waits.從這個(gè)統(tǒng)計(jì)中我們看到，有一張命名為_(kāi)manager_tp的表，占用了99%的行鎖爭(zhēng)用。

明確了爭(zhēng)用對(duì)象以后，我們?cè)賮?lái)找對(duì)應(yīng)的SQL語(yǔ)句。

在以耗時(shí)排名的top SQL 中，有一條SQL占比達(dá)到98%，這條SQL語(yǔ)句正在對(duì)_manager這張表進(jìn)行update操作。但是我們之前看到的那張爭(zhēng)用的表是_manager_tp, 跟這里查出來(lái)的manager不是同一張表。

原因是什么呢？

后經(jīng)查證，MANAGER是指向表*MANAGER_TP的同義詞。我們知道在運(yùn)營(yíng)商的環(huán)境中，他們很喜歡用同義詞指向一張表。

因此，現(xiàn)在確認(rèn)在top SQL里面涉及的對(duì)象和爭(zhēng)用的segment的對(duì)象是匹配的。

接下來(lái)我們看一下從ASH分析出來(lái)的00：00 到08:00的趨勢(shì)：

我們看到在整體的上升的趨勢(shì)中，有一些點(diǎn)會(huì)產(chǎn)生向下的波動(dòng)，向下的波動(dòng)在行鎖爭(zhēng)用中是很常見(jiàn)的一種現(xiàn)象。當(dāng)有一部分行鎖被釋放之后，被阻塞的量就會(huì)往下降。但是在持續(xù)的行鎖阻塞中，雖然會(huì)有短暫的釋放和緩解，但更多的會(huì)話會(huì)很快擁堵上來(lái)。

我們?cè)賹?duì)峰值期間的的行鎖爭(zhēng)用進(jìn)行詳細(xì)分析：

我們看到每一秒行鎖爭(zhēng)用的個(gè)數(shù)都達(dá)到了800+，只有在少數(shù)的時(shí)刻降到了幾十。

因此到這一步，我們首先明確了分析方向：

該SQL自身導(dǎo)致的行鎖，與其他無(wú)關(guān)

排除了存儲(chǔ)不穩(wěn)定可能導(dǎo)致SQL運(yùn)行緩慢的可能性

第二條是因?yàn)樵诳蛻舻臋C(jī)房環(huán)境下，經(jīng)常會(huì)由于存儲(chǔ)的不穩(wěn)定導(dǎo)致應(yīng)用SQL變慢。所以在故障發(fā)生的時(shí)候，也首先查看了操作系統(tǒng)的錯(cuò)誤日志。

當(dāng)我們確定了是某一條SQL導(dǎo)致的行鎖，接下來(lái)我們對(duì)該SQL語(yǔ)句進(jìn)行深入分析：

SQL全文如下：從SQL文本來(lái)看，對(duì)應(yīng)到的應(yīng)該是比較少的記錄。

UPDATE *MANAGE SET EXPIRE_DATE= SYSDATE WHERE ACCESS_NUM = :1 AND IDENT_CODE_LEVEL= :2 AND IDENT_CODE_TYPE= :3

其執(zhí)行計(jì)劃如下：走的是索引范圍掃。從TP_AN看到，并不是一個(gè)唯一索引。

相關(guān)的表和索引信息如下：

我們看到表有200w的記錄，access_num為40w左右。因此平均每行的訪問(wèn)為6。

然后我們把SQL的AWR報(bào)告導(dǎo)出來(lái)一看，我們可以看到平均每次要處理7w多條記錄，和6相比差別很大。這說(shuō)明數(shù)據(jù)存在嚴(yán)重的傾斜。

因此我們做了一個(gè)查詢，結(jié)果如下：

有些號(hào)碼對(duì)應(yīng)到十幾萬(wàn)條記錄，數(shù)據(jù)傾斜嚴(yán)重。

因此我們推測(cè)，發(fā)生故障是因?yàn)槎鄷?huì)話在更新相同的access_number

深度分析：數(shù)據(jù)為什么會(huì)分布不均？

經(jīng)過(guò)跟業(yè)務(wù)部溝通，發(fā)現(xiàn)：

每次用戶申請(qǐng)憑證，表內(nèi)就會(huì)記錄一條憑證信息。

如果用戶反復(fù)申請(qǐng)，表內(nèi)對(duì)同一手機(jī)號(hào)就會(huì)記錄多條信息。

問(wèn)題為何產(chǎn)生？

一次就將表內(nèi)一個(gè)手機(jī)號(hào)對(duì)應(yīng)的所有記錄的過(guò)期日期都更新為當(dāng)前日期，是不合理的做法。

后來(lái)跟開(kāi)發(fā)商進(jìn)行溝通，得到以下結(jié)論：不是代碼問(wèn)題，就是設(shè)計(jì)問(wèn)題

方案1:允許存在相同ACCESS_NUM對(duì)應(yīng)多條記錄的情況：正確的做法應(yīng)該是只更新最新的記錄，而早之前的記錄不應(yīng)該更新，因?yàn)槠湓缫堰^(guò)期（過(guò)期日期比當(dāng)前日期小）。
并將單表改為主子表關(guān)系，主表存最新的記錄，子表存歷史記錄。

方案2:1個(gè)ACCESS_NUM在該表只應(yīng)有一行記錄的情況：應(yīng)根據(jù)判斷新進(jìn)入該表的數(shù)據(jù)是否已經(jīng)存在在表內(nèi)，若是，則更新數(shù)據(jù)，若否，則插入數(shù)據(jù)。

因此開(kāi)發(fā)商給出的方案：

后續(xù)得知，該問(wèn)題不是第一次出現(xiàn)，曾經(jīng)*MANAGER就是指向表*MANAGER的同義詞！！！

我們根據(jù)前面的觀點(diǎn)判斷，在本案例當(dāng)中，平衡三要素中的“實(shí)現(xiàn)”出現(xiàn)了問(wèn)題。是由于開(kāi)發(fā)設(shè)計(jì)不合理導(dǎo)致的行鎖競(jìng)爭(zhēng)。

原文發(fā)布時(shí)間為：2017-12-1

本文作者：懷曉明

本文來(lái)自云棲社區(qū)合作伙伴“數(shù)據(jù)和云”，了解相關(guān)信息可以關(guān)注“數(shù)據(jù)和云”微信公眾號(hào)

總結(jié)

以上是生活随笔為你收集整理的拨云见日—深入解析Oracle TX 行锁（上）的全部?jī)?nèi)容，希望文章能夠幫你解決所遇到的問(wèn)題。

如果覺(jué)得生活随笔網(wǎng)站內(nèi)容還不錯(cuò)，歡迎將生活随笔推薦給好友。