User's Guide
   PART 2. Relational Database Concepts
     CHAPTER 15. Using Transactions and Locks       

Introduction to concurrency

Concurrency means processing more than one transaction at the same time 

Adaptive Server Anywhere can execute more than one transaction at the same time. The term concurrency refers to this ability. Were it not for special mechanisms within the database server, concurrent transactions could interfere with each other to produce inconsistent and incorrect information.

A transaction is a logical unit of work. When a database engine executes transactions sequentially, the database is advanced step by step from one consistent state to the next.

To respond promptly to requests from various users, the database engine must execute statements from other users without waiting for a first user to complete a transaction. Without a control mechanism, the actions of the various users could interfere with each other. For example, two users might try to update the same price at the same time.

Individual users group their work into transactions which, when executed in isolation, modify the database in a safe manner. When executing more than one transaction at once, the database server must take precautions to maintain this security and restrict interference between transactions. Interference can cause the database to end up in a state which is not possible to obtain by executing the separate transactions one at a time.

Transactions processed at the same time are said to be concurrent. While executing the SQL statements which comprise one transaction, the database engine can execute some or all of the statements in other transactions. All Sybase database engines and servers can execute multiple transactions concurrently.

Why concurrency benefits you 

Often, databases form common repositories of information and are shared by a large number of people. These people may need frequent access to the information as part of their jobs. To avoid impeding their work, the database engine must be able to process many transactions at the same time.

Adaptive Server Anywhere allows many simultaneous connections to one database. Usually, these connections are formed by separate users. Concurrent transaction processing means not only that a database engine can accept multiple connections, but that it can also process transactions from more than one connected user or application simultaneously.

Example 

Consider a database for a department store. The database system must allow many clerks to update customer accounts simultaneously. Each clerk must be able to update the status of the accounts as they assist each customer. Each clerk cannot afford to wait until no one else is using the database.

Who needs to know about concurrency 

Concurrency is a concern to all database administrators and developers. Even if you are working with a single-user database, you must be concerned with concurrency. In addition to connections from multiple users, your Adaptive Server Anywhere database can accept separate connections from multiple applications and even 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.

  Tutorial 1: The dirty read

The following tutorial demonstrates one type of inconsistency which can occur when multiple transactions are executed concurrently. Two employees at a typical small merchandising company both access the corporate database at the same time. The first person is the company's Sales Manager. The second is the Accountant.

The Sales Manager wants to increase the price of one of the tee shirts sold by their firm by 95, but is having little trouble with the syntax of the SQL language. At the very same time, unbeknownst to the Sales Manager, the Accountant is trying to calculate the retail value of the current inventory to include in a report he volunteered to bring to the next management meeting.

In this example, you will play the role of two people, both using the demonstration database concurrently.

1. Start Interactive SQL.

2. Connect to the sample database: Select Connect from the Command menu. Enter the user ID DBA and the password SQL. In the Advanced tab of the connection window, name this connection Sales Manager.

   User ID:	DBA
   Password:	SQL
   Connection Name:	Sales Manager
   Database File:	asademo

3. Start a second copy of Interactive SQL.

4. Again enter DBA and SQL as the user ID and password, but this time name the connection Accountant. Click the OK button.

   User ID:	DBA
   Password:	SQL
   Connection Name:	Accountant

5. Pretend you are the Sales Manager. You have decided to raise the price of all the tee shirts by 95. In the window labeled "Sales Manager," execute the following commands:

   SELECT id, name, unit_price
   FROM product;
   UPDATE PRODUCT
   SET unit_price = unit_price + 95
   WHERE NAME = 'Tee Shirt';

   id	name	unit_price
   300	Tee Shirt	104.00
   301	Tee Shirt	109.00
   302	Tee Shirt	109.00
   400	Baseball Cap	9.00
   401	Baseball Cap	10.00
   500	Visor	7.00
   501	Visor	7.00
   600	Sweatshirt	24.00
   601	Sweatshirt	24.00
   700	Shorts	15.00

   You observe immediately that you should have entered 0.95 instead of 95, but before you can fix your error, the accountant accesses the database from another office.

