Setting the transaction isolation level

You can set the transaction isolation level on a per-connection and per-transaction basis. See Transaction for an overview of the transaction isolation levels supported in Vertica. To set the default transaction isolation level for a connection, use the IsolationLevel keyword in the VerticaConnectionStringBuilder string (see Connection String Keywords for details). To set the isolation level for an individual transaction, pass the isolation level to the VerticaConnection.BeginTransaction() method call to start the transaction.

To set the isolation level on a connection-basis:

1. Use the VerticaConnectionStringBuilder to build the connection string.

2. Provide a value for the IsolationLevel builder string. It can take one of two values: IsolationLevel.ReadCommited (default) or IsolationLevel.Serializeable. For example:

       VerticaConnectionStringBuilder builder = new VerticaConnectionStringBuilder();
       builder.Host = "192.168.1.100";
       builder.Database = "VMart";
       builder.User = "dbadmin";
       builder.IsolationLevel = System.Data.IsolationLevel.Serializeable
       VerticaConnection _conn1 = new VerticaConnection(builder.ToString());
       _conn1.Open();
   

To set the isolation level on a transaction basis:

1. Set the IsolationLevel on the BeginTransaction method, for example

   VerticaTransaction txn = _conn.BeginTransaction(IsolationLevel.Serializable);
   

Example usage:

The following example demonstrates:

• getting the connection's transaction isolation level.

• setting the connection's isolation level using connection property.

• setting the transaction isolation level for a new transaction.

using System;
using System.Collections.Generic;
using System.Linq;
using System.Text;
using System.Data;
Vertica.Data.VerticaClient;
namespace ConsoleApplication
{
    class Program
    {
        static void Main(string[] args)
        {
            VerticaConnectionStringBuilder builder = new VerticaConnectionStringBuilder();
            builder.Host = "192.168.1.10";
            builder.Database = "VMart";
            builder.User = "dbadmin";
            VerticaConnection _conn1 = new VerticaConnection(builder.ToString());
             _conn1.Open();
            VerticaTransaction txn1 = _conn1.BeginTransaction();
            Console.WriteLine("\n Transaction 1 Transaction Isolation Level: " +
             txn1.IsolationLevel.ToString());
            txn1.Rollback();
            VerticaTransaction txn2 = _conn1.BeginTransaction(IsolationLevel.Serializable);
            Console.WriteLine("\n Transaction 2 Transaction Isolation Level: " +
             txn2.IsolationLevel.ToString());
            txn2.Rollback();
            VerticaTransaction txn3 = _conn1.BeginTransaction(IsolationLevel.ReadCommitted);
            Console.WriteLine("\n Transaction 3 Transaction Isolation Level: " +
             txn3.IsolationLevel.ToString());
            _conn1.Close();
        }
    }
}

When run, the example code prints the following to the system console:

 Transaction 1 Transaction Isolation Level: ReadCommitted
 Transaction 2 Transaction Isolation Level: Serializable
 Transaction 3 Transaction Isolation Level: ReadCommitted