Fork me on GitHub

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]
      [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 {...})
Using java.jdbc 0.7.3 and Hugsql 0.4.8. db-spec is a JDBC connection string. The get, insert, and update fns are Hugsql queries/fns.


@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


@U04V4HWQ4 what do you mean by "stability of the index"?


That records haven’t been inserted into, updated in, or removed from the ranges covered by the indexes used in the select query


so you basically mean "no phantom reads", right?


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.