postgresql的update锁等待

--当update语句执行时，如果其可以获得锁其会首先获得一个排它锁 ExclusiveLock--在session 1 中 开启一个事务postgres=# begin;BEGINpostgres=# 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 queryFROM pg_locks LEFT OUTERJOIN pg_class    ON (pg_locks.relation = pg_class.oid) LEFT OUTERJOIN pg_namespace    ON (pg_namespace.oid = pg_class.relnamespace), pg_stat_activityWHERE NOT pg_locks.pid=pg_backend_pid()        AND pg_locks.pid=pg_stat_activity.pid        AND transactionid is NOT nullORDER 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;BEGINpostgres=# 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'