Does the many_to_many magic in ecto run within a transaction?

Questions / Help
1

I’m running a GenServer to periodically sanitize the records of a many-to-many association that might be stale as no other records are referencing them anymore, and wondering if there might be any conflicts as I’m running deletion while Ecto adds the records before inserting associations on which I base my assumption whether they are in use or not. Can I depend on a Read Committed isolation in this scenario?

2

You could lock the row while you are changing it:
https://hexdocs.pm/ecto/Ecto.Query.html#lock/3

1 Like
3

Checked with log_statement = 'all', it does happen between BEGIN and COMMIT. Great!

4

Postgres doesn’t let you do an isolation level lower than Read Committed, but read committed is not enough in this case.

As @BradS2S notes you’ll need to lock the parent row so that you have serial access to its children.

5

As long as I don’t see new records that are in the process of inserting by another transaction, I should be good, since my aim is simply to delete unused ones. What do you think?

6

Two things:

  1. other records could be in the process of becoming “unused” while you delete the current set. This is fine, they’ll be caught in the next run of your cleanup process, I’m just noteing it.

  2. I’m a bit curious about how you have unused records at all. If you use foreign key constraints won’t that just ensure that everything is linked?

7

It’s because many-to-many is realized through an intermediate table, which only maintains cascading deletion into itself from the users or uploads ends. I can’t perform cascading deletion of uploads that have no other links to users through other records in user_uploads, at least I don’t know how.

image
image815×331 4.68 KB