Getting Started with Distributed SQL Transactions in the ScalarDB Cluster .NET Client SDK

The ScalarDB Cluster .NET Client SDK supports the distributed SQL transaction functionality of ScalarDB Cluster. The SDK includes transaction and manager abstractions for easier communication within a cluster.

note

Although we recommend using asynchronous methods, as in the following examples, you can use synchronous methods instead.

For details about distributed non-SQL transactions, see Getting Started with Distributed Transactions in the ScalarDB Cluster .NET Client SDK.

Install the SDK

Install the same major and minor version of the SDK as ScalarDB Cluster into the .NET project. You can do this by using the built-in NuGet package manager, replacing <MAJOR>.<MINOR> with the version that you're using:

dotnet add package ScalarDB.Client --version '<MAJOR>.<MINOR>.*'

Create a settings file

Create a scalardb-options.json file and add the following, replacing <HOSTNAME_OR_IP_ADDRESS> with the FQDN or the IP address, and <PORT> with the port number (60053 by default) of your cluster:

{
  "ScalarDbOptions": {
    "Address": "http://<HOSTNAME_OR_IP_ADDRESS>:<PORT>",
    "HopLimit": 10
  }
}

For details about settings files and other ways to configure the client, see Client configuration.

Get a transaction manager

You need to get a transaction manager object for distributed SQL transactions. To get the transaction manager object, you can use TransactionFactory as follows:

// Pass the path to the settings file created in the previous step.
var factory = TransactionFactory.Create("scalardb-options.json");

using var manager = factory.GetSqlTransactionManager();

Execute SQL queries

To execute a SQL statement, you need an ISqlStatement object, which can be created by using a builder as follows:

using ScalarDB.Client.Builders.Sql;

// ...

var sqlStatement =
    new SqlStatementBuilder()
        .SetSql("SELECT * FROM order_service.statements WHERE item_id = :item_id")
        .AddParam("item_id", 2)
        .Build();

A single SQL statement can be executed directly by using the transaction manager as follows:

var resultSet = await manager.ExecuteAsync(sqlStatement);

The result from the ExecuteAsync method will contain records received from the cluster. The value of the specific column can be retrieved in the following manner:

foreach (var record in resultSet.Records)
{
    // Getting an integer value from the "item_id" column.
    // If it fails, an exception will be thrown.
    var itemId = record.GetValue<int>("item_id");
            
    // Trying to get a string value from the "order_id" column.
    // If it fails, no exception will be thrown.
    if (record.TryGetValue<string>("order_id", out var orderId))
        Console.WriteLine($"order_id: {orderId}");
            
    // Checking if the "count" column is null.
    if (record.IsNull("count"))
        Console.WriteLine("'count' is null");
}

For details about which type should be used in GetValue<T> and TryGetValue<T>, see How ScalarDB Column Types Are Converted to and from .NET Types.

Execute SQL queries in a transaction

To execute multiple SQL statements as part of a single transaction, you need a transaction object.

You can create a transaction object by using the transaction manager as follows:

var transaction = await manager.BeginAsync();

You can also resume a transaction that has already been started as follows:

var transaction = manager.Resume(transactionIdString);

note

The Resume method doesn't have an asynchronous version because it only creates a transaction object. Because of this, resuming a transaction by using the wrong ID is possible.

Begin a read-only transaction

If you perform only read operations (that is, SELECT statements) in a transaction, you can begin a read-only transaction as follows:

var transaction = await manager.BeginReadOnlyAsync();

note

Read-only transactions are processed more efficiently than read transactions begun without the read-only mode by assuming no write operations are issued within them. Therefore, attempting to perform write operations (INSERT, UPDATE, or DELETE statements) in a read-only transaction will result in an error.

The transaction has the same ExecuteAsync method as the transaction manager. That method can be used to execute SQL statements.

When a transaction is ready to be committed, you can call the CommitAsync method of the transaction as follows:

await transaction.CommitAsync();

To roll back the transaction, you can use the RollbackAsync method:

await transaction.RollbackAsync();

Execute SQL statements in a batch

The ExecuteBatchAsync method allows you to execute multiple SQL statements in a single RPC call, reducing round-trip overhead when performing batch operations. You can use this method with both the transaction manager and the transaction object.

Execute a batch by using the transaction manager

You can execute a batch of SQL statements directly by using the transaction manager as follows:

using ScalarDB.Client.Builders.Sql;

// ...

var statements = new[]
{
    new SqlStatementBuilder()
        .SetSql("INSERT INTO ns.statements (order_id, item_id, count) VALUES (:order_id, :item_id, :count)")
        .AddParam("order_id", "1")
        .AddParam("item_id", 1)
        .AddParam("count", 5)
        .Build(),
    new SqlStatementBuilder()
        .SetSql("INSERT INTO ns.statements (order_id, item_id, count) VALUES (:order_id, :item_id, :count)")
        .AddParam("order_id", "2")
        .AddParam("item_id", 2)
        .AddParam("count", 10)
        .Build()
};

var resultSets = await manager.ExecuteBatchAsync(statements);

Execute a batch in a transaction

You can also execute a batch of SQL statements as part of a transaction as follows:

var transaction = await manager.BeginAsync();

var resultSets = await transaction.ExecuteBatchAsync(statements);

await transaction.CommitAsync();

The results are returned as IReadOnlyList<IResultSet> in the same order as the input statements. For SELECT statements, the result set contains the retrieved records. For INSERT, UPDATE, and DELETE statements, the result set is empty.

The following example executes multiple SELECT statements in a batch and processes the results:

var selectStatements = new[]
{
    new SqlStatementBuilder()
        .SetSql("SELECT * FROM ns.statements WHERE order_id = :order_id")
        .AddParam("order_id", "1")
        .Build(),
    new SqlStatementBuilder()
        .SetSql("SELECT * FROM ns.statements WHERE order_id = :order_id")
        .AddParam("order_id", "2")
        .Build()
};

var resultSets = await transaction.ExecuteBatchAsync(selectStatements);

for (var i = 0; i < resultSets.Count; i++)
{
    foreach (var record in resultSets[i].Records)
    {
        var itemId = record.GetValue<int>("item_id");
        Console.WriteLine($"item_id: {itemId}");
    }
}

Use positional parameters

In addition to named parameters, you can use positional parameters as follows:

var statements = new[]
{
    new SqlStatementBuilder()
        .SetSql("SELECT * FROM ns.statements WHERE order_id = ? AND item_id = ?")
        .AddParam("1")
        .AddParam(1)
        .Build(),
    new SqlStatementBuilder()
        .SetSql("SELECT * FROM ns.statements WHERE order_id = ? AND item_id = ?")
        .AddParam("2")
        .AddParam(2)
        .Build()
};

var resultSets = await transaction.ExecuteBatchAsync(statements);

note

If an empty collection is provided to ExecuteBatchAsync, no RPC call is made and an empty list is returned.

Get Metadata

You can retrieve ScalarDB's metadata with the Metadata property as follows:

// namespaces, tables metadata
var namespaceNames = new List<string>();

await foreach (var ns in manager.Metadata.GetNamespacesAsync())
{
    namespaceNames.Add(ns.Name);
    Console.WriteLine($"Namespace: {ns.Name}");
    
    await foreach (var tbl in ns.GetTablesAsync())
    {
        Console.WriteLine($"  Table: {tbl.Name}");

        Console.WriteLine($"  Columns:");
        foreach (var col in tbl.Columns)
            Console.WriteLine($"    {col.Name} [{col.DataType}]");

        Console.WriteLine($"  PartitionKey:");
        foreach (var col in tbl.PartitionKey)
            Console.WriteLine($"    {col.Name}");

        Console.WriteLine($"  ClusteringKey:");
        foreach (var col in tbl.ClusteringKey)
            Console.WriteLine($"    {col.Name} [{col.ClusteringOrder}]");

        Console.WriteLine($"  Indexes:");
        foreach (var index in tbl.Indexes)
            Console.WriteLine($"    {index.ColumnName}");

        Console.WriteLine();
    }
}

// users metadata
await foreach (var user in manager.Metadata.GetUsersAsync())
{
    Console.WriteLine($"User: {user.Name} [IsSuperuser: {user.IsSuperuser}]");

    foreach (var nsName in namespaceNames)
    {
        Console.WriteLine($"  Namespace: {nsName}");

        Console.WriteLine($"  Privileges:");
        foreach (var privilege in await user.GetPrivilegesAsync(nsName))
            Console.WriteLine($"    {privilege}");
    }

    Console.WriteLine();
}

note

To use LINQ methods with IAsyncEnumerable<T>, you can install System.Linq.Async package.