我锁定的比我需要的多吗？ [英] Am I locking more than I need to?
问题描述
现在性能不是问题，但这个问题让我很好奇：
假设我有一个购物车系统，其中有一个产品。
表包含所有可能的产品，以及cart_items表。 table
存储每个购物车中每种产品的数量。
显而易见的（或者我想到的第一件事）看起来很棒/>
这样的东西：
创建表产品（
id串行主键，
...
）;
创建表cart_items（
id串行主键，
cart_id int references ...，
prod_id int引用产品（id），
数量int
）;
问题是，当您将第一个项目添加到cart_items时你必须
做一个数量为1的INSERT，但之后你需要做
UPDATEs。这似乎会产生一种潜在的竞争条件，所以在
为了让它起作用，看起来你需要做一个ACCESS
EXCLUSIVE锁在桌面上制作确定没有其他过程同时在桌子上阅读
。
假设我上面的逻辑是正确的，我认为还有另外两种方式
这样做，但两者似乎都多余了：
（1）我可以摆脱数量属性，只需为每个产品插入一个
记录，然后执行一个视图，使用count（）聚合
与prod_id和cart_id相同的产品。
（2）每次添加产品时，我都可以为每个购物车添加一个数量为0
的记录，每次添加购物车时我都可以添加每个产品的数量为0的
记录。
有没有更好的解决方案我错过了？这似乎是一个简单的问题，但现在我正在做全桌锁，以保证安全
方面。也许有一些涉及检查限制的解决方案？
问候，
杰夫戴维斯
------- --------------------（广播结束）------------------------- -
提示5：您是否检查过我们广泛的常见问题解答？
http://www.postgresql.org/docs/faqs/FAQ.html
Right now performance isn''t a problem, but this question has me curious:
Let''s say I have a shopping cart system where there is a "products"
table that contains all possible products, and an "cart_items" table
that stores how many of each product are in each cart.
The obvious (or the first thing that came to my mind) would look
something like this:
create table products (
id serial primary key,
...
);
create table cart_items (
id serial primary key,
cart_id int references ...,
prod_id int references product(id),
quantity int
);
The problem is, when you add the first item to "cart_items" you have to
do an INSERT with a quantity of 1, but after that you need to do
UPDATEs. That would seem to create a potential race condition, so in
order for that to work it would seem you would need to do an ACCESS
EXCLUSIVE lock on the table to make sure no other process was reading
the table at the same time.
Assuming my logic above is correct, there are two other ways I thought
to do it, but both seem considerably more redundant:
(1) I could just get rid of the "quantity" attribute and just insert a
record for each product, then do a view that aggregates the products of
the same prod_id and cart_id with count().
(2) Every time I add a product I could add a record with a quantity of 0
for each cart in existance, and every time I add a cart I could add a
record with a quantity of 0 for each product.
Is there some better solution that I''m missing? It seems like a simple
problem, but right now I''m doing the full table lock to be on the safe
side. Maybe there''s some solution involving check constraints?
Regards,
Jeff Davis
---------------------------(end of broadcast)---------------------------
TIP 5: Have you checked our extensive FAQ?
http://www.postgresql.org/docs/faqs/FAQ.html
推荐答案
世界因为 jd ********** @ empires而欢欣鼓舞。 org （Jeff Davis）写道：
The world rejoiced as jd**********@empires.org (Jeff Davis) wrote:
问题是，当你将第一个项目添加到cart_items时，你必须做一个数量为1的INSERT，但之后你需要做更新。这似乎会产生一种潜在的竞争条件，所以为了使它能够正常工作，你似乎需要在桌面上进行一次ACCESS
EXCLUSIVE锁定，以确保没有其他进程正在阅读
The problem is, when you add the first item to "cart_items" you have to
do an INSERT with a quantity of 1, but after that you need to do
UPDATEs. That would seem to create a potential race condition, so in
order for that to work it would seem you would need to do an ACCESS
EXCLUSIVE lock on the table to make sure no other process was reading
the table at the same time.
多用户
多任务系统可以提供各种竞争条件;你期待什么_actual_问题
？
我所期望的是将一个独特的索引放到cart_items上
基于（cart_id，prod_id）可以防止在
单购物车中出现多件单品的混乱情况
。
我想这是最好的防止尝试，那就是没有任何锁定而且很容易做到的b $ b。通过添加UNIQUE约束。但是
或许我想象一个不同的错误条件。
你能描述一下错误条件的本质吗
在想什么？这可能有助于表明外键检查和/或
唯一性约束可能值得添加。
-
let name =" cbbrowne" ;和tld =" ntlug.org"在String.concat中@ [name; tld] ;;
http://cbbrowne.com/info /internet.html
此登录会话：仅
Various sorts of race conditions are possible in multi-user
multi-tasking systems; what _actual_ problem are you expecting to have
here?
What I would expect is that putting a unique index onto cart_items
based on (cart_id, prod_id) would prevent getting the confusing
situation of having multiple quantities of a single product in a
single cart.
I imagine that is the best thing to try to prevent, and that is
readily done without any "locks" by adding a UNIQUE constraint. But
perhaps I am imagining a different error condition.
Can you describe the nature of the error condition that you are
thinking about? That may help indicate what foreign key checks and/or
uniqueness constraints might be worth adding.
--
let name="cbbrowne" and tld="ntlug.org" in String.concat "@" [name;tld];;
http://cbbrowne.com/info/internet.html
This login session: only
23.95！
23.95!
On 2004年5月20日星期四8:19，Jeff Davis写道：
On Thursday May 20 2004 8:19, Jeff Davis wrote:
创建表产品（
id serial primary key，
...
）;
创建表cart_items（
id序列主键，
cart_id int references ...，
prod_id int引用产品（id），
数量int
）;
问题是，当您将第一个项目添加到cart_items时你必须做一个数量为1的INSERT，但之后你需要做更新。这似乎会产生一种潜在的竞争条件，所以为了使它能够正常工作，你似乎需要在桌面上进行一次ACCESS
EXCLUSIVE锁定，以确保没有其他进程正在阅读
create table products (
id serial primary key,
...
);
create table cart_items (
id serial primary key,
cart_id int references ...,
prod_id int references product(id),
quantity int
);
The problem is, when you add the first item to "cart_items" you have to
do an INSERT with a quantity of 1, but after that you need to do
UPDATEs. That would seem to create a potential race condition, so in
order for that to work it would seem you would need to do an ACCESS
EXCLUSIVE lock on the table to make sure no other process was reading
the table at the same time.
我不确定你所看到的潜在竞争状况，因为你没说过
很多关于你的交易如何适应这里。但我建议你用你的第一个设计去你的b $ b $并且不要担心任何明确的锁定
除非/直到它明显成为一个问题。我已经构建了很多东西
与此类似，根据我的经验，PostgreSQL非常好用
如果你的交易是明智的话，以智能的方式管理锁定/>
合理分组。
HTH。
------------------- --------（广播结束）---------------------------
提示8 ：解释分析是你的朋友
I''m not sure what potential race condition you see since you haven''t said
much about how your transactions fit in here. But I would suggest you go
with your first design and don''t worry about any explicit locking
unless/until it clearly becomes a problem. I''ve built numerous things
similar to this, and in my experience, PostgreSQL is very good about
managing the locking in an intelligent manner if your transactions are
reasonably grouped.
HTH.
---------------------------(end of broadcast)---------------------------
TIP 8: explain analyze is your friend
这篇关于我锁定的比我需要的多吗？的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！
