postgresql的update锁等待

--当update语句执行时，如果其可以获得锁其会首先获得一个排它锁 ExclusiveLock

--在session 1 中 开启一个事务
postgres=# begin;
BEGIN
postgres=# update t set user_name='test rudy' where id=1;

--在session 2 中进行查询
postgres=# SELECT locktype,pg_locks.pid,virtualtransaction,transactionid,nspname,relname,mode,granted,CASE
    WHEN granted='f' THEN
    'get_lock'
    WHEN granted='t' THEN
    'wait_lock'
    END lock_satus,CASE
    WHEN waiting='f' THEN
    'waiting'
    WHEN waiting='t' THEN
    'executing'
    END lock_satus,cast(date_trunc('second',query_start) AS timestamp) AS query_start,substr(query,1,25) AS query
FROM pg_locks LEFT OUTER
JOIN pg_class
    ON (pg_locks.relation = pg_class.oid) LEFT OUTER
JOIN pg_namespace
    ON (pg_namespace.oid = pg_class.relnamespace),pg_stat_activity
WHERE NOT pg_locks.pid=pg_backend_pid()
        AND pg_locks.pid=pg_stat_activity.pid
        AND transactionid is NOT null
ORDER BY  query_start;
   locktype    |  pid  | virtualtransaction | transactionid | nspname | relname |     mode      | granted | lock_satus | lock_satus |     query_start     |           query           
---------------+-------+--------------------+---------------+---------+---------+---------------+---------+------------+------------+---------------------+---------------------------
 transactionid | 17105 | 5/222755           |       4637392 |         |         | ExclusiveLock | t       | wait_lock  | waiting    | 2015-10-19 01:34:44 | update t set user_name='t'
 
 
 
 --如果有另外一个事务也对同一条记录进行更新，其会等待上一个事务结束，它可以先获得一个共享锁，等待上一个事务结束后再获得排它锁
 
 --在session 3 中也进行更新,故在一个多个等待的事务中可以通过ShareLock获得下个将获得的进程是哪一个
 postgres=# begin;
BEGIN
postgres=# update t set user_name='test' where id=1;


  locktype    |  pid  | virtualtransaction | transactionid | nspname | relname |     mode      | granted | lock_satus | lock_satus |     query_start     |           query           
---------------+-------+--------------------+---------------+---------+---------+---------------+---------+------------+------------+---------------------+---------------------------
 transactionid | 17101 | 4/253882           |       4637392 |         |         | ShareLock     | f       | get_lock   | executing  | 2015-10-19 01:43:23 | update t set user_name='t'
 transactionid | 17101 | 4/253882           |       4637393 |         |         | ExclusiveLock | t       | wait_lock  | executing  | 2015-10-19 01:43:23 | update t set user_name='t'
 transactionid | 17105 | 5/222755           |       4637392 |         |         | ExclusiveLock | t       | wait_lock  | waiting    | 2015-10-19 01:43:34 | update t set user_name='t'

（编辑：李大同）

【声明】本站内容均来自网络，其相关言论仅代表作者个人观点，不代表本站立场。若无意侵犯到您的权利，请及时与联系站长删除相关内容!