基于选择的条件插入的最佳隔离水平

发布于 2025-02-09 04:57:03 字数 1360 浏览 0 评论 0原文

有一个简单的表gode_proposal存储向用户提出的要约历史记录：

user_id	possed_on	1'AAA'2022-01-31
20	我	：10：10：
2'AAA'2022-01	id	25-07 -01 20：10：10：25-07
3'BBB'2022-01-31	和由Web请求驱动的可序列化交易try/false true/false ，	20：10：10：25-07

取决于它是否在最后一个记录中找到记录30天，特别是：

如果记录年龄大于30天（或do d d d d d，则选择给定用户ID的最新记录（从uper_proposals中选择user_id ='aaa'订单的提议_on限制1），
如果记录大于30天（或者do d d d d d of T）返回True，并使用当前时间戳（插入ewers_proposals（user_id，user_id，formosed_on）values（'aaa'，'aaa'，'〜now（）'）否则返回false，

这起作用，但是锁定的例外 /回滚正在不改变表设计，是否有更好的方法（不同的隔离级别，锁等）实现了更具性能的交易？似乎read_commited可能有效，但我不清楚是否需要其他锁定。

解释（分析，缓冲区）显示：

Buffers: shared hit=6
-> Sort (cost 8.3..8.31 rows=1 width=48) (actual time=0.195..0.196 rows=1 loops=1)
  Sort key proposed_on
  Sort method: quicksort memory: 25kB
  Buffers shared hit=6
  -> Index Scan using offer_proposal_user_id_idx on offer_proposal (cost=0.28..8.29 rows=1 width=48) (actual time=0.182..0.183 rows=1 loops=1)
     Index Cond (user_id=1234)
     Buffers shared hit=3
Planning time 0.816 ms
Execution time  0.238 ms

原文

I have a simple table offer_proposals that stores the history of offers proposed to a user:

id	user_id	proposed_on
1	'aaa'	2022-01-31 20:10:25-07
2	'aaa'	2022-01-01 20:10:25-07
3	'bbb'	2022-01-31 20:10:25-07

And a SERIALIZABLE transaction that driven by a web request taht returns true/false depending on whether it finds a record in the last 30 days, specifically:

Selects the latest record for a given user ID (select proposed_on from offer_proposals where user_id = 'aaa' order by proposed_on limit 1)
If the record is older than 30 days (or doesn't exist) return true and insert a new record for that user ID with the current timestamp (insert into offer_proposals (user_id, proposed_on) values('aaa', '~now()') otherwise return false

This works but lock acquisition exceptions / rollbacks are becoming an issue. Without changing the table design, is there a better way (different isolation level, locks, etc) to implement this transaction that is more performant? It seems like read_commited may work but I'm unclear if I'd need additional locking.

explain(analyze, buffers) shows:

Buffers: shared hit=6
-> Sort (cost 8.3..8.31 rows=1 width=48) (actual time=0.195..0.196 rows=1 loops=1)
  Sort key proposed_on
  Sort method: quicksort memory: 25kB
  Buffers shared hit=6
  -> Index Scan using offer_proposal_user_id_idx on offer_proposal (cost=0.28..8.29 rows=1 width=48) (actual time=0.182..0.183 rows=1 loops=1)
     Index Cond (user_id=1234)
     Buffers shared hit=3
Planning time 0.816 ms
Execution time  0.238 ms

分享到QQ

分享到微博

如果你对这篇内容有疑问，欢迎到本站社区发帖提问参与讨论，获取更多帮助，或者扫码二维码加入 Web 技术交流群。

发布评论

需要登录才能够评论，你可以免费注册一个本站的账号。

烂柯人 2025-02-16 04:57:03

假设有带有USER_ID列的用户表，则可以将dobe插入一个插入

insert into offer_proposals (user_id, proposed_on)
select user_id, current_date from users 
  where not exists 
   (select * from offer_proposals 
     where user_id = users.user_id 
       and proposed_on >= current_date - INTERVAL '30 days');

到ewert_proposals（user_id，insped_on）中
选择user_id，

Assuming that there is users table with user_id column, it can be dobe in one insert

insert into offer_proposals (user_id, proposed_on)
select user_id, current_date from users 
  where not exists 
   (select * from offer_proposals 
     where user_id = users.user_id 
       and proposed_on >= current_date - INTERVAL '30 days');

insert into offer_proposals (user_id, proposed_on)
select user_id,

回复收藏 0 原文

~没有更多了~