成人国产在线小视频_日韩寡妇人妻调教在线播放_色成人www永久在线观看_2018国产精品久久_亚洲欧美高清在线30p_亚洲少妇综合一区_黄色在线播放国产_亚洲另类技巧小说校园_国产主播xx日韩_a级毛片在线免费

資訊專欄INFORMATION COLUMN

PostgreSQL之珍藏級SQL

IT那活兒 / 3257人閱讀
PostgreSQL之珍藏級SQL
點(diǎn)擊上方藍(lán)字關(guān)注我們


背景


在數(shù)據(jù)庫中，通過鎖以及多版本并發(fā)控制可以保護(hù)數(shù)據(jù)的一致性，例如A正在查詢數(shù)據(jù)，B就無法對A訪問的對象執(zhí)行DDL。A正在更新某條記錄，B就不能刪除或更新這條記錄。

鎖是數(shù)據(jù)庫自動管理的，同時數(shù)據(jù)庫還提供了ADLOCK或者LOCK語法，允許用戶自己控制鎖。

當(dāng)然，如果應(yīng)用程序邏輯設(shè)計不慎，就可能導(dǎo)致嚴(yán)重的鎖等待，或者死鎖的產(chǎn)生。

如果你發(fā)現(xiàn)SQL請求大多數(shù)時候處于等待鎖的狀態(tài)，那么可能出現(xiàn)了業(yè)務(wù)邏輯的問題。


如何檢查或監(jiān)控鎖等待呢？


1.pg_locks展示鎖信息，每一個被鎖或者等待鎖的對象一條記錄。

2.pg_stat_activity，每個會話一條記錄，顯示會話狀態(tài)信息。

我們通過這兩個視圖可以查看鎖，鎖等待情況。同時可以了解發(fā)生鎖沖突的情況。

pg_stat_activity.query反映的是當(dāng)前正在執(zhí)行或請求的SQL，而同一個事務(wù)中以前已經(jīng)執(zhí)行的SQL不能在pg_stat_activity中顯示出來。所以如果你發(fā)現(xiàn)兩個會話發(fā)生了沖突，但是他們的pg_stat_activity.query沒有沖突的話，那就有可能是他們之間的某個事務(wù)之前的SQL獲取的鎖與另一個事務(wù)當(dāng)前請求的QUERY發(fā)生了鎖沖突。


追蹤詳細(xì)的鎖沖突信息：


1.可以通過locktrace跟蹤鎖等待的詳細(xì)信息，

2.通過數(shù)據(jù)庫日志（開啟lock_timeout,log_lockwait參數(shù)）(csvlog)跟蹤鎖等待信息，

3.或者通過數(shù)據(jù)庫日志（開啟log_statements=all，SQL審計）追蹤事務(wù)中所有的SQL(csvlog)，分析事務(wù)之間的鎖沖突。

4.通過SQL查看持鎖，等鎖的事務(wù)狀態(tài)。


鎖的釋放時機(jī)：


大多數(shù)鎖要等待事務(wù)結(jié)束后釋放，某些輕量級鎖（數(shù)據(jù)庫自動控制）是隨用隨釋放的。

查看當(dāng)前事務(wù)鎖等待、持鎖信息的SQL

這條SQL非常有用，建議DBA珍藏。

with    

t_wait as    

(    

  select a.mode,a.locktype,a.database,a.relation,a.page,a.tuple,a.classid,a.granted,   

  a.objid,a.objsubid,a.pid,a.virtualtransaction,a.virtualxid,a.transactionid,a.fastpath,    

  b.state,b.query,b.xact_start,b.query_start,b.usename,b.datname,b.client_addr,b.client_port,b.application_name   

    from pg_locks a,pg_stat_activity b where a.pid=b.pid and not a.granted   

),   

t_run as   

