To atone for the harshness of my previous reply, here are some answers to the questions you ACTUALLY asked...

"My question is this: on the comment marked "HERE", is there any possibility that another connection could alter that value before the SELECT sets the variable?"

No. The UPDATE statement acquires a lock on the row that prevents all other connections from reaching the "HERE" point until the update is committed. Since the UPDATE is issued from inside a stored procedure, the client application doesn't know anything about it, so even if client-side autocommit is turned on the client software won't send an immediate COMMIT to the server together with the UPDATE. Since a BEGIN TRANSACTION has been executed, even if server-side autocommit is turned on at this point (CHAINED = 'OFF') SQL Anywhere itself will not execute an immediate COMMIT together with the UPDATE.

Those immediate COMMITs, either sent by the client or executed by the server, are what constitute "autocommit", and autocommit is evil, but that's off-topic.

"Is the row locked by the BEGIN TRANSACTION command in a way that will avoid concurrency issues?"

No. The UPDATE gets a lock, locks can cause blocks, and blocks cause concurrency problems. If the second explicit COMMIT statements in this procedure actually does a commit (no guarantee!) there won't be any locks held after the procedure terminates, then life is good. However, if a transaction is already in progress when the procedure is called, the lock acquired by the UPDATE will not be released by the second COMMIT... see the discussion of @@TRANCOUNT below (and have the Tylenol handy :)

So, yeah, the inner BEGIN TRANSACTION / COMMIT is unnecessary... it has no effect.

"Is there a better/faster way for this code to work?"

Yes. If the system is very busy, it is possible for two or more connection to more-or-less simultaneously execute this statement

IF EXISTS(SELECT * FROM "DBA"."UniqueIds" WHERE "IdName" = @IdName)

in which case they will all try to execute this statement

INSERT INTO "DBA"."UniqueIds" ("IdName", "Id") VALUES(@IdName, 1)

and all but one connections will fail. This will probably never happen, however, since it only happens for the first new entry in each @IdName.

The real problem is concurrency, when this procedure is called as part of a string of updates being executed in one transaction. All connections attempting to insert a new entry in @IdName will execute one after the other, single-threaded... this procedure will be a bottleneck on a busy system.

Let's assume you can't change the underlying columns to use DEFAULT AUTOINCREMENT, and since you're using Version 11 you can't use CREATE SEQUENCE.

You might consider a series of empty shadow tables, each with a single DEFAULT AUTOINCREMENT column that corresponds to a column of interest in a real table. Replace calls to your procedure with SET statements like SET @pkey = GET_IDENTITY ( 'shadow-table-name', 1 ) to reserve and return the next autoincrement value, then use @pkey in the INSERT. The shadow tables remain empty, and have no effect whatsoever on the rest of the application... especially no locks, blocks or concurrency issues.

For the record, here is a chapter from my book on SQL Anywhere 9; the facts are the same for SQL Anywhere 11. Note that nested SAVEPOINT and ROLLBACK statements are not discussed here.

9.3 Transactions

A database transaction is a sequence of SQL statements that are treated as an single unit for the purposes of correctly satisfying a request while ensuring database integrity. Transaction design is an important part of application design, and it has profound effects on database performance as well as correctness and integrity.

A transaction must satisfy four requirements: it must be atomic, consistent, isolated and durable, or "ACID" for short.

Atomic: All of the database changes made by the transaction must be completed when the transaction finishes successfully; if the transaction fails none of the changes are to be made to the database. A failure at any point during the transaction causes the entire transaction to fail; a failure may be explicitly forced by a SQL statement such as ROLLBACK, or caused implicitly by an external event such as a system crash. For example, if a transaction inserts a sales_order row in the ASADEMO database, together with one or more corresponding sales_order_item rows, a failure during the transaction means that none of those rows exist when the transaction is completed.

Consistent: All constraints on the data must be satisfied when the transaction is completed. These include constraints maintained by application programs as well as constraints defined within the database schema.

Isolated: Separate transactions must not change the same data at the same time; all changes must be isolated from one another. The smallest unit of isolation with SQL Anywhere is the row.