6. The company's accountant is worried that too much money is tied up in inventory. Pretend you are the accountant. In the window named "Accountant," execute the following commands to calculate the total retail value of all the merchandise in stock:

   SELECT SUM( quantity * unit_price )
     AS inventory
   FROM product;

   inventory
   21453.00

   Unfortunately, this calculation isn't accurate. The Sales Manager accidentally raised the price of the visor $95, and the result reflects this erroneous price. This mistake demonstrates one typical type of inconsistency known as a dirty read. You, as the Accountant, accessed data which the Sales Manager has entered, but has not yet committed.

        You can eliminate dirty reads and other inconsistencies explained in Isolation levels and consistency.

7. Return to the role of Sales Manager. In the first window fix the error by rolling back your first changes and entering the correct UPDATE command. Check that your new values are correct.

   ROLLBACK;
   UPDATE product
   SET unit_price = unit_price + 0.95
   WHERE NAME = 'Tee Shirt';

   id	name	unit_price
   300	Tee Shirt	9.95
   301	Tee Shirt	14.95
   302	Tee Shirt	14.95
   400	Baseball Cap	9.00
   401	Baseball Cap	10.00
   500	Visor	7.00
   501	Visor	7.00
   600	Sweatshirt	24.00
   601	Sweatshirt	24.00
   700	Shorts	15.00

8. The Accountant does not know that the amount he calculated was in error. You can see the correct value by executing his SELECT statement again in his window.

   SELECT SUM( quantity * unit_price )
     AS inventory
   FROM product;

   inventory
   6687.15

9. Finish the transaction in the Sales Manager's window. She would enter a COMMIT statement to make his changes permanent, but you may wish to enter a ROLLBACK, instead, to avoid changing the copy of the demonstration database on your machine.

   ROLLBACK;

The accountant unknowingly receives erroneous information from the database because the database engine is processing the work of both the Sales Manager and the Accountant simultaneously. The following section introduces other types of inconsistencies and introduces means to eliminate them.

  Using locks to ensure consistency

Row-level locking 

Adaptive Server Anywhere uses row-level locking to allow transactions to execute concurrently without interference, or with limited interference. Any transaction can acquire a lock to prevent other concurrent transactions from modifying or even accessing a particular row. This row-level locking scheme always stops some types of interference. For example, a transaction which is updating a particular row of a table always acquires a lock on that row to ensure that no other transaction can update or delete the same row at the same time.

Some inconsistency may be tolerable 

Inconsistency in the information an application sees is tolerable in some cases. Therefore, you do not need to prohibit all forms of inconsistent behavior in all cases. For this reason, Adaptive Server Anywhere grants you control over the level of consistency required in the information any transaction sees.

In the above example, you were able to observe the Sales Manager's uncommitted results in the Accountant's window. This type of inconsistency is known as a dirty read. You were able to observe this problem because some locking features are turned off in the demonstration database allowing this type of inconsistency to occur. Various locking options let you eliminate such interference.

  Improving concurrency

Transaction Size Affects Concurrency 

The way you group SQL statements into transactions can have significant effects on data integrity and on system performance. If you make a transaction too short and it does not contain an entire logical unit of work, then inconsistencies can be introduced into the database. If you write a transaction which is too long and contains several unrelated actions, then there is greater chance that a ROLLBACK will unnecessarily undo work that could have been committed quite safely into the database.

Locks obtained during a transaction are not released until a COMMIT or ROLLBACK statement is issued. If your transactions are long and lock large amounts of data, they can lower concurrency by preventing other transactions from being processed simultaneously.

There are many factors which determine 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.

Use fewer locks for better concurrency 

The number of locks which your transactions place can affect the level of concurrency which your database can support. Each lock limits the access to some specific row of a table. If another transaction should require access to the same row, then it may have to wait until your transaction completes. The more locks you place, the more likely it is that other transactions will be delayed. You should use sufficient locking to ensure the integrity of your data, but it is good practice to use the lowest level of locking which will suffice. Doing so allows your database to process transactions as quickly as possible.

The locking scheme and the options which allow you to limit interference and control locking are discussed in detail later in this chapter.