Run the following command to create an Interactive SQL connection (Connection1) to the SQL Anywhere sample database:

dbisql -c "DSN=SQL Anywhere 12 Demo;ConnectionName=Connection1"

Run the following command to create an Interactive SQL connection (Connection2) to the SQL Anywhere sample database:

dbisql -c "DSN=SQL Anywhere 12 Demo;ConnectionName=Connection2"

In Connection1, execute the following statement to set the isolation level to 1 (read committed), which acquires and holds a read lock on the current row.

SET OPTION isolation_level = 1;

In Connection1, execute the following statement:

SELECT * FROM Products;

In Connection2, execute the following statement:

UPDATE Products
SET Name = 'New Tee Shirt'
WHERE ID = 302;

In Connection1, execute the SELECT statement again:

SELECT * FROM Products;

The SELECT statement is blocked and cannot proceed because the UPDATE statement in Connection2 has not been committed or rolled back. The SELECT statement must wait until the transaction in Connection2 is complete before it can proceed. This ensures that the SELECT statement does not read uncommitted data into its result.

In Connection2, execute the following statement:

ROLLBACK;

The transaction in Connection2 completes, and the SELECT statement in Connection1 proceeds.

Using the statement snapshot isolation level achieves the same concurrency as isolation level 1, but without blocking.

In Connection1, execute the following statement to allow snapshot isolation:

SET OPTION PUBLIC.allow_snapshot_isolation = 'On';

In Connection 1, execute the following statement to change the isolation level to statement snapshot:

SET TEMPORARY OPTION isolation_level = 'statement-snapshot';

In Connection1, execute the following statement:

SELECT * FROM Products;

In Connection2, execute the following statement:

UPDATE Products
SET Name = 'New Tee Shirt'
WHERE ID = 302;

In Connection1, execute the SELECT statement again:

SELECT * FROM Products;

The SELECT statement executes without being blocked, but does not include the data from the UPDATE statement executed by Connection2.

In Connection2, finish the transaction by executing the following statement:

COMMIT;

In Connection1, finish the transaction (the query against the Products table), and then execute the SELECT statement again to view the updated data:

COMMIT;
SELECT * FROM Products;

Undo the changes to the SQL Anywhere sample database by executing the following statement:

UPDATE Products
SET Name = 'Tee Shirt'
WHERE id = 302;
COMMIT;