3

I have a table with structure:

CREATE TABLE public.users_storages
(
  user_id bigint NOT NULL,
  item_type integer NOT NULL,
  count integer,
  CONSTRAINT users_storages_pkey PRIMARY KEY (user_id, item_type)
)

And I have such query: INSERT INTO users_storages SELECT $1,$2,$3 WHERE (SELECT sum(count) FROM users_storages WHERE user_id = $1 GROUP BY user_id) <= $4 But, such query have a race condition, how i can lock inserting rows for user_id = $1?

user3928409
  • 65
  • 1
  • 5
  • see this question http://stackoverflow.com/questions/14179713/locking- table-in-postgresql – Shubham Batra Feb 23 '16 at 05:16
  • Yes, it's related. But I can't use unique indexes, and the only way It's locking whole table, or use serializable isolation level. But i want lock inserting only for specific primary key part (user_id) – user3928409 Feb 23 '16 at 11:51
  • then You can use `begin and commit` with `rollback` or this one might be helpful to you http://stackoverflow.com/questions/7395915/does-inserting-data-into-sql-server-lock-the-whole-table – Shubham Batra Feb 23 '16 at 12:23
  • I'm using Postgresql, and lock table is not good. – user3928409 Feb 23 '16 at 13:52

0 Answers0