{"id":1965,"date":"2015-04-06T23:58:39","date_gmt":"2015-04-06T20:58:39","guid":{"rendered":"http:\/\/www.profelis.com.tr\/tr\/blog\/?p=1965"},"modified":"2023-02-01T10:58:13","modified_gmt":"2023-02-01T07:58:13","slug":"postgresql-101-bekleyen-sorgular","status":"publish","type":"post","link":"https:\/\/profelis.com.tr\/en\/2015\/04\/06\/postgresql-101-bekleyen-sorgular\/","title":{"rendered":"PostgreSQL 101: Bekleyen Sorgular"},"content":{"rendered":"<div title=\"Page 209\">\n<div>\n<h2>PostgreSQL 101: Bekleyen Sorgular<\/h2>\n<p><strong>pg_stat_activity<\/strong> g\u00f6r\u00fcn\u00fcm\u00fc boolean bir alan olan <strong>waiting<\/strong> s\u00fctununa sahiptir.\u00a0Bu s\u00fctunun de\u011ferleri<\/p>\n<pre> pg_ terminate_backend(procpid) from pg_stat_activity, where current_query =<\/pre>\n<p>i\u015flemin (transaction) devam etti\u011fi sorgular, ve<\/p>\n<pre>current_timestamp - query_start &gt; '1 min';<\/pre>\n<p>olarak se\u00e7ildi\u011finde bir sistem kilidinin (lock) a\u00e7\u0131lmas\u0131n\u0131 bekleyen sorgular olarak \u00e7al\u0131\u015f\u0131r. Di\u011fer bir \u015fekilde a\u00e7\u0131klamaya \u00e7al\u0131\u015f\u0131rsak; <strong>pg_stat_activity<\/strong> g\u00f6r\u00fcn\u00fcm\u00fcn\u00fc e\u011fer <strong>waiting = TRUE<\/strong> olarak filtrelersek bu durumda yukar\u0131daki ilk sorgu ile sonu\u00e7lar getirilir.<\/p>\n<h2>Sorgumu Kim Blokluyor?<\/h2>\n<p>Bir sorgunun blokland\u0131\u011f\u0131n\u0131 \u00f6\u011frendikten sonra hemen kimin bloklad\u0131\u011f\u0131n\u0131 bulmak isteriz. A\u015fa\u011f\u0131dakine benzer bir soru bu sorumuzun cevab\u0131n\u0131 bize verecektir:<\/p>\n<\/div>\n<div class=\"page\" title=\"Page 211\">\n<div class=\"layoutArea\">\n<div class=\"column\">\n<pre>SELECT\n    w.current_query as waiting_query,\n    w.procpid as w_pid,\n    w.usename as w_user,\n    l.current_query as locking_query,\n    l.procpid as l_pid,\n    l.usename as l_user,\n    t.schemaname || '.' || t.relname as tablename\nFROM pg_stat_activity w\n    join pg_locks l1 on w.procpid = l1.pid and not l1.granted\n    join pg_locks l2 on l1.relation = l2.relation and l2.granted\n    join pg_stat_activity l on  l2.pid = l.procpid\n    join pg_stat_user_tables t on l1.relation = t.relid\nWHERE w.waiting;\n\n<\/pre>\n<p>Bu sorgunun sonucu process ID ile s\u00fcre\u00e7 numaras\u0131n\u0131, kullan\u0131c\u0131, ve current query ile i\u015flenen sorguyu bloklayan ve bloklanan i\u015fle\u00e7ler ile beraber g\u00f6sterecektir.\u00a0Sorgu sonucu ayn\u0131 zamanda blo\u011fa sebep olan \u015fema ve tablo adlar\u0131n\u0131 da g\u00f6sterir.<\/p>\n<p>Kullan\u0131c\u0131 adlar\u0131 yine bir sistem g\u00f6r\u00fcn\u00fcm\u00fc olan <strong>pg_stat_user_tables<\/strong> g\u00f6r\u00fcn\u00fcm\u00fcnden bir join arac\u0131l\u0131\u011f\u0131 ile getirilmektedir.<\/p>\n<\/div>\n<\/div>\n<\/div>\n<\/div>","protected":false},"excerpt":{"rendered":"<p>pg_stat_activity g\u00f6r\u00fcn\u00fcm\u00fc boolean bir alan olan waiting s\u00fctununa sahiptir.\u00a0Bu s\u00fctunun de\u011ferleri olarak se\u00e7ildi\u011finde bir sistem kilidinin (lock) a\u00e7\u0131lmas\u0131n\u0131 bekleyen sorgular olarak \u00e7al\u0131\u015f\u0131r. Di\u011fer bir \u015fekilde a\u00e7\u0131klamaya \u00e7al\u0131\u015f\u0131rsak; pg_stat_activity g\u00f6r\u00fcn\u00fcm\u00fcn\u00fc e\u011fer waiting = TRUE olarak filtrelersek bu durumda yukar\u0131daki ilk sorgu ile sonu\u00e7lar getirilir.<\/p>","protected":false},"author":3,"featured_media":0,"comment_status":"closed","ping_status":"closed","sticky":false,"template":"","format":"standard","meta":{"footnotes":""},"categories":[75,45],"tags":[48,60],"class_list":["post-1965","post","type-post","status-publish","format-standard","hentry","category-postgresql","category-yazi","tag-enterprisedb","tag-postgresql"],"_links":{"self":[{"href":"https:\/\/profelis.com.tr\/en\/wp-json\/wp\/v2\/posts\/1965","targetHints":{"allow":["GET"]}}],"collection":[{"href":"https:\/\/profelis.com.tr\/en\/wp-json\/wp\/v2\/posts"}],"about":[{"href":"https:\/\/profelis.com.tr\/en\/wp-json\/wp\/v2\/types\/post"}],"author":[{"embeddable":true,"href":"https:\/\/profelis.com.tr\/en\/wp-json\/wp\/v2\/users\/3"}],"replies":[{"embeddable":true,"href":"https:\/\/profelis.com.tr\/en\/wp-json\/wp\/v2\/comments?post=1965"}],"version-history":[{"count":0,"href":"https:\/\/profelis.com.tr\/en\/wp-json\/wp\/v2\/posts\/1965\/revisions"}],"wp:attachment":[{"href":"https:\/\/profelis.com.tr\/en\/wp-json\/wp\/v2\/media?parent=1965"}],"wp:term":[{"taxonomy":"category","embeddable":true,"href":"https:\/\/profelis.com.tr\/en\/wp-json\/wp\/v2\/categories?post=1965"},{"taxonomy":"post_tag","embeddable":true,"href":"https:\/\/profelis.com.tr\/en\/wp-json\/wp\/v2\/tags?post=1965"}],"curies":[{"name":"wp","href":"https:\/\/api.w.org\/{rel}","templated":true}]}}