国产xxxx99真实实拍_久久不雅视频_高清韩国a级特黄毛片_嗯老师别我我受不了了小说

資訊專欄INFORMATION COLUMN

PostgreSQL之珍藏級SQL

IT那活兒 / 3246人閱讀
PostgreSQL之珍藏級SQL
點擊上方藍字關注我們


背景


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

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

當然，如果應用程序邏輯設計不慎，就可能導致嚴重的鎖等待，或者死鎖的產生。

如果你發現SQL請求大多數時候處于等待鎖的狀態，那么可能出現了業務邏輯的問題。


如何檢查或監控鎖等待呢？


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

2.pg_stat_activity，每個會話一條記錄，顯示會話狀態信息。

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

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


追蹤詳細的鎖沖突信息：


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

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

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

4.通過SQL查看持鎖，等鎖的事務狀態。


鎖的釋放時機：


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

查看當前事務鎖等待、持鎖信息的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麻煩，可以將它創建為視圖


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

一直處于等待狀態


會話E

也一直處于等待狀態




處理方法

前面的鎖查詢SQL，已經清晰的顯示了每一個發生了鎖等待的對象，按鎖的大小排序，要快速解出這種狀態，terminate最大的鎖對應的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后，大家都清凈了


    再查詢該表數據


ksl=> select * from v_locks_monitor ;

(0 rows)


ksl=>



END




文章版權歸作者所有，未經允許請勿轉載,若此文章存在違規行為，您可以聯系管理員刪除。

轉載請注明本文地址：http://specialneedsforspecialkids.com/yun/130000.html

相關文章

  • PgSQL · 應用案例 · 阿里云 RDS PostgreSQL 高并發特性 vs 社區版本

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

    ThinkSNS 評論0 收藏0

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

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

    趙春朋 評論0 收藏0

  • 大佬為你揭秘微信支付的系統架構，你想知道的都在這里了

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

    Terry_Tai 評論0 收藏0

發表評論

0條評論

IT那活兒

男|高級講師

TA的文章

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