IT/DataBase

[PostGreSQL] ํ˜„์žฌ ์‹คํ–‰์ค‘์ธ SQL ์กฐํšŒ, ์ข…๋ฃŒ

์•Œ ์ˆ˜ ์—†๋Š” ์‚ฌ์šฉ์ž 2022. 11. 28.

๋ชฉ์ฐจ

    image source:https://namu.wiki/w/PostgreSQL

     

    PostGreSQL ํ˜„์žฌ ์‹คํ–‰์ค‘์ธ SQL ์กฐํšŒ, ์ข…๋ฃŒ

    ํ˜„์žฌ ์‹คํ–‰์ค‘์ธ SQL ์กฐํšŒ ์ฟผ๋ฆฌ๐Ÿ˜Š

    select  * 
    from pg_stat_activity
    ;

    ์ปฌ๋Ÿผ ์ •๋ณด

    image source:https://kwomy.tistory.com/80

     

    lock ๊ฑธ๋ฆฐ ํ…Œ์ด๋ธ” ์กฐํšŒ๐Ÿ™„

    SELECT t.relname, 
    	   l.locktype, 
           page, 
           virtualtransaction, 
           pid, 
           mode, 
           granted 
    FROM pg_locks l, pg_stat_all_tables t 
    WHERE l.relation = t.relid ORDER BY relation asc
    ;

    ํ˜„์žฌ ํ…Œ์ด๋ธ”์— lock์„ ํ™•์ธ ํ•  ์ˆ˜ ์žˆ๋‹ค. ๊ธฐ๋ณธ์ ์œผ๋กœ ๋Œ€๋ถ€๋ถ„์˜ lock ํ…Œ์ด๋ธ”์€ ๊ดœ์ฐฎ์œผ๋‚˜

    RowExclusiveLock ์ด ๊ฒ€์ƒ‰๋œ๋‹ค๋ฉด ํ•ด๋‹น ํ…Œ์ด๋ธ”์— ์ ‘๊ทผ์ด ์ง€์—ฐ๋˜์–ด ๋‹ค๋ฅธ ์ฟผ๋ฆฌ์—๋„ ์˜ํ–ฅ์„ ๋ฏธ์น  ์ˆ˜ ์žˆ์œผ๋ฏ€๋กœ ๋ฐ˜๋“œ์‹œ ์žก๊ณ ์žˆ๋Š” ํŠธ๋žœ์žญ์…˜์ด๋‚˜ ์„œ๋ฒ„ ์ƒํƒœ ๋“ฑ์„ ์ ๊ฒ€ํ•˜์—ฌ lock์„ ํ•ด์ œ ํ•ด ์ฃผ๋Š” ์ž‘์—…์ด ํ•„์š”ํ•˜๋‹ค.

     

    ํ•ด๋‹น ์ž‘์—… ์ข…๋ฃŒ๐Ÿ˜Ž

    pg_cancel_backend(int pid)

    SELECT pg_cancel_backend(int pid);
    -- ex pg_stat_activity์—์„œ ์กฐํšŒํ•œ pid๊ฐ’์„ ๋งค๊ฐœ๋ณ€์ˆ˜ ๊ฐ’์œผ๋กœ ๋„ฃ์–ด์คŒ
    SELECT pg_cancel_backend(20020);

    return ๊ฐ’ : true or false

    pg_cancel_backend๋Š” ํ•ด๋‹น pid๋งŒ ์ค‘์ง€์‹œํ‚ด, ์ด๋ ‡๊ฒŒ ํ•ด์„œ ์ข…๋ฃŒ๊ฐ€ ์•ˆ๋  ๊ฒฝ์šฐ๋„ ์žˆ์Œ

    ์ด ๊ฒฝ์šฐ pg_terminate_backen๋กœ ์ค‘์ง€์‹œํ‚จ ํ›„ ๋‹ค์‹œ pg_cancel_backend()๋ฅผ ๋‹ค์‹œ ์‹คํ–‰

     

    pg_terminate_backen(int pid)๐Ÿ™‚

    select pg_terminate_backen(int pid);
    -- ex) pg_stat_activity์—์„œ ์กฐํšŒํ•œ pid๊ฐ’์„ ๋งค๊ฐœ๋ณ€์ˆ˜ ๊ฐ’์œผ๋กœ ๋„ฃ์–ด์คŒ
    select pg_terminate_backen(20020);

    pg_terminate_backend๋Š” ํ•ด๋‹น PID์™€ ์—ฐ๊ณ„๋œ ๋ชจ๋“  ์ƒ์œ„ ์ฟผ๋ฆฌ ํ”„๋กœ์„ธ์Šค๋ฅผ ์ข…๋ฃŒ์‹œํ‚จ๋‹ค.

    return ๊ฐ’ : true or false

     

    ์ฐธ์กฐ : Kwomy's DB World๋‹˜ ๋ธ”๋กœ๊ทธ, 2021.06.30, https://kwomy.tistory.com/80

     

    [PostgreSQL] ํ…Œ์ด๋ธ” lock ์กฐํšŒ ๋ฐ killํ•˜๊ธฐ

    ํ˜„์žฌ ์ˆ˜ํ–‰์ค‘์ธ SQL ์ „์ฒด ์กฐํšŒ select datname, pid, usename, application_name, client_addr, client_port, backend_start, query_start, wait_event_type, state, backend_xmin query from pg_stat_activity; ์œ„..

    kwomy.tistory.com

    ๋Œ“๊ธ€