In Table 13.2. Conflicting Lock Modes Here we see that ROW EXCLUSIVE conflicts with the SHARE, SHARE ROW EXCLUSIVE, EXCLUSIVE, and ACCESS EXCLUSIVE lock modes, but it doesn't conflict with itself. Consider following example. I start 2 transactions T1 and T2 simultaneously.
Then in T1 I execute following commands:
my_db=# begin;
BEGIN
my_db=*# update t set color = 'green' where id = 2;
UPDATE 1
Then in T2 I execute following commands:
my_db=# begin;
BEGIN
my_db=*# update t set color = 'blue' where id = 2;
T2 is become locked after that.
Then I select all locks for relation t:
my_db=# select lock.locktype,lock.mode,lock.transactionid,lock.relation::regclass as relation,lock.virtualxid from pg_locks as lock where locktype = 'relation';
locktype | mode | transactionid | relation | virtualxid
----------+------------------+---------------+-------------+------------
relation | RowExclusiveLock | | t_color_idx |
relation | RowExclusiveLock | | t |
relation | RowExclusiveLock | | t_color_idx |
relation | RowExclusiveLock | | t |
relation | AccessShareLock | | pg_locks |
(5 rows)
my_db=#
I see there are only locks of RowExclusiveLock and AccessShareLock modes. But T2 is locked. But according Table 13.2. Conflicting Lock Modes RowExclusiveLock doesn't conflict with itself.
Ok, RowExclusiveLock conflicts with ACCESS EXCLUSIVE. Consider new example: I start 2 new transactions T1 and T2 simultaneously. In T1:
my_db=# begin;
BEGIN
my_db=*#
my_db=*# alter table t alter column color TYPE varchar(400);
ALTER TABLE
my_db=*#
In T2:
my_db=# begin;
BEGIN
my_db=*# update t set color = 'black' where id = 2;
T2 is become locked after that like first example.
Then I select all locks for relation t:
my_db=# select lock.locktype,lock.mode,lock.transactionid,lock.relation::regclass as relation,lock.virtualxid from pg_locks as lock where locktype = 'relation';
locktype | mode | transactionid | relation | virtualxid
----------+---------------------+---------------+-------------+------------
relation | AccessShareLock | | pg_locks |
relation | RowExclusiveLock | | t |
relation | AccessShareLock | | t_color_idx |
relation | AccessExclusiveLock | | t_color_idx |
relation | AccessExclusiveLock | | 3028689 |
relation | ShareLock | | t |
relation | AccessExclusiveLock | | t |
(7 rows)
my_db=#
I see there is also AccessExclusiveLock. According Table 13.2. Conflicting Lock Modes RowExclusiveLock conflicts with AccessExclusiveLock. But behaviour in second example is the same in first: T2 is become locked.
So,
I expected that conflicts means locks.
If two lock modes conflict, two different transactions cannot take them at the same time on the same object. The first to come takes the lock, and the second one gets blocked until the first one releases the lock (commits).
SHARE
lock is a lock that prevents all concurrent data modifications on the table and is taken by CREATE INDEX
. ROW EXCLUSIVE
is the lock taken by transactions that want to perform INSERT
, UPDATE
or DELETE
on a table. It conflicts with the SHARE
lock for obvious reasons, and it does not conflict with itself because two concurrent transactions are allowed to modify the data in the same table concurrently (as long as they don't try to modify the same rows, which is handled by row locks).