Snapshot isolation example

Execute 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"

Execute 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 command 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 command:

SELECT * FROM Products;

ID

Name

Description

Size

Color

Quantity

...

300

Tee Shirt

Tank Top

Small

White

28

...

301

Tee Shirt

V-neck

Medium

Orange

54

...

302

Tee Shirt

Crew Neck

One size fits all

Black

75

...

400

Baseball Cap

Cotton Cap

One size fits all

Black

112

...

...

...

...

...

...

...

...

In Connection2, execute the following command:

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 command:

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 command to allow snapshot isolation:

SET OPTION PUBLIC.allow_snapshot_isolation = 'On';

In Connection 1, execute the following command 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, issue 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 command:

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;

ID

Name

Description

Size

Color

Quantity

...

300

Tee Shirt

Tank Top

Small

White

28

...

301

Tee Shirt

V-neck

Medium

Orange

54

...

302

New Tee Shirt

Crew Neck

One size fits all

Black

75

...

400

Baseball Cap

Cotton Cap

One size fits all

Black

112

...

...

...

...

...

...

...

...

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