iAnywhere.com - Snapshot isolation example

Click here to view and discuss this page in DocCommentXchange. In the future, you will be sent there automatically.

SQL Anywhere 10.0.1 » SQL Anywhere Server - SQL Usage » Using Transactions and Isolation Levels » Isolation levels and consistency » Snapshot isolation

Snapshot isolation example

The following example uses two connections to the SQL Anywhere sample database to illustrate how snapshot isolation can be used to maintain consistency without blocking.

To use snapshot isolation

Execute the following command to create an Interactive SQL connection (Connection1), to the SQL Anywhere sample database:
```
dbisql -c "DSN=SQL Anywhere 10 Demo;UID=DBA;PWD=sql;ConnectionName=Connection1"
```
Execute the following command to create an Interactive SQL connection (Connection2) to the SQL Anywhere sample database:
```
dbisql -c "DSN=SQL Anywhere 10 Demo;UID=DBA;PWD=sql;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:
```
UPDATE Products
SET Name = 'Tee Shirt'
WHERE id = 302;
COMMIT;
```

For additional examples about using snapshot isolation, see: