Slick 3 Transactions checking for failure Multiple Actions

Question

I have a questions about Slick 3 Transactions

I want to run a series of delete rows, and check that each delete succeeded. If any fail I want to roll back the entire transactions

Here is some pseudo Slick code:

val action = DBIO.seq(
  TableQuery[X].filter(_.x===a).delete,
  TableQuery[Y].filter(_.y===b).delete,
  TableQuery[Z].filter(_.z===c).delete
).transactionally

database.run(action)

In this case each of the deletes should remove an existing row. If any does not in fact find a row to remove, I want the whole transaction to roll back.

What is the idiom to do this in Slick?

Thanks in advance Peter

opus111 opus111 · Accepted Answer · 2015-10-30T13:29:29

I found a solution that works for me. Posting it here for those that come after.

As far as I can tell, there seems to be no way in SQL to have a DELETE WHERE fail when the WHERE clause matches no rows. However a INSERT creating a duplicate row can be made to fail via table constraints.

So instead of just DELETEing the rows, I add an additional "Deleted" column to my tables. Deleting looks like this

DELETE where ... and Deleted=0
INSERT ..., Deleted=1

and (re)INSERTing looks like this

DELETE where ... and Deleted=1
INSERT ..., Deleted=0

Now if 2 processes try to delete the same row, the first DELETE will succeed for both, but the INSERT will fail with "duplicate row" for one of them. This exception will cause a rollback on the transaction.

Slick 3 Transactions checking for failure Multiple Actions

1 Answers