Isolation may be taken further when considering whether different transactions may even see the effects of changes made by other transactions; the ISOLATION_LEVEL option applies to this aspect, and is discussed later in this chapter. As far as updates are concerned, however, isolation is not optional; once a transaction has changed a row in a table, SQL Anywhere does not permit any other transaction from changing the same row until the first transaction is completed.

Durable: All database changes made by a transaction that finishes successfully must be persistent; subsequent failures must not affect the changes made by this transaction. In other words, a COMMIT must be permanent.

For example, a transaction may insert a sales_order row in the ASADEMO database, together with one or more corresponding sales_order_item rows. This transaction is atomic because all of the inserted rows will exist in the database if the transaction succeeds, and none of them will remain if the transaction fails. This transaction is consistent because it satisfies the application constraint that for every sales_order row at least one corresponding sales_order_item row must also exist; it also satisfies the database foreign key constraint that every sales_order_item row must have a single corresponding sales_order row. This transaction is isolated because no other database connection can delete or update the inserted rows until the transaction is successfully completed. This transaction is durable because, once it is successfully completed, the inserted rows remain in the database.

Each transaction has a beginning and an end, and is run within the context of a single connection. One connection may execute more than one transaction, but only one after another in a serial fashion. Different transactions may run at the same time on different connections, and they can affect one another, but a single transaction does not span more than one connection.

Note: Individual non-compound SQL statements are atomic in nature, which means that if the statement fails any changes it has already made to the database are automatically undone. This applies to single INSERT, UPDATE and DELETE statements that operate on more than one row; if one of these statements fail after affecting one or more rows, all of its effects are automatically undone. This activity is separate from transaction control; the failure of one statement does not automatically cause a transaction to roll back. The atomic nature of SQL statements is implemented internally via savepoints which are discussed in Section 9.4 Savepoints and Subtransactions.

Three different SQL statements may be used to explicitly control when transactions begin and end: BEGIN TRANSACTION, COMMIT and ROLLBACK. This control is not absolute in all cases; a ROLLBACK statement always ends a transaction, but a BEGIN TRANSACTION doesn't necessarily start a transaction, nor does a COMMIT necessarily end one.

<begin_transaction> ::= BEGIN TRAN        -- all forms are equivalent
                      | BEGIN TRANSACTION
                      | BEGIN TRAN <transaction_name>
                      | BEGIN TRANSACTION <transaction_name>

<transaction_name>  ::= <identifier> -- not used for any purpose

<commit>            ::= COMMIT      -- all forms are equivalent
                      | COMMIT WORK
                      | COMMIT TRAN
                      | COMMIT TRANSACTION
                      | COMMIT TRAN <transaction_name>
                      | COMMIT TRANSACTION <transaction_name>

<rollback>          ::= ROLLBACK      -- all forms are equivalent
                      | ROLLBACK WORK
                      | ROLLBACK TRAN
                      | ROLLBACK TRANSACTION
                      | ROLLBACK TRAN <transaction_name>
                      | ROLLBACK TRANSACTION <transaction_name>

Tip: Don't bother with the transaction name parameters on the BEGIN TRANSACTION, COMMIT or ROLLBACK statements; they have no effect in SQL Anywhere. Also, the different formats for each statement are equivalent. The full syntax is shown here because these different formats sometimes appear in documentation and utility program output, and they often lead to unnecessary confusion.

SQL Anywhere has two modes of transaction control, called "chained mode" and "unchained mode".

In chained mode a transaction is implicitly started by any INSERT, UPDATE or DELETE statement, or any SELECT statement that acquires locks. This transaction ends when an explicit COMMIT or ROLLBACK statement is executed, or when the transaction fails.

In unchained mode a transaction may be explicitly started by a BEGIN TRANSACTION statement; such a transaction ends when an explicit COMMIT or ROLLBACK statement is executed, or when the transaction fails. If no BEGIN TRANSACTION statement is executed, each statement is run as its own transaction, with an implicit commit if it works and an implicit rollback if it fails. Here is a simple example of two UPDATE statements run in chained mode; the SET TEMPORARY OPTION CHAINED = 'ON' statement is used to clearly document that chained mode is in effect:

