I have a questions about an SQL procedure. I'm replacing some code in the front end application with an SQL Procedure to allow other languages to use the same logic, shocking behaviour, I know. The procedure checks for the existence of a record, if it exists it increments it and returns the incremented value or inserts a new record where necessary and returns "1". Anyway, we have a couple of hundred users all potentially accessing this code, so I want to be absolutely certain that it was going to work how it's supposed to. 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? Is the row locked by the BEGIN TRANSACTION command in a way that will avoid concurrency issues? From the documentation, I gather that it does but I wanted to ask a person/people.
Also, is there a better/faster way for this code to work? Any tips would be appreciated.
The second/inner BEGIN/COMMIT section is probably unnecessary but, I'm new to SQL Procedures so I'm covering all bases.
DECLARE @IDValue BIGINT BEGIN TRANSACTION IF EXISTS(SELECT * FROM "DBA"."UniqueIds" WHERE "IdName" = @IdName) BEGIN BEGIN TRANSACTION UPDATE "DBA"."UniqueIds" SET "Id" = "Id" + 1 WHERE "IdName" = @IdName //--------HERE-------- SELECT @IDValue = "ID" FROM "DBA"."UniqueIds" WHERE "IdName" = @IdName COMMIT TRANSACTION END ELSE BEGIN INSERT INTO "DBA"."UniqueIds" ("IdName", "Id") VALUES(@IdName, 1) SET @IDValue = 1 END COMMIT TRANSACTION RETURN @IdValue
asked 16 Oct '13, 07:55
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.
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.
answered 17 Oct '13, 10:40
Just a few remarks:
As you are basically trying to build a primary key generator, there are several different approaches, and personally, I would recommend DEFAULT AUTOINCREMENTs (or SEQUENCES for v12 and above) over a separate "key value table" like you are using currently because the former should behave better with many concurrent transactions - confine this help topic that gives an overview:
That being said, I guess your construct is safe even on the lower default isolation levels (0 or 1, depending on the API used): SQL Anywhere prevents two concurrent transactions to modify the same row by using exclusive write locks - writers always block other writers on the same row. So in my understanding, when transaction A has reached the update statement, it will block all other transactions that are trying to update the same row until A has been committed (or rolled back). As the SELECT follows the UPDATE, it should be safe. - As I stated, that's a guess.
As to the nested transactions: Note that only the outermost COMMIT will make the changes to the database permanent - the inner COMMIT will only decrease the nesting level so it will have no influence in your sample (and note that the stored procedure may be called within an outer transaction itself, so even the COMMIT at the end of the SP's body might not make the changes permanent... And that may mean you will prevent other transactions from getting a new ID value for a potential long timespan, thus reducing concurrency (or even introducing deadlocks, in case transaction A (after it has got a new ID) would try to update a second row that has already been modified by transaction B before B tried to gather a new ID...).
It should also be mentioned that the database server can use "server-side autocommit", i.e. a mode in which each DML statement (even within stored procedures) is treated as a separate transaction...
Did anybody tell you transactions are easy?
How about this, the suggestion came up a couple of time to use shadow tables and GET_IDENTITY to solve the problem.
ALTER FUNCTION "DBA"."GetNextIdNew"(@IdName CHAR(20) ) RETURNS BIGINT AS BEGIN DECLARE @ReturnValue BIGINT DECLARE @TableExists INT DECLARE @TableName CHAR(40) DECLARE @OldIdValue INT SET @OldIdValue = 0 SET @TableName = 'Shadow_'||@IdName SELECT @TableExists = COUNT(1) FROM sysObjects WHERE "Name" = @TableName IF @TableExists = 0 BEGIN //MESSAGE 'Table Does Not Exist' TO CLIENT EXECUTE CreateShadowTable @IdName SELECT @OldIdValue = "Id" FROM UniqueIds WHERE "IdName" = @IdName IF @OldIdValue > 0 EXECUTE SA_RESET_IDENTITY @TableName, 'DBA', @OldIdValue SET @ReturnValue = GET_IDENTITY(@TableName, 1) END ELSE BEGIN //MESSAGE 'Table Exists' TO CLIENT SET @ReturnValue = GET_IDENTITY(@TableName, 1) END RETURN @ReturnValue END
The entire @TableExists = 0 section will only ever happen once for each Id and I'll go through each one in the old table and call this proc to create the shadow tables. It will also take care of any new Ids added this way, although that shouldn't be happening any more.
The create table proc is watcom procedure because I couldn't get it to work with t-sql, because I'm dumb: The IF NOT EXISTS is probably unnecessary, too.
ALTER PROCEDURE "DBA"."CreateShadowTable"(IN tablename char(20) ) BEGIN EXECUTE IMMEDIATE 'CREATE TABLE IF NOT EXISTS Shadow_' || tablename || ' ( IdValue INT PRIMARY KEY DEFAULT AUTOINCREMENT)' END
answered 24 Oct '13, 09:34
You can not be sure.
What I use is the following. ( Probably this is also not safe :-) But has worked for me many years )
BEGIN ATOMIC select ORDERKEY_KEY, ORDERKEY_LASTCHANGED into nKey, tsDate from "AFAdmin"."ORDERKEY" with (holdlock) where ORDERKEY_TYPE = cORDERKEY_TYPE; if nKey is null -- insert else -- update end if; END
What you also have to be aware of is that these building your own PK Generator is Top 38 on the list How To Make SQL Anywhere Slow.
In newer Versions of SQLA you should try to create a sequences. Creating a sequence
You did not mention which version of SQLA you were using?
If you are using version 11 or above then I would suggest you using a MERGE statement.
Example: I believe that something like this will do everything you need (Note: I am using WATCOM SQL syntax):
begin set @IDValue = 1; -- assume we are going to insert merge into "DBA"."UniqueIds" as ov( "IdName", "ID" ) using ( select @idName as "IdName", 1 as "ID" ) nv on ov."IdName" = nv."IdName" when matched then update set ov."ID" = ov."ID" + 1, @IDValue = ov."ID" -- extract the ID value from the table when not matched then insert; commit; return @IDValue; end;
The way that the above MERGE statement works is this: We construct a row "nv" (for "New Value") and then merge that row into the existing "ov" (for "Old Value") that is your UniqueIds table. If the new value IdName matches a rows in old value (aka UniqueIds) then we update the row setting ov."ID" = ov."ID" + 1; else when there is not a match then we simply insert the new row into the UniqueIds table.
Regarding your question about locking: A row is not locked within a transaction until it is selected (depending on your isolation level) or updated. For example, if you are using isolation level 0 then no read locks are taken.
The code you show is extremely dangerous. Its operation depends on whether the connection is operating in CHAINED = 'ON' or CHAINED = 'OFF' mode. Its operation also depends on whether a transaction is already in progress when the block is entered or not. There is no way to inspect just this code, and determine what it does.
It is highly recommended you avoid using the BEGIN TRANSACTION statement altogether, and use the CHAINED = 'ON' mode (which is the default except for Open Client and jConnect connections, where you have to remove or modify or override the effect of the evil sp_tsql_environment system procedure).
Avoiding BEGIN TRANSACTION implies you will also be avoiding use of nested COMMIT statements which are misleading to the reader. In fact, nested COMMIT statements don't do anything at all and there is no such thing as a nested transaction.
If this code is used to generate unique auto-incrementing primary key values, then switch to using DEFAULT AUTOINCREMENT on the column (like Volker said), and do not specify an explicit value when doing the INSERT. SQL Anywhere will generate unique values with no locks that cause blocks, and if you need to know what value got generated just SELECT @@IDENTITY after the INSERT.