Insert inside transaction causing Exclusive lock

1
votes

I am doing a simple insert row inside of a transaction, here is the steps that I followed

  • begin;
  • insert data
  • query pg_locks

The pg_locks now says that there is an Exclusive lock as in the picture below.

According to the documentation an Exclusive Lock is only Acquired by refresh materialized view concurrently.

What am I doing wrong here?

Using pgsql - 9.4

Pg Locks table before insert has four rows, this is due to querying the pg_locks table itself in two separate sessions.

After insert it has two extra rows, one which is the actual transaction id itself which is an exclusive lock and another is on relation RowExclusiveLock

1
databasepostgresqltransactions
Have you looked at what all of those columns mean? postgresql.org/docs/9.2/static/view-pg-locks.html Hint: What is it that an exclusive lock is being held on here? - David Aldridge
I understand that it is being held for only one row, but then what is the difference between ExclusiveLock and a RowExclusiveLock in this particular case. - Greedy Coder
As far as I can tell the ExclusiveLock is not held for a relation (and please post sample data like that as formatted text, not as a screenshot) - a_horse_with_no_name
The ExclusiveLocks are being held for different types of object. - David Aldridge

1 Answers

2
votes

Those are locks on the transaction IDs. That's totally normal.

The purpose is that another transaction can try to acquire the lock on this transaction, causing it to wait until the first transaction commits or rolls back (and thus releases the lock on its self) before continuing.

You only need to worry about relation locks.