SET TEMPORARY OPTION CHAINED = 'ON';
UPDATE t1 SET non_key_1 = 'xxx' WHERE key_1 = 1;
UPDATE t1 SET non_key_1 = 'yyy' WHERE key_1 = 2;
COMMIT;

The transaction log file may be examined to determine when transactions begin and end; the dbtran.exe utility program can be used to translate the log file into readable SQL statements. Here is a command line that executes dbtran.exe, using the options -a to include all operations including uncommitted ones, -c to specify the connection parameters, -s to produce UPDATE statements in the ANSI format, and -y to overwrite the output file without confirmation. The final parameter is the file specification for the output text file, test9_log.sql: "%ASANY9%win32dbtran.exe" -a -c "DSN=test9" -s -y test9_log.sql

Here's what the output looks like for the UPDATE and COMMIT statements shown above; the BEGIN TRANSACTION entry shows that a transaction was started before the first UPDATE:

--BEGIN TRANSACTION-1001-0000402114
BEGIN TRANSACTION
go
--UPDATE-1001-0000402115
UPDATE DBA.t1
   SET non_key_1='xxx'
 WHERE key_1=1
go
--UPDATE-1001-0000402126
UPDATE DBA.t1
   SET non_key_1='yyy'
 WHERE key_1=2
go
--COMMIT-1001-0000402137
COMMIT WORK
go

Here are the same two UPDATE statements, run in unchained mode with no explicit COMMIT:

SET TEMPORARY OPTION CHAINED = 'OFF';
UPDATE t1 SET non_key_1 = 'xxx' WHERE key_1 = 1;
UPDATE t1 SET non_key_1 = 'yyy' WHERE key_1 = 2;

This time the dbtran.exe output shows two separate BEGIN TRANSACTION and COMMIT operations were performed:

--BEGIN TRANSACTION-1001-0000402237
BEGIN TRANSACTION
go
--UPDATE-1001-0000402238
UPDATE DBA.t1
   SET non_key_1='xxx'
 WHERE key_1=1
go
--COMMIT-1001-0000402249
COMMIT WORK
go
--BEGIN TRANSACTION-1001-0000402250
BEGIN TRANSACTION
go
--UPDATE-1001-0000402251
UPDATE DBA.t1
   SET non_key_1='yyy'
 WHERE key_1=2
go
--COMMIT-1001-0000402262
COMMIT WORK
go

Applications using unchained mode have no control over the design or scope of transactions unless they issue explicit BEGIN TRANSACTION statements. Here is the previous example, modified to take control and force both UPDATE statements to be included in one transaction:

SET TEMPORARY OPTION CHAINED = 'OFF';
BEGIN TRANSACTION;
UPDATE t1 SET non_key_1 = 'xxx' WHERE key_1 = 1;
UPDATE t1 SET non_key_1 = 'yyy' WHERE key_1 = 2;
COMMIT;

The dbtran.exe output for unchained mode using an explicit BEGIN TRANSACTION looks exactly the same as it did for first example using chained mode above; one BEGIN TRANSACTION, two UPDATE statements and a single COMMIT:

--BEGIN TRANSACTION-1001-0000402314
BEGIN TRANSACTION
go
--UPDATE-1001-0000402315
UPDATE DBA.t1
   SET non_key_1='xxx'
 WHERE key_1=1
go
--UPDATE-1001-0000402326
UPDATE DBA.t1
   SET non_key_1='yyy'
 WHERE key_1=2
go
--COMMIT-1001-0000402337
COMMIT WORK
go

Note: The BEGIN TRANSACTION entry in the dbtran.exe output serves to mark the point when a transaction was actually started, not the point when an explicit BEGIN TRANSACTION statement was executed. That might sound pedantic, but it's important when reading the output from dbtran.exe: an explicit BEGIN TRANSACTION statement in the application code may or may not correspond to a BEGIN TRANSACTION entry in the transaction log, regardless of the transaction mode.

