User's Guide

Part III. Using SQL Anywhere

Chapter 19. Using Transactions and Locks

This section discusses the following particular concurrency issues:

• Primary key generation
• Data definition statements and concurrency
• Coordinating transactions with multiple database engines

Primary key generation

Many applications generate primary key values automatically.

Example

For example, invoice numbers could be obtained by adding 1 to the previous invoice number. This will not work when there is more than one person adding invoices to the database. Two people may decide to use the same invoice number.

There is more than one solution to the problem:

• Use a different range of invoice numbers for each person that adds new invoices.
• This could be done by having a table with two columns (user name and invoice number). The table would have one row for each user that adds invoices. Each time a user adds an invoice, the number in the table would be incremented and used for the new invoice. In order to handle all tables in the database, the table should have three columns (table name, user name, and last key value).
• Have a table with two columns (table name and last key value).
• There would be one row in this table for the last invoice number used. Each time a user adds an invoice, establish a new connection, increment the number in the table, and commit. The incremented number can be used for the new invoice. Other users will be able to grab invoice numbers because you updated the row with a separate transaction that only lasted an instant.

• Probably the best solution is to use a column with a default value of AUTOINCREMENT.

  For example: CREATE TABLE orders ( order_id INTEGER NOT NULL DEFAULT AUTOINCREMENT, order_date DATE, primary key( order_id )). On INSERTs into the table, if a value is not specified for the autoincrement column, a unique value is generated. If a value is specified, it will be used. If the value is larger than the current maximum value for the column, that value will be used as a starting point for subsequent INSERTs. The value of the most recently inserted row in an autoincrement column is available as the global variable @@identity.

Data definition statements and concurrency

The CREATE INDEX statement, ALTER TABLE statement, and DROP statement are prevented whenever the statement affects a table that is currently being used by another connection. These statements can be time consuming and the database server will not process requests referencing the same table while the command is being processed.

The CREATE TABLE statement does not cause any concurrency conflicts.

The GRANT statement, REVOKE statement, and SET OPTION statement also do not cause concurrency conflicts. These commands affect any new SQL statements sent to the database engine, but do not affect existing outstanding statements.

GRANT and REVOKE for a user are not allowed if that user is connected to the database.

Coordinating transactions with multiple database engines

Two-phase commit is a mechanism of coordinating transactions between multiple servers. It is a primary component of most distributed database systems. Most applications do not need to use two-phase commit.

The first phase of a two-phase commit asks the database engine to prepare to commit and report any errors that would occur on a commit. This phase is accomplished with the PREPARE TO COMMIT statement. The second phase actually performs the commit operation using the COMMIT statement.

If you want to coordinate transactions with multiple servers, you can issue the PREPARE TO COMMIT statement to each server. If one of them fails, you can deal with the error or rollback all transactions. If all of the first phase commits are successful, you can commit each transaction knowing that there won't be any errors (except environment errors such as a network connection going down or one of the servers going off line).