How to handle concurrency during an INSERT-SELECT cycle?

Question

The application should limit each user's access to a certain resource to a maximum number of accesses every 24 hours. To keep track of those, we have the following table:

CREATE TABLE accesses (
    user_id         INTEGER NOT NULL,
    dt              TIMESTAMPZ
);

Each time the resource is accessed, we record it in the table. However, we also need to check if the access limit was reached. To that end, we need to execute this query:

SELECT count(*) 
FROM accesses 
WHERE dt >= timezone('utc', now()) - interval '24 hours' AND user_id = $1

and we need to check the result against the user limit.

Now, if two accesses are requested at the same time we may have problems:

• if we first read, compare, and the insert we may grant more accesses than the limit;
• if we first insert, then read and compare, we may disallow legitimate accesses.

The two ways that I know are to either use a SERIALIZABLE transaction or SELECT ... FOR UPDATE. I am not sure if the latter actually works in this case, because we will not update the rows that are read, rather we will insert new ones. So I think that it'll be a useless lock and the result will probably be still incorrect.

However, with a SERIALIZABLE transaction I have retry it in case it fails.

What should be done in this case, to ensure proper access limits?

Answer 1

Not sure if that's the best approach, but it seems to be a quick and reliable as long as application[s] consistently uses it. Create a new table, access_attempt (user_id integer not null primary key).
Modify you workflow :

begin ;
insert into attempt_access (user_id) values (:user_id);
-- do you verification, raise an error if needed
insert into access (user_id, dt) values(:user_id, current_timestamp) ;
delete from access_attempt where user_id = :user_id;
commit;  

A brief description. First of all, a new transaction is started and we insert a user_id into the new table attempt_access which has just one column. Until transaction completes , other transactions will not be able to insert row with the same user_id and will wait until the first one complete. Then verification is performed, record insert into access table, and finally the record inserted in the very beginning is removed. Now transaction can be committed . If transaction is rolled back the record in attempt_access will be removed automatically .

Answer 2

Assuming this doesn't cause too much of a bottleneck, you could use a PostgreSQL advisory lock to serialize access to the tables. That way your app can only do anything when it has the lock.

https://www.postgresql.org/docs/11/explicit-locking.html