Chained mode is sometimes called manual mode because it requires explicit COMMIT and ROLLBACK statements to mark the end of transactions. Unchained mode is sometimes called autocommit mode because each successful statement is automatically committed when no explicit BEGIN TRANSACTION statement has been executed. However, there are two kinds of autocommit mode: Server-side autocommit mode is the kind implemented by SQL Anywhere database engine when you set the CHAINED option 'OFF' to get unchained mode as described above, and you don't execute explicit BEGIN TRANSACTION statements. Transactions are started and ended automatically, inside the database engine, and there are no BEGIN TRANSACTION, COMMIT or ROLLBACK statements coming from the client side.

Client-side autocommit mode is implemented by the client database interface software such as ODBC and JDBC. When the interface "AutoCommit" flag is set, an explicit COMMIT statement is sent to SQL Anywhere after each INSERT, UPDATE and DELETE statement. If server-side autocommit is also in force, these extra COMMIT statements have no effect because by the time they arrive, SQL Anywhere has already done a commit. If server-side autocommit is not in force, then the COMMIT statements sent by client-side autocommit will have an effect.

Here's a table that shows what happens when a single UPDATE statement is issued by a PowerBuilder application using ODBC, under the four combinations of server-side and client-side autocommit settings. The first column shows the client-side SQLCA.AutoCommit setting used by the PowerBuilder application when connecting via ODBC to the database. The second column shows the server-side setting of the CHAINED option used by SQL Anywhere. The third column shows what SQL statements were actually sent across the client server interface from ODBC to SQL Anywhere. The fourth column shows what internal operations were performed by SQL Anywhere, as recorded in the transaction log:

Client-side      Server-side
ODBC AutoCommit  CHAINED Mode  Statements Sent  Operations Performed
===============  ============  ===============  =====================
     FALSE           OFF       UPDATE           Begin, update, commit
     TRUE            OFF       UPDATE, COMMIT   Begin, update, commit
     TRUE            ON        UPDATE, COMMIT   Begin, update, commit
     FALSE           ON        UPDATE           Update

Autocommit mode is in effect for the first three combinations shown above; it doesn't matter much if it's client-side or server-side autocommit, the important fact is that each database change is treated as a transaction by itself. With autocommit in effect it is impossible for an application transaction to span more than one INSERT, UPDATE or DELETE statement.

Tip: Never use autocommit. Always take explicit control of transaction design in your applications. Use the settings shown on the last line of the table above: always set the CHAINED option 'ON', set any client-side AutoCommit flag to FALSE, and explicitly execute a COMMIT or ROLLBACK statement when it is time to finish a transaction.

SQL Anywhere may or may not actually perform a commit operation when it executes a COMMIT statement; this depends on the current value of a builtin connection-level variable called @@TRANCOUNT. If @@TRANCOUNT is zero or 1 when a COMMIT statement is executed, SQL Anywhere will perform a commit; if @@TRANCOUNT is 2 or higher the COMMIT statement will be ignored. Here are the details of how @@TRANCOUNT gets changed and used:

@@TRANCOUNT is set to zero when a connection is started, and is set back to zero whenever a transaction is finished.

In unchained mode, each explicit BEGIN TRANSACTION statement increases @@TRANCOUNT by 1.

In chained mode, if @@TRANCOUNT is zero when an implicit transaction is been started, it isn't immediately changed, but the next explicit BEGIN TRANSACTION statement will set @@TRANCOUNT to 2. Subsequent BEGIN TRANSACTION statements increase @@TRANCOUNT by 1.

In chained mode, if an explicit BEGIN TRANSACTION statement is executed before a transaction is implicitly started, @@TRANCOUNT is set to 1; subsequent BEGIN TRANSACTION statements increase @@TRANCOUNT by 1.

