3

I have table named 'car'

              Table "db145004db.car"
  Column  |  Type  | Collation | Nullable | Default 
----------+--------+-----------+----------+---------
 car_no   | bigint |           | not null | 
 car_name | text   |           |          | 
Indexes:
    "car_pkey" PRIMARY KEY, btree (car_no)

Table Content:

 car_no |  car_name  
--------+------------
      1 | first_car
      2 | second_car
      3 | third_car

I ran this following two transaction,

Transaction 1:

begin ;
select * from car where car_no=2 for update; 

Transaction 2:

begin ;
select * from car where car_no=2 for update; (waiting for first transaction to release lock)

My 'pg_locks' table looks like:

  locktype    | database | relation | page | tuple | virtualxid | transactionid | classid | objid | objsubid | virtualtransaction |  pid  |        mode         | granted | fastpath 
---------------+----------+----------+------+-------+------------+---------------+---------+-------+----------+--------------------+-------+---------------------+---------+----------
 relation      |   103777 |   143022 |      |       |            |               |         |       |          | 4/75               | 11043 | AccessShareLock     | t       | t
 relation      |   103777 |   143016 |      |       |            |               |         |       |          | 4/75               | 11043 | RowShareLock        | t       | t
 virtualxid    |          |          |      |       | 4/75       |               |         |       |          | 4/75               | 11043 | ExclusiveLock       | t       | t
 relation      |   103777 |   143022 |      |       |            |               |         |       |          | 5/144              | 11150 | AccessShareLock     | t       | t
 relation      |   103777 |   143016 |      |       |            |               |         |       |          | 5/144              | 11150 | RowShareLock        | t       | t
 virtualxid    |          |          |      |       | 5/144      |               |         |       |          | 5/144              | 11150 | ExclusiveLock       | t       | t
 relation      |   103777 |    11577 |      |       |            |               |         |       |          | 3/141              | 11263 | AccessShareLock     | t       | t
 virtualxid    |          |          |      |       | 3/141      |               |         |       |          | 3/141              | 11263 | ExclusiveLock       | t       | t
 transactionid |          |          |      |       |            |         10204 |         |       |          | 5/144              | 11150 | ShareLock           | f       | f
 tuple         |   103777 |   143016 |    0 |     2 |            |               |         |       |          | 5/144              | 11150 | AccessExclusiveLock | t       | f
 transactionid |          |          |      |       |            |         10204 |         |       |          | 4/75               | 11043 | ExclusiveLock       | t       | f

I can't see any locks related to rows/tuples (car_no=2). Also, locktype and mode are little confusing(I can't understand what they tend to convey). As a newbie to psql, I am totally confused. Can anyone help interpreting 'pg_locks', with respect to row-level locking, with respect to above scenario.

3

I came across the postgres docs regarding locking , which states that

Although tuples are a lockable type of object, information about row-level locks is stored on disk, not in memory, and therefore row-level locks normally do not appear in this view. If a transaction is waiting for a row-level lock, it will usually appear in the view as waiting for the permanent transaction ID of the current holder of that row lock.

I got my answer.

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.