Liking cljdoc? Tell your friends :D

Transactions

To execute one or more queries in a transaction, wrap them with begin and commit functions as follows:

(pg/begin conn)

(pg/execute conn
            "insert into test1 (name) values ($1)"
            {:params ["Test1"]})

(pg/execute conn
            "insert into test1 (name) values ($1)"
            {:params ["Test2"]})

(pg/commit conn)

Both rows are inserted in a transaction. Should one of them fail, none will succeed. By checking the database log, you'll see the following entries:

statement: BEGIN
execute s23/p24: insert into test1 (name) values ($1)
  parameters: $1 = 'Test1'
execute s25/p26: insert into test1 (name) values ($1)
  parameters: $1 = 'Test2'
statement: COMMIT

The rollback function rolls back the current transaction. The "Test3" entry will be available during transaction but won't be stored at the end.

(pg/begin conn)
(pg/execute conn
            "insert into test1 (name) values ($1)"
            {:params ["Test3"]})
(pg/rollback conn)

Of course, there is a macro what handles BEGIN, COMMIT, and ROLLBACK logic for you. The with-tx one wraps a block of code. It opens a transaction, executes the body and, if there was not an exception, commits it. If there was an exception, the macro rolls back the transaction and re-throws it.

The first argument of the macro is a connection object:

(pg/with-tx [conn]
  (pg/execute conn
              "delete from test1 where name like $1"
              {:params ["Test%"]})
  (pg/execute conn
              "insert into test1 (name) values ($1)"
              {:params ["Test3"]}))

The macro expands into something like this:

(pg/begin conn)
(try
  (let [result (do <body>)]
    (pg/commit conn)
    result)
  (catch Throwable e
    (pg/rollback conn)
    (throw e)))

The macro accepts several optional parameters that affect a transaction, namely:

NameTypeDescription
:read-only?BooleanWhen true, only read operations are allowed
:rollback?BooleanWhen true, he transaction gets rolled back even if there was no an exception
:isolation-levelKeyword or StringSet isolation level for the current transaction

The :read-only? parameter set to true restricts all the queries in this transaction to be read only. Thus, only SELECT queries will work. Running INSERT, UPDATE, or DELETE will cause an exception:

(pg/with-tx [conn {:read-only? true}]
  (pg/execute conn
              "delete from test1 where name like $1"
              {:params ["Test%"]}))

;; Execution error (PGErrorResponse) at org.pg.Accum/maybeThrowError (Accum.java:205).
;; Server error response: {severity=ERROR, code=25006, ... message=cannot execute DELETE in a read-only transaction, verbosity=ERROR}

The :rollback? parameter, when set to true, rolls back a transaction even if it was successful. This is useful for tests:

(pg/with-tx [conn {:rollback? true}]
  (pg/execute conn
              "delete from test1 where name like $1"
              {:params ["Test%"]}))

;; statement: BEGIN
;; execute s11/p12: delete from test1 where name like $1
;;   parameters: $1 = 'Test%'
;; statement: ROLLBACK

Above, inside the with-tx macro, you'll have all the rows deleted but once you get back, they will be there again.

Finally, the :isolation-level parameter sets isolation level for the current transaction. The table below shows its possible values:

:isolation-level parameterPostgres level
:read-uncommitted, "READ UNCOMMITTED"READ UNCOMMITTED
:read-committed, "READ COMMITTED"READ COMMITTED
:repeatable-read, "REPEATABLE READ"REPEATABLE READ
:serializable, "SERIALIZABLE"SERIALIZABLE

Usage:

(pg/with-tx [conn {:isolation-level :serializable}]
  (pg/execute conn
              "delete from test1 where name like $1"
              {:params ["Test%"]})
  (pg/execute conn
              "insert into test1 (name) values ($1)"
              {:params ["Test3"]}))

;; statement: BEGIN
;; statement: SET TRANSACTION ISOLATION LEVEL SERIALIZABLE
;; execute s33/p34: delete from test1 where name like $1
;;   parameters: $1 = 'Test%'
;; execute s35/p36: insert into test1 (name) values ($1)
;;   parameters: $1 = 'Test3'
;; statement: COMMIT

The default transation level depends on the settings of your database.

This document doesn't describe the difference between isolation levels. Please refer to the official documentation for more information.

❮Quick start (Demo)

Can you improve this documentation?Edit on GitHub

cljdoc is a website building & hosting documentation for Clojure/Script libraries

Keyboard shortcutsReport a problemcljdoc on GitHub
× close