Multi-User Support in EDB (Windows CE 5.0)

Article
09/14/2012

EDB provides the ability for multiple threads, across multiple processes, to access the same database concurrently. Each thread that accesses the database is considered a user. Because EDB supports multiuser access, it must support transactions and locking to prevent data corruption and collisions.

Note Only EDB supports transactions. CEDB does not support transactions or locking. All operations with CEDB are atomic.

Understanding EDB Transactions

Transactions are used to group a set of database operations into a single atomic operation, thereby dramatically improving the reliability of an application. An atomic operation is one in which all operations in the group either succeed (commit) or fail (roll back). Without transactions, each application must handle data conflicts and undo logic internally, which can be complex and prone to errors.

For example, suppose you have developed an e-mail application. When a user marks an e-mail message as read, your application must update two databases: the database that stores the e-mail message (called DB1) and the database that tracks the read and unread messages within the e-mail folder (called DB2). Likewise, marking a message as unread also requires updating both databases.

If DB1 updates successfully, but the update to DB2 fails, your application must resolve the difference. It must either undo the change it made to DB1, or retry the update to DB2. But what if either of those operations fails?

Even in this simple scenario, handling errors can become very complex very quickly. However, transactions make it quite simple. The same scenario, using transactions, follows these steps:

Begin a transaction on the volume.
Modify the "Read" property on DB1.
Modify the "Read" count on DB2.
Commit and end the transaction.

In EDB, the commit is atomic, which means that the operations performed inside the transaction are either fully committed, or not committed at all. In the steps above, if either step 2 or step 3 fails, the transaction is not committed, and changes do not occur to either database.

While the process of marking an e-mail message as read requires two physical operations, it is one logical operation. When determining where to use transactions, look for logical operations that you can group together inside a single transaction.

Note Transactions can be extremely useful, but it is important to realize that if you lock an object in a transaction, it remains locked until the transaction is ended. This may block other users from accessing data in the database.

Using Transactions with EDB

In EDB, everything happens inside of a transaction, whether you explicitly start a transaction or a transaction is implicitly started internally by EDB when a function such as CeDeleteRecord is called.

Note If a transaction is explicit, it can contain many changes; if a transaction is implicit, it contains only one change.

To use transactions, each database involved in the transaction must be participating in the same session. To do this, you first create a session handle by calling the CeCreateSession function, and then pass in this resulting session handle when you open each database. You can also call the CeGetDatabaseSession function to obtain the session handle for a previously opened database. This is useful when performing a transaction between two databases.

Once you have one or more databases in a session, you begin a transaction by calling the CeBeginTransaction function. After you start the transaction, all of the changes made to the databases are queued until you end the transaction. You end the transaction by calling the CeEndTransaction function. The CeEndTransaction function allows you to either commit or revert all of the changes in the queue.

When using transactions, consider the following rules:

Transactions cannot be nested. In other words, CeBeginTransaction can't be called if another CeBeginTransaction is currently being used in the session.
All operations based on data (for example, reading, writing, inserting, or updating data) are affected by the scope of the begin/end transaction statements.
All operations that affect the schema of a database (such as adding properties to the database or creating sort orders) are unaffected by the scope of the transaction. Schema changes are committed even if the transaction is reverted.

Committing a Transaction

When you call the CeEndTransaction function and commit the changes made in the transaction, the changes are not immediately saved to disk. As with all data written to the database, the committed changes are cached until one of the following occurs:

The volume is explicitly flushed. For more information about explicitly flushing a volume, see CeFlushDbVol.
The FlushInterval setting for the volume is reached.
The volume is unmounted.

EDB Locking

EDB uses locking to help implement transactions. If a change is made to a row during a transaction, the row is locked until the end of the transaction. Any relevant sort order page is also locked. While the row or page is locked, any other transaction that tries to update the row or page will fail with a sharing violation. The transaction that receives the sharing violation can retry the operation again on the assumption that the row or page has been unlocked, or can fail and display an error to the user.

Important Even if a transaction is committed, some locks may still be held in the database. To ensure that all locks are released, you must close the handle to the database.

It is important to understand that, outside of transactions, EDB does not hold a lock on any data in the database. When designing client applications that use EDB, you must be aware of the ramifications of this. For example, suppose you have a thread that reads a row and then updates it. Between the time you read the row and send your update, another thread updates the row. Your update will overwrite the other thread's changes, resulting in unexpected data. Also, if your application is positioned on a row, another thread can delete that row, changing your row position. To prevent these possible problems, you should use transactions.

EDB Isolation Levels

Although transactions are useful when changing data in a database, you do not need to use them when reading data. EDB includes a versioning mechanism to ensure that the latest committed version is always read by a thread. However, if you need to ensure that a row that is read early in a transaction is not modified by another transaction, you must protect that row. To protect the row, EDB supports transaction isolation levels. Depending on the isolation level you use, the row may be locked as soon as data is read, thus blocking other transactions from performing certain operations.

Note A row is protected only if you explicitly seek to it or if an implicit AUTOINCREMENT seek occurs.

The following table shows the three isolation levels supported by EDB:

Isolation level	Supports a repeatable read	Phantom rows can be introduced
SQLCEDB_ISOLEVEL_READCOMMITTED or SQLCEDB_ISOLEVEL_DEFAULT	No	Yes
SQLCEDB_ISOLEVEL_REPEATABLEREAD	Yes	Yes
SQLCEDB_ISOLEVEL_SERIALIZABLE	Yes	No

Note A phantom row occurs when a row is added to the database after you have read the rows. When you use the SQLCEDB_ISOLEVEL_SERIALIZABLE isolation level, no other thread can add a row, thus preventing the introduction of phantom rows.

To minimize the overhead of locking, EDB occasionally reduces a large number of row-level locks to a single lock on the entire database. This may adversely affect concurrency. To reduce the likelihood of this occurring, construct transactions that operate on small sets of data and ensure that they are committed or reverted as quickly as possible.

Note Versioning does not apply to the CEVT_STREAM data type.