Review which routes need to be changed to a single DB transaction

[sf2ne]

The paginated routes (/changes, policy /search, pattern /search) already are a single transaction.

Regarding transactions, slick, and postgresql:

• Each individual DBIOAction in db.run(), including those in flatmap(), runs in auto-commit mode, if transactionally is not wrapped around multiple DBIOActions. I think this means that the DBIOAction objects that are not combinators like seq and sequence don't need transactionally. (We have about 15 cases like this. Not sure if slick automatically doesn't create a transaction for this or not.)
• To wrap several db.run flatmap steps into a single transaction, follow https://scala-slick.org/doc/3.3.0/dbio.html#transactions :

  
  val a = (for {
  ns <- coffees.filter(_.name.startsWith("ESPRESSO")).map(_.name).result
  _ <- DBIO.seq(ns.map(n => coffees.filter(_.name === n).delete): _*)
  } yield ()).transactionally

val f: Future[Unit] = db.run(a)

Potentially related to: https://github.com/open-horizon/exchange-api/issues/402

[naphelps]

These 5 routes effect policy/pattern search route(s):

nodePutPolicyRoute nodeDeletePolicyRoute nodePutAgreementRoute nodeDeleteAgreementsRoute nodeDeleteAgreementRoute

These routes make multiple independent DB changes which include updating the lastUpdated attribute. Each change is its own transaction and when the route fails the DB changes up to that point remain. The downstream impact of this on the search routes is random skipping/hanging in returned nodes. It can at times prevent the search routes from returning any nodes.

Additional routes with similar issues likely exist within the exchange.

[naphelps]

Transactions from multiple routes and well as multiple calls to the same route are presently allowed to be interleaved. This can have unknown consequences on downstream routes that use data in the DB to function.