I got a MySQL deadlock on update-project-author below, while running multiple threads but on different project-id. Is laziness in get-authors involved? If so, would it help to pass get-authors the tx from the calling function? Or maybe call it before the tx is started? Or is insert-project-authors the problem?

(defn get-authors [project-id]
  (->> (project-authors-by-id db-spec {:project_id project-id})
       (map author->readable)))
       
(defn add-missing-authors-to-project [project-id authors]
  (sql/with-db-transaction
      [tx db-spec]
      (let [existing-authors (get-authors project-id)
            new-authors (->> (set/difference authors existing-authors) ...)]
          (when (seq new-authors)
            (insert-project-authors tx {:authors new-authors}))
          (doseq [author authors]
              (update-project-author tx {...})

@ulsa I spent a couple of months working most of the deadlocks out of a non-trivial mysql application, so this is near and dear to me. A couple of things jump out: 1. your call to get-authors should accept and operate on the tx to ensure the stability of the index. 2. If you’re concerned about the potential for concurrent transactions to deadlock, you should sort your updates by index value so that locks are acquired in a consistent order

If your deadlock is reproducible by a single call to add-missing-authors-to-project I think it’s very clear that the transaction in get-authors and the transaction in update-project-author are conflicting, and simply using one transaction should fix it up.

You may also be advised to specify the SERIALIZABLE isolation level or explicitly lock your selected records for update.

Thanks a lot for your insight. I'll follow those up.