UltraLite Database User's Guide
UltraLite databases may receive multiple concurrent requests. In order to design applications that handle concurrent requests properly you should understand how UltraLite manages concurrency in the database.
It is helpful to separate several concepts when thinking about concurrent database access. These concepts are ordered from high-level to low-level:
Applications The UltraLite engine can respond to requests from multiple separate applications. Other versions of the UltraLite runtime permit only a single application to connect to a database at a time.
For more information, see The UltraLite runtime.
Threads The UltraLite runtime supports multi-threaded applications. A single application may be written to use multiple threads, each of which may connect to the database.
For more information, see Threading in UltraLite applications.
Connections Even a single-threaded application may open multiple connections to a database. In any case, individual connections can employ only a single thread.
Transactions Each connection can have a single transaction in progress at any one time. Transactions may consist of a single request or multiple requests. Data modifications made during a transaction are not permanent in the database until the transaction is committed. Either all data modifications made in a transaction are committed, or all are rolled back.
Requests A transaction consists of one or more requests. A request may be a query that reads data, or an insert, update, or delete that modifies data, or a synchronization.
The current row When an application is working with the result set of a query, UltraLite maintains a pointer to the current row within the result set. In some interfaces, this current row is tracked explicitly using a cursor (a pointer to a position in a result set). In others, the application uses methods on a result set object or table object to identify and change the current row. Such methods use a cursor "under the covers".
The UltraLite runtime can manage a maximum of four databases at any one time. A single UltraLite application may open multiple connections to separate databases. No concurrency issues arise from such applications, as the data in each database is independent.
When a transaction changes a row, UltraLite locks that row until the transaction is committed or rolled back. The lock prevents other transactions from changing the row, although they can still read the row. An attempt to change a locked row sets error SQLCODE SQLE_LOCKED, while an attempt to change a deleted row sets the error SQLE_NOTFOUND. Your applications should check the SQLCODE value after attempting to modify data.
To understand how locking and concurrency is managed, it helps to consider two connections, A and B, each with their own transaction.
As connection A works with the result set of a query, UltraLite fetches a copy of the current row into a buffer. If 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. At that time, a write lock is placed on the row to prevent other transactions from modifying it. The change to the database is not permanent until connection A commits the transaction.
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.
If connection B does modify the current row, the row becomes locked. Connection A can then not modify the current row. If connection A fetches the current row again, and the row has been deleted, connection A gets the next row in the result set. If the row has been modified, connection A gets the latest copy of the row. If the columns of the index used by connection A have been modified, connection A sees the change as a delete followed by an insert, and so gets the next row in the result set.
Synchronization behaves as a separate connection. During the upload phase, UltraLite applications can access UltraLite databases in a read-only fashion. During the download phase, read-write access is permitted but if an application changes a row that the download then attempts to change, the download will fail and roll back. You can disable access to data during synchronization by setting the Disable Concurrency synchronization parameter.
For more information, see Disable Concurrency synchronization parameter.
SQL Anywhere Studio 9.0.2
Copyright © 1989–2004 Sybase, Inc. Portions copyright © 2001–2004 iAnywhere Solutions, Inc. All rights reserved.