Logo Questions Linux Laravel Mysql Ubuntu Git Menu
 

What is ROW EXCLUSIVE in PostgreSQL exactly?

I understand that PostgreSQL inserts use ROW EXCLUSIVE locks, does that mean that inserts can be in parallel, and that one insert won't lock up the entire table?

Table in question has a primary key generated outside of DB and no additional indexes (but I'm curious what would happen if that wasn't the case).

Edit 1:

Per documentation, ROW EXCLUSIVE conflicts with SHARE which is acquired by CREATE INDEX.

Does this mean that if the table has index, insert will lock up the entire table?

Or will the table be locked only when creating the index first time?

Also, as I understand now, primary key is also an index, right?

like image 289
omittones Avatar asked Jan 25 '13 21:01

omittones


1 Answers

Concurrent inserts should not block each other, unless they are inserting conflicting keys into a unique index, in which case the second insert will wait for the transaction containing the first to be committed or rolled back, and then either abort or proceed. A primary key is implemented as a unique index.

Non-unique indexes should not cause additional lock conflicts. Creating an index will block inserts and updates to the table, although you can just add concurrently to the command to avoid this, for some speed penalty.

like image 161
araqnid Avatar answered Oct 21 '22 04:10

araqnid