PostgreSQL锁定模式 [英] PostgreSQL locking mode
问题描述
来自此文档 http://www.postgresql.org/docs /current/static/explicit-locking.html
我知道PostgreSQL提供了多种锁定模式来控制对表中数据的并发访问.
I knew PostgreSQL provides various lock modes to control concurrent access to data in tables.
我的问题是我有很多会话将访问我的数据库，但是我应该使1个大表包含40列，还是使许多表具有较少的列(一对一关系)，使我感到困惑.
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).
-
因为当我选择数据时，我将全部选择它--->使用INNER JOIN从许多表中进行选择需要花费更多的时间，但是从1个大表中进行选择所花费的时间却更少.因此，如果我使用许多表，PHP的响应速度会变慢.
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.
但是当我仅使用一个表时，许多会话将更新该表中的数据，我担心死锁或延迟，因为命令UPDATE，DELETE和INSERT在目标表上获取ROW EXCLUSIVE锁定模式.通常，任何修改表中数据的命令都将获取此锁定模式.
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?
推荐答案
确实，INSERT，UPDATE或DELETE必须获取要更新的表上的ROW EXCLUSIVE锁.
It is true that INSERT, UPDATE or DELETE must acquire ROW EXCLUSIVE lock on table to be updated.
但是，此锁定不会阻止SELECT正常工作. SELECT仅需要ACCESS SHARE锁定.此锁与ROW EXCLUSIVE兼容-换句话说，只要不获取任何显式锁，您就可以完美地执行SELECT，而其他数据由INSERT，UPDATE或DELETE更新.
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.
换句话说，您永远都不会使用第二种方法看到任何死锁(只是不要使用SELECT FOR UPDATE，您会没事的.)
In other words, you should never see any deadlocks using second approach (just don't use SELECT FOR UPDATE and you'll be fine).
在 PostgreSQL文档中了解更多信息.
这篇关于PostgreSQL锁定模式的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！
