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

Isolation level side effects

UltraLite operates at an isolation level of 0. You cannot change the isolation level in UltraLite. This means you should interpret the result of your queries with this limitation in mind.

An isolation level of 0 means the following side effects are possible:

• No locking operations are required when executing a SELECT statement.

• Applications can access uncommitted data (also known as dirty reads). In this scenario, an application may access rows in the database that are not committed and consequently may still get rolled back by another transaction. This phenomena can result in phantom rows (that is, rows that get added after the original query, making the result set returned in a repeated, duplicate query different).

  For a tutorial that demonstrates the effects of dirty reads, see Tutorial: dirty reads. For a tutorial that demonstrates a phantom row, see Tutorial: phantom rows.

• Applications can perform non-repeatable reads. In this scenario, an application reads a row from the database, then goes on to perform other operations. Then a second application updates/deletes the row and commits the change. If the first application attempts to re-read the original row, it receives either the updated information or discovers that the original row was deleted.

  For a tutorial that demonstrates the effects of non-repeatable reads, see Tutorial: non-repeatable reads.

Consider two connections, A and B, each with their own transactions.

1. As connection A works with the result set of a query, UltraLite fetches a copy of the current row into a buffer.

   Note Reading or fetching a row does not lock the row. If connection A fetches but does not modify a row, connection B can still modify the row.

2. As A modifies the current row, it changes the copy in the buffer. The copy in the buffer is written back into the database when connection A calls an Update method or closes the result set.

3. A write lock is placed on the row to prevent other transactions from modifying it. This modification is uncommitted, until connection A performs a commit.

4. Depending on the modification, if connection B fetches the current row, it may experience the following:

   Connection A's modification	Result1
   Row has been deleted.	Connection B gets the next row in the result set.
   Row has been modified.	Connection B gets the latest copy of the row.

   ¹Queries used by Connection A and B do not contain temporary tables. Temporary tables can cause other side effects.