PostgreSQL locking mode

Question

From this doc http://www.postgresql.org/docs/current/static/explicit-locking.html

I knew PostgreSQL provides various lock modes to control concurrent access to data in tables.

My problem is I have many sessions will accessing my DB , but I'm confuse should I made 1 big table with 40 column or many tables with fewer column (one to one relationship).

Because when I select the data I will select all of it ---> it takes more time when I select from many tables using INNER JOIN, but it takes less time to select from 1 big table. So it will my php respond slower if I'm using many tables.
But when I use just one table meanwhile many session will update my data in the table, I'm afraid of deadlocks or delay because commands UPDATE, DELETE, and INSERT acquire ROW EXCLUSIVE lock mode on the target table. In general, this lock mode will be acquired by any command that modifies data in a table.

Could anyone suggested which is the best approach should I made? One big table or many tables?

Your question for one big table or many tables is impossible to answer without knowing what you are storing in it. Standard advice is to normalize your schema bkent.net/Doc/simple5.htm — Eelke
– Eelke, Commented Apr 9, 2013 at 5:41
I stored device information for example id, name, public ip, private ip, gateway, mask, external port, internal port, etc.. I'm confused should I made into one big table with many column or split it into many tables, for exp: dev_info, dev_ip, dev_port — user430926
– user430926, Commented Apr 9, 2013 at 6:04
To solve your "slow query", please read this wiki.postgresql.org/wiki/SlowQueryQuestions and update your question accordingly. — user330315
– user330315, Commented Apr 9, 2013 at 7:13

mvp · Accepted Answer · 2013-04-09 05:35:49Z

9

It is true that INSERT, UPDATE or DELETE must acquire ROW EXCLUSIVE lock on table to be updated.

However, this lock does not prevent SELECT from working normally. SELECT only requires ACCESS SHARE lock. This lock is compatible with ROW EXCLUSIVE - in other words, you can perfectly execute SELECT while other data is updated by INSERT, UPDATE or DELETE, as long as you don't acquire any explicit locks.

In other words, you should never see any deadlocks using second approach (just don't use SELECT FOR UPDATE and you'll be fine).

6 Comments

user430926 Over a year ago

What if the first approach UPDATE A column, second approach also UPDATE other B column, will it wait until the first UPDATE transaction finish and then UPDATE the second one or will it be conflict?

mvp Over a year ago

You should read more about MVCC. TLDR: second UPDATE may wait for the first one if exact same row was updated. In this case normalization should help.

user430926 Over a year ago

So ROW EXCLUSIVE lock on table to be updated but only lock the row I'm going to updated right? Not the whole table? 1st UPDATE : UPDATE mytable SET ... WHERE key = 1;

mvp Over a year ago

That is correct - UPDATE will lock only if trying to update exact same unique key as another one is still updating. Otherwise, they will run without slowing down.

user430926 Over a year ago

So lock on table just to prevent if other transaction wants to drop columns that I want to updated. This means that ROW EXCLUSIVE can be held by multiple transactions, right?

|

Collectives™ on Stack Overflow

PostgreSQL locking mode

1 Answer 1

6 Comments

Your Answer

Linked

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

6 Comments

Your Answer

Sign up or log in

Post as a guest

Linked

Related