(   

  select a.mode,a.locktype,a.database,a.relation,a.page,a.tuple,a.classid,a.granted,   

  a.objid,a.objsubid,a.pid,a.virtualtransaction,a.virtualxid,a.transactionid,a.fastpath,   

  b.state,b.query,b.xact_start,b.query_start,b.usename,b.datname,b.client_addr,b.client_port,b.application_name   

    from pg_locks a,pg_stat_activity b where a.pid=b.pid and a.granted   

),   

t_overlap as   

(   

  select r.* from t_wait w join t_run r on   

  (   

    r.locktype is not distinct from w.locktype and   

    r.database is not distinct from w.database and   

    r.relation is not distinct from w.relation and   

    r.page is not distinct from w.page and   

    r.tuple is not distinct from w.tuple and   

    r.virtualxid is not distinct from w.virtualxid and   

    r.transactionid is not distinct from w.transactionid and   

    r.classid is not distinct from w.classid and   

    r.objid is not distinct from w.objid and   

    r.objsubid is not distinct from w.objsubid and   

    r.pid <> w.pid   

  )    

),    

t_unionall as    

(    

  select r.* from t_overlap r    

  union all    

  select w.* from t_wait w    

)    

select locktype,datname,relation::regclass,page,tuple,virtualxid,transactionid::text,classid::regclass,objid,objsubid,   

string_agg(   

Pid: ||case when pid is null then NULL else pid::text end||chr(10)||   

Lock_Granted: ||case when granted is null then NULL else granted::text end|| , Mode: ||case when mode is null then NULL else mode::text end|| , FastPath: ||case when fastpath is null then NULL else fastpath::text end|| , VirtualTransaction: ||case when virtualtransaction is null then NULL else virtualtransaction::text end|| , Session_State: ||case when state is null then NULL else state::text end||chr(10)||   

Username: ||case when usename is null then NULL else usename::text end|| , Database: ||case when datname is null then NULL else datname::text end|| , Client_Addr: ||case when client_addr is null then NULL else client_addr::text end|| , Client_Port: ||case when client_port is null then NULL else client_port::text end|| , Application_Name: ||case when application_name is null then NULL else application_name::text end||chr(10)||    

Xact_Start: ||case when xact_start is null then NULL else xact_start::text end|| , Query_Start: ||case when query_start is null then NULL else query_start::text end|| , Xact_Elapse: ||case when (now()-xact_start) is null then NULL else (now()-xact_start)::text end|| , Query_Elapse: ||case when (now()-query_start) is null then NULL else (now()-query_start)::text end||chr(10)||    

SQL (Current SQL in Transaction): ||chr(10)||  

case when query is null then NULL else query::text end,    

chr(10)||--------||chr(10)    

order by    

  (  case mode    

    when INVALID then 0   

    when AccessShareLock then 1   

    when RowShareLock then 2   

    when RowExclusiveLock then 3   

    when ShareUpdateExclusiveLock then 4   

    when ShareLock then 5   

    when ShareRowExclusiveLock then 6   

    when ExclusiveLock then 7   

    when AccessExclusiveLock then 8   

    else 0   

  end  ) desc,   

  (case when granted then 0 else 1 end)  

) as lock_conflict  

from t_unionall   

group by   

locktype,datname,relation,page,tuple,virtualxid,transactionid::text,classid,objid,objsubid ;  


如果覺得寫SQL麻煩，可以將它創(chuàng)建為視圖


create view v_locks_monitor as   

with    

t_wait as    

(    

  select a.mode,a.locktype,a.database,a.relation,a.page,a.tuple,a.classid,a.granted,   

  a.objid,a.objsubid,a.pid,a.virtualtransaction,a.virtualxid,a.transactionid,a.fastpath,    

  b.state,b.query,b.xact_start,b.query_start,b.usename,b.datname,b.client_addr,b.client_port,b.application_name   

    from pg_locks a,pg_stat_activity b where a.pid=b.pid and not a.granted   

),   

t_run as   

