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

i have simple table:

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

also have function performs insert table in transactional manner (jdbc-insert-i-tx). timeout-before, timeout-after, label parameters there reproduce issue , simplify debugging.

(defn jdbc-insert-i [con i]   (jdbc/db-do-prepared-return-keys    con    ;; db-do-prepared-return-keys can updates within tx,    ;; disable behaviour sice handling txs ourselves    false    (format "insert 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 leave tx" label))       result))) 

timeouts implemented using manifold's deferreds, rather irrelevant question:

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

after i'm doing 2 simultaneous inserts of same value within separate transactions. want these updates execute before of transactions commits. therefore i'm setting timeouts, first transaction doesn't wait before doing update, waits 1 second before doing commit, while second transaction waits half second before doing update, 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} ;; transaction finished   (println @result-2) ;; => no luck, exception   ) 

after executing code above i'm getting following debug output:

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

obviously second transaction doesn't finish. happened due exception:

psqlexception error: duplicate key value violates unique constraint "i_unique"   detail: key (i)=(1) exists.       org.postgresql.core.v3.queryexecutorimpl.receiveerrorresponse     (queryexecutorimpl.java:2284) 

however exception doesn't relate serialization error (what expecting actually), informs constraint violation. occured during execution of jdbc/db-do-prepared-return-keys, , not on call connection.commit. so, seems, second transaction somehow "see" updates made first transaction. totally unexpected me, since isolation level set highest one: :serializable.

is behaviour correct? or wrong somewhere?

if 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"] 

you're experiencing same situation in question insert , transaction serialization in postresql

such behavior correct because postgresql blocks execution of subsequent concurrent mutations of row indexed field until first mutation finished (either or error), time second jdbc insert "touches" db first transaction completed. info behavior found here.