Multiuser Support in EDB (Windows Embedded CE 6.0)
1/6/2010
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.
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 and are committed, or fail and are rolled back.
Note
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 in a transaction, whether you explicitly start a transaction or a transaction is implicitly started internally by EDB when a function such as CeDeleteRecord (EDB) 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, first create a session handle by calling the CeCreateSession (EDB) function, and then pass received session handle when you open each database. You can also call the CeGetDatabaseSession (EDB) 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, begin a transaction by calling the CeBeginTransaction (EDB) function. After you start the transaction, all changes made to the databases are queued until you end the transaction. End the transaction by calling the CeEndTransaction (EDB) function. CeEndTransaction enables you to either commit or revert all changes in the queue.
When using transactions, remember the following:
- Transactions cannot be nested. In other words, CeBeginTransaction cannot be called if another transaction 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 CeEndTransaction 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 e, see CeFlushDBVol (EDB).
- The flush interval setting for the volume is reached.
- The volume is unmounted.
EDB Locking
EDB uses locking to 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 fails with a sharing violation. The transaction that receives the sharing violation can retry the operation or can fail and display an error.
Important
Even if a transaction is committed, some locks can still be held in the database. To ensure that all locks are released, close the handle to the database.
Outside of transactions, EDB does not lock any data in the database. If 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 can update the row. Your update then overwrites 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, 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, the row can be locked as soon as data is read, thus blocking other transactions from changing that row.
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 can 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.
See Also
Reference
CeCreateSession (EDB)
CeGetDatabaseSession (EDB)
CeBeginTransaction (EDB)
CeEndTransaction (EDB)