(   

  select a.mode,a.locktype,a.database,a.relation,a.page,a.tuple,a.classid,a.granted,   

  a.objid,a.objsubid,a.pid,a.virtualtransaction,a.virtualxid,a.transactionid,a.fastpath,   

  b.state,b.query,b.xact_start,b.query_start,b.usename,b.datname,b.client_addr,b.client_port,b.application_name   

    from pg_locks a,pg_stat_activity b where a.pid=b.pid and a.granted   

),   

t_overlap as   

(   

  select r.* from t_wait w join t_run r on   

  (   

    r.locktype is not distinct from w.locktype and   

    r.database is not distinct from w.database and   

    r.relation is not distinct from w.relation and   

    r.page is not distinct from w.page and   

    r.tuple is not distinct from w.tuple and   

    r.virtualxid is not distinct from w.virtualxid and   

    r.transactionid is not distinct from w.transactionid and   

    r.classid is not distinct from w.classid and   

    r.objid is not distinct from w.objid and   

    r.objsubid is not distinct from w.objsubid and   

    r.pid <> w.pid   

  )    

),    

t_unionall as    

(    

  select r.* from t_overlap r    

  union all    

  select w.* from t_wait w    

)    

select locktype,datname,relation::regclass,page,tuple,virtualxid,transactionid::text,classid::regclass,objid,objsubid,   

string_agg(   

Pid: ||case when pid is null then NULL else pid::text end||chr(10)||   

Lock_Granted: ||case when granted is null then NULL else granted::text end|| , Mode: ||case when mode is null then NULL else mode::text end|| , FastPath: ||case when fastpath is null then NULL else fastpath::text end|| , VirtualTransaction: ||case when virtualtransaction is null then NULL else virtualtransaction::text end|| , Session_State: ||case when state is null then NULL else state::text end||chr(10)||   

Username: ||case when usename is null then NULL else usename::text end|| , Database: ||case when datname is null then NULL else datname::text end|| , Client_Addr: ||case when client_addr is null then NULL else client_addr::text end|| , Client_Port: ||case when client_port is null then NULL else client_port::text end|| , Application_Name: ||case when application_name is null then NULL else application_name::text end||chr(10)||    

Xact_Start: ||case when xact_start is null then NULL else xact_start::text end|| , Query_Start: ||case when query_start is null then NULL else query_start::text end|| , Xact_Elapse: ||case when (now()-xact_start) is null then NULL else (now()-xact_start)::text end|| , Query_Elapse: ||case when (now()-query_start) is null then NULL else (now()-query_start)::text end||chr(10)||    

SQL (Current SQL in Transaction): ||chr(10)||  

case when query is null then NULL else query::text end,    

chr(10)||--------||chr(10)    

order by    

  (  case mode    

    when INVALID then 0   

    when AccessShareLock then 1   

    when RowShareLock then 2   

    when RowExclusiveLock then 3   

    when ShareUpdateExclusiveLock then 4   

    when ShareLock then 5   

    when ShareRowExclusiveLock then 6   

    when ExclusiveLock then 7   

    when AccessExclusiveLock then 8   

    else 0   

  end  ) desc,   

  (case when granted then 0 else 1 end)  

) as lock_conflict  

from t_unionall   

group by   

locktype,datname,relation,page,tuple,virtualxid,transactionid::text,classid,objid,objsubid ;



ksl=> create table locktest(id int primary key, info text);  

CREATE TABLE

ksl=> insert into locktest values (1,a);

INSERT 0 1


會話A

ksl=> begin;

BEGIN

ksl=> update locktest set info=a where id=1;

UPDATE 1

ksl=> select * from locktest ;

 id | info

----+------

  1 | a

(1 row)


ksl=>


會話B

ksl=> begin;

BEGIN

ksl=> select * from locktest ;

 id | info

----+------

  1 | a

(1 row)


ksl=>


會話C

ksl=> begin;

BEGIN

ksl=> insert into locktest values (2,test);

INSERT 0 1

ksl=>


會話D

一直處于等待狀態(tài)


會話E

也一直處于等待狀態(tài)




處理方法

