Collection Contents Index Savepoints within transactions Replication and concurrency pdf/chap15.pdf

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

Particular concurrency issues

This section discusses the following particular concurrency issues:

Top of page  Primary key generation

You will encounter situations where the database should automatically generate a unique number. For example, if you are building a table to store sales invoices you might prefer that the database assign unique invoice numbers automatically, rather than require sales staff pick them.

There are many methods for generating such numbers.


For example, invoice numbers could be obtained by adding 1 to the previous invoice number. This method 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:

   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.

Unique values in replicated databases    
Different techniques are required if you replicate your database and more than one person can add entries which must later be merged.
. See Replication and concurrency.

Autoincrement using PowerBuilder    
Adaptive Server Anywhere supports an AUTOINCREMENT default value on fields. However, this type of field cannot be used by a PowerBuilder data window for primary key generation, because PowerBuilder is unable to find the record after insertion. If a table in your database is only added to from a PowerBuilder script, you may want to use the AUTOINCREMENT default value mechanism.

Top of page  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 is 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.

Data definition statements and replicated databases    
Using data definition statements in replicated databases requires special care.
See the separate manual entitled Data Replication with SQL Remote.

Top of page  

Collection Contents Index Savepoints within transactions Replication and concurrency pdf/chap15.pdf