My goal is to limit user to specific record count, how much he can add new records of something to my database. Right now limit is saved in the database and each time user adds or deletes new records, script checks how much records in database user owns and how much is his limit. With one user and one action per user this works just fine, but I'm struggling with solution, that works with many users and many actions per user. I noticed, that this method of counting works terrible on situations, when user do many parallel actions. And because each action is related to many tables, it have to be in database transaction. So if user starts 2 or more parallel processes, each process see total record count related to it's transaction. But after transaction total record count may exceed max limit. Problem could disappear, if I won't use database translation, but that can lead to loss of data integrity. Also problem would disappear, if I limit user to only one action, but this is also not acceptable solution.
Maybe someone have any advice, how it's best to solve this issue?
There is no solution for that that won't constitute a performance hit.
One possibility is to have a table that stores the record count for each user. You have to update that in every transaction that modifies data, perhaps using a trigger (this is a performance overhead and serializes data modifications for each user). But then a check constraint on the number of rows per user would keep users from exceeding their quota.