In this post, originally written by Glenn Paulley and posted to sybase.com in July of 2011, Glenn talks about the trade-offs when using different isolation levels and updating data in the database.
Routinely, application developers trade off serializable transaction semantics in favour of better execution time performance by limiting the potential for lock contention. Few and far between are applications that execute at ISO/ANSI SQL isolation level 3, SERIALIZABLE
. Indeed, the SQL Anywhere default isolation level is zero - READ UNCOMMITTED
- except for JDBC applications, where the default is READ COMMITTED
.
At the READ UNCOMMITTED
isolation level with SQL Anywhere, only schema locks and write row locks are acquired by a transaction during its operation; read row locks are never acquired, and so at READ UNCOMMITTED
write transaction do not block read transactions. On the flip side, however, SQL Anywhere does not guarantee semantics at the READ UNCOMMITTED
isolation level. To use the common parlance, you get what you pay for. With many applications, the risk and/or impact of uncommitted rows is low; sometimes this can lead to complacency about what READ UNCOMMITTED
really means. In this post, I want to illustrate an example where the impact is more obvious.
In the SQL Anywhere Version 5.5 release, circa 1997, we introduced full support for set-level UPDATE
statements that could modify columns that were part of a table's PRIMARY KEY
, UNIQUE
constraint, or part of a unique index, in support of the ISO SQL/1992 standard which was the current SQL standard at that time. To illustrate, suppose we have the following table:
CREATE TABLE updkey ( a INTEGER PRIMARY KEY, b INTEGER UNIQUE, c VARCHAR(500) )
populated by the following INSERT
statement:
INSERT INTO updkey(a,b,c) SELECT row_num, row_num, 'test string' FROM rowgenerator WHERE row_num
In this example, we desire to renumber the "b" values of all ten rows using a single atomic statement. We can do so as follows:
UPDATE updkey SET b = 11-b, c = 'New value'
Processing the UPDATE
statement row-by-row clearly won't do, since the update of any single row in the updkey table will immediately violate the uniqueness constraint on column "b". (Aside: you may be thinking the WAIT_ON_COMMIT
connection option might help here, but WAIT_ON_COMMIT
only affects referential integrity constraints, not uniqueness constraints). Consequently, Version 5.5 of SQL Anywhere provided a different mechanism to perform the update, and it has implications for lower levels of concurrency control, as we shall see.
When the SQL Anywhere server processes an UPDATE
or MERGE
statement and encounters a uniqueness constraint violation on a primary key, unique index, or unique constraint, the server automatically creates an unnamed "hold" temporary table to temporarily store the problematic rows. The temporary table contains both the before and after values of a row, so that AFTER
row and AFTER
statement triggers can work correctly. Processing the rows is done row-by-row as follows:
DELETE
triggers are fired for this temporary deletion.AFTER
row triggers are fired for this row.
Once all of the rows have been processed, any deleted rows that have been copied to the hold temporary table are then re-inserted into the base table, with the modified values from the UPDATE
or MERGE
statement. The order in which the rows from the hold temporary table are processed is not guaranteed. If the re-insertion of any of the saved rows still causes a uniqueness violation, then the entire UPDATE
or MERGE
statement is rolled back, and the uniqueness constraint violation is reported back to the application.
Only if all row modifications are successful are any AFTER
statement triggers fired for the request.
The effect of deleting rows during the execution of an INSERT
or MERGE
statement can impact the results of
AFTER
row trigger that is fired for the UPDATE
or MERGE
statement that initiated the action; orSERIALIZABLE
or SNAPSHOT
isolation levels.The semantics of this processing of set-level update operations is somewhat counter-intuitive, since on the surface you might expect that another connection concurrently querying the table would either "see" the old row values, or the new row values. However, with set-level update operations on tables with uniqueness constraints, there is the possibility that other connections will not see a particular row at all, depending on the isolation level being used. If the other connection is executing at the SERIALIZABLE
isolation level, it will block until the transaction doing the update issues a COMMIT
or ROLLBACK
. If the other connection is executing at SNAPSHOT
isolation, that transaction will continue to see the original values of the modified rows for the duration of that transaction.
This detailed, complex behaviour has previously been undocumented. It will appear in the standard documentation in the next major release of SQL Anywhere.
You must be a registered user to add a comment. If you've already registered, sign in. Otherwise, register and sign in.
User | Count |
---|---|
20 | |
10 | |
9 | |
7 | |
7 | |
7 | |
6 | |
6 | |
5 | |
5 |