User's Guide Part III. Using SQL Anywhere Chapter 19. Using Transactions and LocksSQL Anywhere supports transaction processing to ensure that logically related commands get executed as a unit, and to enable multiple applications to have access to a database.
When several users are working with the same information in a database at the same time (concurrently), their actions may interfere with each other to produce inconsistent and incorrect information. Transaction processing and row-level locking allow concurrent use while maintaining database consistency.
Transaction processing is not a concern simply for developers of applications for multiuser database engines; developers working with single-user databases also have to be concerned with concurrency. Single-user SQL Anywhere databases may still have multiple applications connected to them, or may have multiple connections from a single application. These applications and connections can interfere with each other in exactly the same way as multiple users in a network setting.
Transaction processing is fundamental to proper data recovery in case of system failures.
For information about database backups and data recovery, see the chapter "Backup and Data Recovery".
SQL statements are grouped into transactions. A transaction is a logical unit of work, meaning that the set of commands making up a transaction must be processed in its entirety, or not at all. From the user's point of view, a transaction is indivisible. Transactions are atomic.
Transactions start with one of the following events:
Transactions complete with one of the following events:
SQL Anywhere's transaction processing ensures that each transaction is processed in its entirety or not at all. Transaction processing is fundamental to ensuring that a database contains correct information. It addresses two distinct, yet related, problems: data recovery and database consistency in the face of concurrent usage.
A transfer of funds from one account to another is an archetypal transaction. This transaction consists of two operations:
If the transaction is completed properly both operations are recorded in the database. However, if something happens after the from account is debited to prevent the transaction being completed, then it is unacceptable to leave the debit in the database without recording the credit. Either both the debit and the credit must be processed, or neither. In case of failure, the debit needs to be undone, or rolled back .
Transaction processing ensures that if, for any reason, a transaction is not successfully completed, then the entire transaction is undone, or rolled back. The database is left entirely unaffected by failed transactions.
SQL Anywhere's transaction processing ensures that the contents of a transaction are processed securely, even in the event of system failures in the middle of a transaction. The mechanisms for data recovery are described in the chapter "Backup and Data Recovery". The remainder of this chapter is devoted to concurrency and consistency of transactions.
When several users are using a database at the same time they are said to be concurrent users. Even single-user databases need to be aware of concurrency problems, as several applications could be run at the same time on one machine, or a single application could have several active connections to the database at one time.
If several connections access the same information in a database at the same time, they could interfere with each other and produce inconsistencies in the database. Transaction processing helps to ensure databases remain consistent while allowing concurrent transactions.
There are three types of inconsistency that can occur during the execution of concurrent transactions:
SQL Anywhere uses a locking scheme to ensure that concurrent transactions do not interfere with each other to produce inconsistencies.
Inconsistency in the information an application sees is tolerable in some cases. Therefore, we do not need to prohibit all forms of inconsistent behavior in all cases. For this reason, the client application developer is given some control over the level of consistency required in the information the application sees.
The SQL Anywhere locking scheme restricts access to the information that a particular transaction is working with to ensure that other transactions do not see information that may not be committed to the database, and do not alter information on which the transaction is relying. The locking scheme is discussed in detail later in this chapter.
The way SQL statements are grouped into transactions can have significant effects on data integrity and on system performance. If a transaction is too short, and does not contain an entire logical unit of work, then inconsistencies can be introduced into the database. If a transaction is too long, and contains several unrelated actions, then there is greater chance of a ROLLBACK unnecessarily undoing work that could have been committed quite safely into the database. Also, if transactions are too long, and lock large amounts of data, they can prevent other transactions from being processed and so reduce concurrency.
There are many factors determining the appropriate length of a transaction, depending on the type of application and the environment. Some guidelines are given towards the end of this chapter.