Why one Clojure JDBC serializable transaction "sees" (?) changes made by another transaction?

Question

I have simple table:

create table tx_test
(
  i integer,
  constraint i_unique unique (i)
);

Also I have function that performs insert to this table in transactional manner (jdbc-insert-i-tx). timeout-before, timeout-after, label parameters are there only to help reproduce the issue and simplify debugging.

(defn jdbc-insert-i [con i]
  (jdbc/db-do-prepared-return-keys
   con
   ;; db-do-prepared-return-keys can itself do updates within tx,
   ;; disable this behaviour sice we are handling txs by ourselves
   false
   (format "insert into tx_test values(%s)" i)
   []))

(defn jdbc-insert-i-tx [db-spec timeout-before timeout-after label i]
  (jdbc/with-db-transaction [t-con db-spec :isolation :serializable]
    (and timeout-before
         (do
           (println (format "--> %s: waiting before: %s" label timeout-before))
           (do-timeout timeout-before)))
    (let [result (do
                   (println (format "--> %s: doing update" label))
                   (jdbc-insert-i t-con i))]
      (and
       timeout-after
       (do
         (println (format "--> %s: waiting after: %s" label timeout-after))
         (do-timeout timeout-after)))
      (println (format "--> %s about to leave tx" label))
      result)))

Timeouts are implemented using manifold's deferreds, but this is rather irrelevant to this question:

(defn do-timeout [ms]
  @(d/timeout! (d/deferred) ms nil))

After I'm doing two simultaneous inserts of the same value within separate transactions. I want these updates to execute before any of transactions commits. Therefore I'm setting timeouts, so first transaction doesn't wait before doing an update, but waits 1 second before doing a commit, while second transaction waits for half a second before doing an update, but doesn't wait before commit.

(let [result-1 (d/future (jdbc-insert-i-tx db-spec nil 1000 :first 1))
      result-2 (d/future (jdbc-insert-i-tx db-spec 500 nil :second 1))]
  (println @result-1) ;; => {:i 1} ;; this transaction finished successfully
  (println @result-2) ;; => no luck, exception
  )

After executing the code above I'm getting the following debug output:

--> :first: doing update
--> :second: waiting before: 500
--> :first: waiting after: 1000
--> :second: doing update
--> :first about to leave tx

Obviously second transaction doesn't finish. This happened due to exception:

PSQLException ERROR: duplicate key value violates unique constraint "i_unique"
  Detail: Key (i)=(1) already exists.       org.postgresql.core.v3.QueryExecutorImpl.receiveErrorResponse     (QueryExecutorImpl.java:2284)

However the exception doesn't relate to serialization error (what I was expecting actually), but informs about constraint violation. Also it occured during execution of jdbc/db-do-prepared-return-keys, and not on the call to Connection.commit. So, it seems, second transaction could somehow "see" updates made by first transaction. This is totally unexpected for me, since isolation level was set to the highest one: :serializable.

Is this behaviour correct? Or am wrong somewhere?

If this helps, I'm using following libraries:

[com.mchange/c3p0 "0.9.5.2"]
[org.postgresql/postgresql "9.4.1208"]
[org.clojure/java.jdbc "0.3.7"]

Answer 1

You're experiencing basically the same situation as in this question INSERT and transaction serialization in PostreSQL

Such behavior is correct because Postgresql blocks execution of the subsequent concurrent mutations of the row with indexed field until the first mutation is completely finished (either successfully or with an error), so by the time your second JDBC insert "touches" DB the first transaction is already completed. Some info about this behavior could be found here.