The requirement
I am currently building a permissions system. One of the requirements is that it is horizontally scalable.
To achieve this, we have done the following
There is a single "compiled resource permission" table that looks something like this:
| user_id | resource_id | reason |
| 1 | 1 | 1 |
| 1 | 2 | 3 |
| 2 | 1 | 2 |
The structure of this table denotes user 1 has accesses to resource 1 & 2, and user 2 has access only to resource 1.
The "reason" column is a bitwise number which has bits switched on depending on "Why" they have that permission. Binary bit "1" denotes they are an admin, and binary bit "2" denotes they created the resource.
So user 1 has access to resource 1 because they are an admin. He has access to resource 2 because he is an admin and he created that resource. If he was no longer admin, he would still have access to ticket 2 but not ticket 1.
To figure out what needs to go into this table, we use a "patcher" class that programmatically loops around the users & resources passed to it and logically looks at all DB tables necessary to figure out what rows need adding and what rows need removing from the table.
How we are trying to scale and the problem
To horizontally scale this, we split the logic into chunks and give it to a number of "workers" on an async queue
This only seems to scale so far before it no longer speeds up or sometimes even row locking happens which slows it down.
Is there a particular type of row lock we can use to allow it to scale indefinitely?
Are we approaching this from completely the wrong angle? We have a lot of "Reasons" and a lot of complex permission logic that we need to be able to recompile fairly quickly
SQL Queries that run concurrently, for reference
When we are "adding" reasons:
INSERT INTO `compiled_permissions` (`user_id`, `resource_id`, `reason`) VALUES ((1,1,1), (1,2,3), (2,1,2)) ON DUPLICATE KEY UPDATE `reason` = `reason` | VALUES(`reason`);
When we are "removing" reasons:
UPDATE `compiled_permissions` SET `reason` = `reason` & ~ (CASE
(user_id = 1 AND resource_id = 1 THEN 2 ... CASE FOR EVERY "REASON REMOVAL")
ELSE `reason`
END)
WHERE (`user_id`, `resource_id`) IN ((1,1),(1,2) .. ETC )
