Transactions
Tip
Examples in multiple languages can be found at following links: immudb SDKs examples
immudb supports transactions both on key-value and SQL level, but interactive transactions are supported only on SQL with the exception of execAll method, that provides some additional properties.
Interactive transactions are a way to execute multiple SQL statements in a single transaction. This makes possible to delegate application logic to SQL statements - a very common use case is for example checking if the balance > 0 before making a purchase.
In order to create a transaction, you must call the NewTx() method on the client instance. The resulting object is a transaction object that can be used to execute multiple SQL statements, queries, commit or rollback.
Following methods are exposed by the transaction object:
1Commit() CommittedSQLTx, error
2Rollback() error
3SQLExec(sql, params) error
4SQLQuery(sql, params) SQLQueryResult, errorIt’s possible to rollback a transaction by calling the Rollback() method. In this case, the transaction object is no longer valid and should not be used anymore.
To commit a transaction, you must call the Commit() method.
Note: immudb implements multi-version concurrency control. Thus multiple read-write transactions may be concurrently processed. It’s up the application to handle read conflict errors. In case a read-write conflict is detected, the sdk will return the 25P02 CodInFailedSqlTransaction error code.
Go
1package main
2
3import (
4 "context"
5 "fmt"
6 "log"
7
8 immudb "github.com/codenotary/immudb/pkg/client"
9)
10
11func handleErr(err error) {
12 if err != nil {
13 log.Fatal(err)
14 }
15}
16
17func main() {
18 opts := immudb.DefaultOptions().
19 WithAddress("localhost").
20 WithPort(3322)
21
22 client := immudb.NewClient().WithOptions(opts)
23 err := immudb.OpenSession(
24 context.TODO(),
25 []byte(`immudb`),
26 []byte(`immudb`),
27 "defaultdb",
28 )
29 handleErr(err)
30
31 defer client.CloseSession(context.TODO())
32
33 tx, err := client.NewTx(context.TODO())
34 handleErr(err)
35
36 err = tx.SQLExec(
37 context.TODO(),
38 `CREATE TABLE IF NOT EXISTS mytable(id INTEGER AUTO_INCREMENT, title VARCHAR[256], active BOOLEAN, PRIMARY KEY id);`,
39 nil,
40 )
41 handleErr(err)
42
43 nRows := 10
44 for i := 0; i < nRows; i++ {
45 err := tx.SQLExec(
46 context.TODO(),
47 "INSERT INTO mytable(title, active) VALUES (@title, @active)",
48 map[string]interface{}{
49 "title": fmt.Sprintf("title%d", i),
50 "active": i%2 == 0,
51 },
52 )
53 handleErr(err)
54 }
55
56 txh, err := tx.Commit(context.TODO())
57 handleErr(err)
58
59 fmt.Printf("Successfully committed rows %d\n", txh.UpdatedRows)
60
61 reader, err := client.SQLQueryReader(context.TODO(), "SELECT * FROM mytable", nil)
62 handleErr(err)
63
64 for reader.Next() {
65 row, err := reader.Read()
66 handleErr(err)
67
68 fmt.Println(row[0], row[1])
69 }
70}Java
1package io.codenotary.immudb.helloworld;
2
3import io.codenotary.immudb4j.FileImmuStateHolder;
4import io.codenotary.immudb4j.ImmuClient;
5import io.codenotary.immudb4j.sql.SQLQueryResult;
6import io.codenotary.immudb4j.sql.SQLValue;
7
8public class App {
9
10 public static void main(String[] args) {
11
12 ImmuClient client = null;
13
14 try {
15
16 FileImmuStateHolder stateHolder = FileImmuStateHolder.newBuilder()
17 .withStatesFolder("./immudb_states")
18 .build();
19
20 client = ImmuClient.newBuilder()
21 .withServerUrl("127.0.0.1")
22 .withServerPort(3322)
23 .withStateHolder(stateHolder)
24 .build();
25
26 client.openSession("defaultdb", "immudb", "immudb");
27
28 client.beginTransaction();
29
30 client.sqlExec(
31 "CREATE TABLE IF NOT EXISTS mytable(id INTEGER, title VARCHAR[256], active BOOLEAN, PRIMARY KEY id)");
32
33 final int rows = 10;
34
35 for (int i = 0; i < rows; i++) {
36 client.sqlExec("UPSERT INTO mytable(id, title, active) VALUES (?, ?, ?)",
37 new SQLValue(i),
38 new SQLValue(String.format("title%d", i)),
39 new SQLValue(i % 2 == 0));
40 }
41
42 SQLQueryResult res = client.sqlQuery("SELECT id, title, active FROM mytable");
43
44 while (res.next()) {
45 System.out.format("('%s', '%s')\n", res.getInt(0), res.getString(1), res.getBoolean(2));
46
47 }
48
49 client.commitTransaction();
50
51 client.closeSession();
52
53 } catch (Exception e) {
54 e.printStackTrace();
55 } finally {
56 if (client != null) {
57 try {
58 client.shutdown();
59 } catch (InterruptedException e) {
60 e.printStackTrace();
61 }
62 }
63 }
64
65 }
66
67}Python
Currently immudb Python sdk doesn’t support interactive transactions.
However you can still use non-interactive SQL Transactions.
1from immudb import ImmudbClient
2from uuid import uuid4
3
4URL = "localhost:3322" # ImmuDB running on your machine
5LOGIN = "immudb" # Default username
6PASSWORD = "immudb" # Default password
7DB = b"defaultdb" # Default database name (must be in bytes)
8
9def main():
10 client = ImmudbClient(URL)
11 client.login(LOGIN, PASSWORD, database = DB)
12
13 client.sqlExec("""
14 CREATE TABLE IF NOT EXISTS example (
15 uniqueID VARCHAR[64],
16 value VARCHAR[32],
17 created TIMESTAMP,
18 PRIMARY KEY(uniqueID)
19 );""")
20
21 client.sqlExec("""
22 CREATE TABLE IF NOT EXISTS related (
23 id INTEGER AUTO_INCREMENT,
24 uniqueID VARCHAR[64],
25 relatedValue VARCHAR[32],
26 PRIMARY KEY(id)
27 );""")
28
29 uid1 = str(uuid4())
30 uid2 = str(uuid4())
31 params = {
32 "uid1": uid1,
33 "uid2": uid2
34 }
35
36 resp = client.sqlExec("""
37 BEGIN TRANSACTION;
38
39 INSERT INTO example (uniqueID, value, created)
40 VALUES (@uid1, 'test1', NOW()), (@uid2, 'test2', NOW());
41 INSERT INTO related (uniqueID, relatedValue)
42 VALUES (@uid1, 'related1'), (@uid2, 'related2');
43 INSERT INTO related (uniqueID, relatedValue)
44 VALUES (@uid1, 'related3'), (@uid2, 'related4');
45
46 COMMIT;
47 """, params)
48
49 transactionId = resp.txs[0].header.id
50
51 result = client.sqlQuery("""
52 SELECT
53 related.id,
54 related.uniqueID,
55 example.value,
56 related.relatedValue,
57 example.created
58 FROM related
59 JOIN example
60 ON example.uniqueID = related.uniqueID;
61 """)
62 for item in result:
63 id, uid, value, relatedValue, created = item
64 print("ITEM", id, uid, value, relatedValue, created.isoformat())
65
66
67 result = client.sqlQuery(f"""
68 SELECT
69 related.id,
70 related.uniqueID,
71 example.value,
72 related.relatedValue,
73 example.created
74 FROM related BEFORE TX {transactionId}
75 JOIN example BEFORE TX {transactionId}
76 ON example.uniqueID = related.uniqueID;
77 """)
78 print(result) # You can't see just added entries,
79 # my fellow time traveller
80
81if __name__ == "__main__":
82 main()Node.js
This feature is not yet supported or not documented. Do you want to make a feature request or help out? Open an issue on Node.js sdk github project
.NET
This feature is not yet supported or not documented. Do you want to make a feature request or help out? Open an issue on .Net sdk github project
Others
If you’re using another development language, please refer to the immugw option.
Savepoints#
Savepoints allow partial rollback within a transaction. This is essential for ORM compatibility (Django, Rails, etc.).
1BEGIN TRANSACTION;
2
3INSERT INTO orders (id, product) VALUES (1, 'Widget');
4
5SAVEPOINT before_update;
6
7UPDATE orders SET product = 'Gadget' WHERE id = 1;
8
9-- Undo the update, keep the insert
10ROLLBACK TO SAVEPOINT before_update;
11
12COMMIT;
13-- Only the original insert (Widget) is committedSupported statements:
SAVEPOINT name– create a savepointROLLBACK TO SAVEPOINT name– rollback to a savepoint (alsoROLLBACK TO name)RELEASE SAVEPOINT name– remove a savepoint