前面的鎖查詢SQL，已經(jīng)清晰的顯示了每一個發(fā)生了鎖等待的對象，按鎖的大小排序，要快速解出這種狀態(tài)，terminate最大的鎖對應(yīng)的PID即可。

ksl=> select pg_terminate_backend(43600);

-[ RECORD 1 ]--------+--

pg_terminate_backend | t


ksl=>

      會話D

sl=> begin;

BEGIN

ksl=> truncate locktest ;

FATAL:  terminating connection due to administrator command

server closed the connection unexpectedly

        This probably means the server terminated abnormally

        before or while processing the request.

The connection to the server was lost. Attempting reset: Succeeded.

ksl=>

    干掉43600后，大家都清凈了


    再查詢該表數(shù)據(jù)


ksl=> select * from v_locks_monitor ;

(0 rows)


ksl=>



END




文章版權(quán)歸作者所有，未經(jīng)允許請勿轉(zhuǎn)載,若此文章存在違規(guī)行為，您可以聯(lián)系管理員刪除。

轉(zhuǎn)載請注明本文地址：http://systransis.cn/yun/130000.html

相關(guān)文章

  • PgSQL · 應(yīng)用案例 · 阿里云 RDS PostgreSQL 高并發(fā)特性 vs 社區(qū)版本

    摘要：阿里云，采用與模式類似的方案，解決了進(jìn)程模式在高并發(fā)的情況下性能下降的問題。具體測試結(jié)果分析阿里云在高并發(fā)下，相比社區(qū)版本好很多，更加平穩(wěn)。阿里云引入了機(jī)制后，響應(yīng)延遲，抖動相比社區(qū)版本低了很多。 摘要： 背景 進(jìn)程模型數(shù)據(jù)庫，需要為每個會話指派獨(dú)立的進(jìn)程與之服務(wù)，在連接數(shù)非常多，且大都是活躍連接時，進(jìn)程調(diào)度浪費(fèi)或引入的開銷甚至遠(yuǎn)遠(yuǎn)大于實(shí)際任務(wù)需要的開銷（例如上下文切換，MEMCPY等...

    ThinkSNS 評論0 收藏0

  • IntelliJ IDEA 18 周歲，吐血推進(jìn)珍藏已久的必裝插件

    摘要：代碼規(guī)約掃描插件以今年年初發(fā)布的阿里巴巴開發(fā)規(guī)約為標(biāo)準(zhǔn)，作為的插件形式存在，檢測代碼中存在不規(guī)范得位置然后給予提示。 IntelliJ IDEA是目前最好最強(qiáng)最智能的Java IDE，前幾天，他剛剛年滿18歲。 showImg(https://segmentfault.com/img/remote/1460000017974611);? 本文，給大家推薦幾款我私藏已久的，自己經(jīng)常使用的...

    趙春朋 評論0 收藏0

  • 大佬為你揭秘微信支付的系統(tǒng)架構(gòu)，你想知道的都在這里了

    摘要：年之前，微信支付業(yè)務(wù)快速發(fā)展，需要一款數(shù)據(jù)庫能夠安全高效的支撐微信支付商戶系統(tǒng)核心業(yè)務(wù)，這個重任落在了騰訊數(shù)據(jù)庫團(tuán)隊自研上。由于是用于微信支付的核心數(shù)據(jù)庫，騰訊被定位為安全高效，穩(wěn)定，可靠的數(shù)據(jù)庫集群。 歡迎大家前往騰訊云+社區(qū)，獲取更多騰訊海量技術(shù)實(shí)踐干貨哦~ 本文由李躍森發(fā)表于云+社區(qū)專欄李躍森，騰訊云PostgreSQL首席架構(gòu)師，騰訊數(shù)據(jù)庫團(tuán)隊架構(gòu)師，負(fù)責(zé)微信支付商戶系統(tǒng)核心數(shù)...

    Terry_Tai 評論0 收藏0

發(fā)表評論

0條評論

IT那活兒

男|高級講師

TA的文章

閱讀更多
最新活動
閱讀需要支付1元查看
<