4 ms·

How does this stay correct in the presence of concurrent activity?

berkle4455 3y ago
Using transactions or UUID/ULIDs though maybe I’m misunderstandingyour question. How do foreign key constraints help with concurrency?
- AlisdairO 3y ago
  Table User: userid, etc Table Resources: resourceid, userid, etc If I want to restrict deletion of a user to only be possible after all the resources are deleted, I'm forced into using higher-than-default isolation levels in most DBs. This has significant performance implications. It's also much easier to make a mistake - for example, if when creating a resource I check that the user exists prior to starting the transaction, then start the tran, then do the work, it will allow insertion of data into a nonexistent user.
  - edf13 3y ago
    Add your user check as a where clause on the resources insert?
    - AlisdairO 3y ago
      Can you give an example? I’m not aware of a mechanism like that that will protect you from concurrency artifacts reliably - certainly not a general one.
      - drwiggly 3y ago
        start transaction; select id from users where id = ? for update; if row_count() < 1 then raise 'no user' end if; insert into sub_resource (owner, thing) values (?, ?); commit; ??
        AlisdairO 3y ago
        Do that in most relational dbs in the default isolation level (read committed), and concurrently executing transactions will still be able to delete users underneath you after the select. If we take postgres as an example, performing the select takes exactly zero row level locks, and makes no guarantees at all about selected data remaining the same after you’ve read it. edit: my mistake - I missed that the select is for update. Yes, this will take explicit locks and thus protect you from the deletion, but is slower/worse than just using foreign keys, so it won't fundamentally help you. further edit: let's take an example even in a higher isolation level (repeatable read): -- setup postgres=# create table user_table(user_id int); CREATE TABLE postgres=# create table resources_table(resource_id int, user_id int); CREATE TABLE postgres=# insert into user_table values(1); INSERT 0 1 Tran 1: postgres=# BEGIN TRANSACTION ISOLATION LEVEL REPEATABLE READ; BEGIN postgres=# select * from user_table where user_id = 1; user_id --------- 1 (1 row) Tran 2: postgres=# BEGIN TRANSACTION ISOLATION LEVEL REPEATABLE READ; BEGIN postgres=# select * from resources_table where user_id = 1; resource_id | user_id -------------+--------- (0 rows) postgres=# delete from user_table where user_id = 1; DELETE 1 postgres=# commit; COMMIT Tran 1: postgres=# insert into resources_table values (1,1); INSERT 0 1 postgres=# commit; COMMIT Data at the end: postgres=# select * from resources_table; resource_id | user_id -------------+--------- 1 | 1 (1 row) postgres=# select * from user_table; user_id --------- (0 rows) You can fix this by using SERIALIZABLE, which will error out in this case. This stuff is harder than people think, and correctly indexed foreign keys really aren't a performance issue for the vast majority of applications. I strongly recommend just using them until you have a good reason not to.

Using transactions or UUID/ULIDs though maybe I’m misunderstandingyour question. How do foreign key constraints help with concurrency?

AlisdairO 3y ago
Table User: userid, etc Table Resources: resourceid, userid, etc If I want to restrict deletion of a user to only be possible after all the resources are deleted, I'm forced into using higher-than-default isolation levels in most DBs. This has significant performance implications. It's also much easier to make a mistake - for example, if when creating a resource I check that the user exists prior to starting the transaction, then start the tran, then do the work, it will allow insertion of data into a nonexistent user.
- edf13 3y ago
  Add your user check as a where clause on the resources insert?
  - AlisdairO 3y ago
    Can you give an example? I’m not aware of a mechanism like that that will protect you from concurrency artifacts reliably - certainly not a general one.
    - drwiggly 3y ago
      start transaction; select id from users where id = ? for update; if row_count() < 1 then raise 'no user' end if; insert into sub_resource (owner, thing) values (?, ?); commit; ??
      - AlisdairO 3y ago
        Do that in most relational dbs in the default isolation level (read committed), and concurrently executing transactions will still be able to delete users underneath you after the select. If we take postgres as an example, performing the select takes exactly zero row level locks, and makes no guarantees at all about selected data remaining the same after you’ve read it. edit: my mistake - I missed that the select is for update. Yes, this will take explicit locks and thus protect you from the deletion, but is slower/worse than just using foreign keys, so it won't fundamentally help you. further edit: let's take an example even in a higher isolation level (repeatable read): -- setup postgres=# create table user_table(user_id int); CREATE TABLE postgres=# create table resources_table(resource_id int, user_id int); CREATE TABLE postgres=# insert into user_table values(1); INSERT 0 1 Tran 1: postgres=# BEGIN TRANSACTION ISOLATION LEVEL REPEATABLE READ; BEGIN postgres=# select * from user_table where user_id = 1; user_id --------- 1 (1 row) Tran 2: postgres=# BEGIN TRANSACTION ISOLATION LEVEL REPEATABLE READ; BEGIN postgres=# select * from resources_table where user_id = 1; resource_id | user_id -------------+--------- (0 rows) postgres=# delete from user_table where user_id = 1; DELETE 1 postgres=# commit; COMMIT Tran 1: postgres=# insert into resources_table values (1,1); INSERT 0 1 postgres=# commit; COMMIT Data at the end: postgres=# select * from resources_table; resource_id | user_id -------------+--------- 1 | 1 (1 row) postgres=# select * from user_table; user_id --------- (0 rows) You can fix this by using SERIALIZABLE, which will error out in this case. This stuff is harder than people think, and correctly indexed foreign keys really aren't a performance issue for the vast majority of applications. I strongly recommend just using them until you have a good reason not to.