Changing the Transaction Isolation Level
Changing the transaction isolation level lets you choose how transactions prevent interference from other transactions. By default, the JDBC driver matches the transaction isolation level of the Vertica server. The Vertica default transaction isolation level is READ_COMMITTED
, which means any changes made by a transaction cannot be read by any other transaction until after they are committed. This prevents a transaction from reading data inserted by another transaction that is later rolled back.
Vertica also supports the SERIALIZABLE
transaction isolation level. This level locks tables to prevent queries from having the results of their WHERE
clauses changed by other transactions. Locking tables can have a performance impact, since only one transaction is able to access the table at a time.
A transaction retains its isolation level until it completes, even if the session's isolation level changes during the transaction. Vertica internal processes (such as the Tuple Mover and refresh operations) and DDL operations always run at the SERIALIZABLE
isolation level to ensure consistency.
You can change the transaction isolation level connection property after the connection has been established using the Connection
object's setter (setTransactionIsolation()
) and getter (getTransactionIsolation()
). The value for transaction isolation property is an integer. The Connection
interface defines constants to help you set the value in a more intuitive manner:
Constant | Value |
---|---|
|
2 |
|
8 |
The Connection
interface also defines several other transaction isolation constants (READ_UNCOMMITTED
and REPEATABLE_READ
). Since Vertica does not support these isolation levels, they are converted to READ_COMMITTED
and SERIALIZABLE
, respectively.
The following example demonstrates setting the transaction isolation level to SERIALIZABLE.
import java.sql.*; import java.util.Properties; public class SetTransactionIsolation { public static void main(String[] args) { Properties myProp = new Properties(); myProp.put("user", "ExampleUser"); myProp.put("password", "password123"); Connection conn; try { conn = DriverManager.getConnection( "jdbc:vertica://VerticaHost:5433/ExampleDB", myProp); // Get default transaction isolation System.out.println("Transaction Isolation Level: " + conn.getTransactionIsolation()); // Set transaction isolation to SERIALIZABLE conn.setTransactionIsolation(Connection.TRANSACTION_SERIALIZABLE); // Get the transaction isolation again System.out.println("Transaction Isolation Level: " + conn.getTransactionIsolation()); conn.close(); } catch (SQLException e) { e.printStackTrace(); } } }
Running the example results in the following being printed out to the console:
Transaction Isolation Level: 2Transaction Isolation Level: 8