In both chained and unchained modes, each COMMIT statement decreases @@TRANCOUNT by 1 until it reaches zero. If @@TRANCOUNT reaches zero when a COMMIT statement is executed, an actual commit operation is performed: all the transaction's database changes are made permanent, all the locks held by the transaction are released, and the transaction is ended. If @@TRANCOUNT does not reach zero when a COMMIT statement is executed, nothing more is done; as far as the outstanding database changes and locks are concerned, the COMMIT is ignored, and the transaction is still in progress.

In both chained and unchained modes, a ROLLBACK statement sets @@TRANCOUNT to zero, rolls back all the transaction's database changes, releases all the locks, and ends the transaction.

This happens regardless of the current value of @@TRANCOUNT.

The term "nested transaction" is sometimes used when @@TRANCOUNT rises to 2 or higher. That term is misleading, however, because only the outermost transaction has any meaning as far as database changes and locks are concerned. When @@TRANCOUNT rises to 2 or higher, a COMMIT statement does absolutely nothing except lower the @@TRANCOUNT value. A nested transaction implies that changes made in an inner transaction may be made permanent while changes made in the outer transaction are rolled back, and that simply is not possible in SQL Anywhere; there is no such thing as a nested transaction.

Tip: Don't use the BEGIN TRANSACTION statement at all. Use chained mode: let SQL Anywhere start each transaction implicitly, use explicit COMMIT and ROLLBACK statements to end each transaction, and don't disable COMMIT statements with extra BEGIN TRANSACTION statements.

Here is an example of a simple transaction; a parent row is inserted in the sales_order table in the ASADEMO database, and a corresponding child row is inserted in the sales_order_items table. The first INSERT starts the transaction. If both INSERT statements work OK, the transaction ends with a COMMIT; if a foreign key violation is detected, the SQLSTATE will be set to '23503' and the exception handler will end the transaction with a ROLLBACK:

BEGIN
   DECLARE @errormsg  VARCHAR ( 32767 );
   DECLARE error_23503 EXCEPTION FOR SQLSTATE '23503';

INSERT sales_order ( id, cust_id, order_date, fin_code_id, region, sales_rep )
      VALUES ( 1, 101, CURRENT DATE, 'r1', 'Eastern', 299 );

INSERT sales_order_items ( id , line_id, prod_id, quantity, ship_date )
      VALUES ( 1, 1, 999, 12, DATEADD ( DAY, 1, CURRENT DATE ) );

COMMIT;
   MESSAGE 'COMMIT OK.' TO CONSOLE;

EXCEPTION
   WHEN error_23503 THEN
      SET @errormsg = ERRORMSG();
      ROLLBACK;
      MESSAGE 'ROLLBACK after error.' TO CONSOLE;
      MESSAGE STRING ( 'ERRORMSG() = ', @errormsg ) TO CONSOLE;
END;

In this particular case, the prod_id value of 999 causes a foreign key violation and the transaction ends with a ROLLBACK; here's the output from the MESSAGE statements: ROLLBACK after error.

ERRORMSG() = No primary key value for foreign key 'ky_prod_id' in 
   table 'sales_order_items'

For more information about the tables in the ASADEMO database, see Section 3.10 in Chapter 3, Selecting. For more information about the BEGIN block and exception handlers, see Sections 8.2 and 8.3 in Chapter 8, Packaging.

Tip: When designing transactions, watch out for any SQL statement that performs a commit as a side effect. Any statement that updates the SQL Anywhere system catalog tables falls into this category, and if a transaction is in progress it will be ended. For example, statements like SET OPTION, GRANT and CREATE SYNCHRONIZATION USER statements all perform commits, and so do CREATE TABLE and CREATE INDEX when used on local permanent and global temporary tables. The LOAD TABLE statement also performs a commit, and so does TRUNCATE TABLE if the fast form is used; for more information about TRUNCATE TABLE see Section 5.7 in Chapter 3, Deleting. Some statements that affect schema don't perform commits, however; CREATE DATABASE doesn't, probably because it doesn't affect the current database, and neither does any statement involving a local temporary table. Also, the SET TEMPORARY OPTION statement doesn't do a commit so it's possible to change connection options while a transaction is in progress.