Chapter 37 – SQL Transaction Concurrency¶
Note
You are reading a digital copy of SQL-99 Complete, Really, a book that documents the SQL-99 standard.
The book and the standard does not reflect the features of CrateDB, we are just publishing it as a service for the community and for reference purposes.
On the one hand, CrateDB does not implement the SQL-99 standard thoroughly, on the other hand, it extends the standard for implementing cluster features and others.
For more information specific to CrateDB, check out the CrateDB Reference documentation.
Although there is never more than one current SQL transaction between your DBMS and your application program at any point in time, the operational problem – although perhaps we should call it a “challenge” or an “opportunity” – is that many DBMSs run in multi-tasking or multi-user scenarios. These may range from the fairly simple (for example, an MS-Windows background task running in conjunction with a foreground task) to the fairly complex (for example a heterogeneous mix of computers connected over a network via TCP/IP). The problem/challenge/opportunity has grown larger in recent years, with the demand for “24x7” (twenty-four hour by seven-day) accessibility, and with the demand for DBMS servers that can operate on Internet hosts (especially with WindowsNT and Linux operating systems).
In SQL, the primary unit of work is the “transaction”, as we saw in the previous chapter. The interesting difficulties lie with transaction concurrency, which we define as:
Concurrency. The running together of two transactions, which may access the same database rows during overlapping time periods. Such simultaneous accesses, called collisions, may result in errors or inconsistencies if not handled properly. The more overlapping that is possible, the greater the concurrency.
The proper handling of collisions requires some work on the part of the application programmer. It is possible to leave the whole matter in the hands of the DBMS, but that would almost certainly lead to performance which everyone would call unacceptable. Therefore your requirement for this part of the job is to understand how errors or inconsistencies can arise during collisions, to use the somewhat paucous SQL options which can increase concurrency, and to help the DBMS* along with a variety of settings or application plans.
* Though we use the singular word “DBMS”, we note once again that there may in fact be several co-operating agencies responsible for transaction management.
Table of Contents
Isolation Phenomena¶
What sort of errors and inconsistencies can creep in during concurrent operations? Database groupies generally use four categories, which they call – in order by seriousness – Lost Update, Dirty Read, Non-Repeatable Read and Phantom.
In the descriptions that follow, the conventions are that “Txn#1” and “Txn#2”
are two concurrent transactions, that “change” has its usual sense of “an
INSERT or UPDATE or DELETE”, and “read” means “FETCH” or some
close equivalent. The charts are time lines with time points, so that events
shown lower down in the chart are taking place later in time.
LOST UPDATE
Txn#1 |
Txn#2 |
|---|---|
… |
Read |
Read |
… |
Change |
… |
… |
Change |
Commit |
… |
… |
Commit |
The “lost update” is the change made by Txn#1. Since Txn#2 makes a later change, it supersedes Txn#1’s change. The result is as if Txn#1’s change never happened.
DIRTY READ
Txn#1 |
Txn#2 |
|---|---|
Read |
… |
Change |
… |
… |
Read |
Rollback |
… |
… |
Rollback |
Here the key is that Txn#2 “reads” after Txn#1 “changes”, and so Txn#2 “sees” the new data that Txn#1 changed to. That change was ephemeral though: Txn#1 rolled back the change. So Txn#1’s change really never happened, but Txn#2 based its work on that change anyway. The old name for the Dirty Read phenomenon is “uncommitted dependency”.
NON-REPEATABLE READ
Txn#1 |
Txn#2 |
|---|---|
… |
read |
read |
… |
… |
change |
read |
… |
… |
Commit |
Commit |
… |
The supposition here is that Txn#1 will “read” the same row twice. The second time, though, the values in the row will be different. This is by no means as serious an inconsistency (usually) as Lost Update or Dirty Read, but it does certainly break the requirements of an ACID transaction.
PHANTOM
Txn#1 |
Txn#2 |
|---|---|
|
… |
… |
|
… |
|
|
… |
This now-you-don’t-see-it-now-you-do phenomenon is one that often slips through, especially with older or dBASE-like DBMSs. The reason is that DBMSs might notice concurrent access to rows, but fail to notice concurrent access to the paths to those rows. Phantoms can affect transactions which contain at least two <search condition>s which in some way overlap or depend on one another. Phantoms are rare and are usually tolerable, but can cause surprise errors because even some good DBMSs let them through, unless you take explicit measures to signal “no phantoms please”.
Pessimistic Concurrency: LOCKING¶
The most common and best-known way to eliminate some or all of the transaction concurrency phenomena is locking. Typically, a lock works like this:
Txn#1 |
Txn#2 |
|---|---|
“LOCK” the desired object |
… |
… |
Wait: desired object is locked |
read and/or change |
… |
read and/or change |
… |
Commit (which releases locks) |
… |
… |
“LOCK” the desired object |
Here, the Object being locked might be a Column, a row, a page, a Table or the entire database. Incidentally, when a lock is on a Column, locks are said to be “finely granular”; when a lock is on a page or Table or database, locks are said to be “coarsely granular”. DBMSs with coarse-granularity locking have less concurrency (because a lock on row#1 causes an unnecessary lock on other rows as well), but are efficient despite that because: the coarser the granularity, the fewer locks exist, and therefore searching the list of locks is quicker. At this moment, it appears that the majority of important DBMSs support locking by row, with some (non-standard) optional syntax that allows locking by Table.
A lock is much like a reservation in a restaurant. If you find that your desired seat has already been taken by someone who came before you, you must either wait or go elsewhere.
Usually an SQL DBMS supports at least two kinds of locks: “shared locks” and “exclusive locks”. A shared lock exists because there is nothing wrong with letting two transactions read the same row; concurrency can only cause trouble if one transaction or the other is updating. Therefore, at retrieval time, a shared lock is made, and this shared lock does not block other transactions from accessing the same row (with another retrieval). At change time, the shared lock is upgraded to an exclusive lock, which blocks both reads and writes by other transactions. The use of different kinds of locks is something that distinguishes an SQL DBMS from a DBMS that depends on the operating system (operating systems like MS-DOS support exclusive locks only).
The famous irritant with a lock-based concurrency resolution mechanism is the “deadlock” (or deadly embrace), which goes like this:
Txn#1 |
Txn#2 |
|---|---|
Lock Row #1 |
… |
… |
Lock Row #2 |
Attempt to Lock Row #2 – |
… |
WAIT, because it’s locked |
|
… |
Attempt to Lock Row #1 -- |
WAIT, because it’s locked |
|
WAIT |
WAIT |
Since Txn#1 is waiting for Txn#2 to release its lock, but Txn#2 is waiting for Txn#1 to release its lock, there can be no progress. The DBMS must detect situations like this and force one transaction or the other to “rollback” with an error.
Locking is reliable and popular. However, it is sometimes criticized for being based on an excessively pessimistic assumption: that something which you read could be something that you will change. The result is a profusion of “shared locks”, the great majority of which turn out to be unnecessary because only a relatively small number of rows are actually updated.
Optimistic Concurrency: TIMESTAMPING¶
There are several ways to control concurrency without locking. The most common ones can be grouped together as the “optimistic assumption” ways, and the most common of those ways is timestamping. With timestamping, there are no locks but there are two situations which cause transaction failure:
If a younger transaction has “read” the row, then an attempt by an older transaction to “change” that row will fail.
If a younger transaction has “changed” the row, then an attempt by an older transaction to “read” that row will fail.
The general effect of these rules is that concurrency is high, but failure is frequent. Indeed, it is quite possible that a transaction will fail many times. But what the heck, one can put the transaction in a loop and keep retrying until it goes through.
Some DBMSs enhance the concurrency further by actually “reading” a row which has been changed by another transaction, and deciding whether the change is significant. For example, it often happens that the same Column is being updated so that it has the same value for both transactions. In that case, there may be no need to abort.
Most optimistic concurrency mechanisms are not particularly good for detecting Non-Repeatable Reads or Phantoms.
SET TRANSACTION statement¶
By now, we’ve come to expect that SQL statements aren’t used to specify
methods. Instead, they state what the requirements are. That’s the idea behind
the SET TRANSACTION statement. It tells the DBMS – somewhat indirectly –
what sort of concurrency phenomena are intolerable for the next transaction,
but it does not say how they are to be prevented. That detail is left up to the
DBMS itself – that is, the choice of concurrency protocol (locking,
timestamping or some other method) is implementation-defined. The required
syntax for the SET TRANSACTION statement is:
SET [ LOCAL ] TRANSACTION <transaction mode> [ {,<transaction mode>}... ]
<transaction mode> ::=
<transaction access mode> |
<isolation level> |
<diagnostics size>
<transaction access mode> ::=
READ ONLY | READ WRITE
<isolation level> ::=
ISOLATION LEVEL
{READ UNCOMMITTED | READ COMMITTED | REPEATABLE READ | SERIALIZABLE}
<diagnostics size> ::=
DIAGNOSTICS SIZE <number of conditions>
<number of conditions> ::= <simple value specification>
The SET TRANSACTION statement sets certain characteristics for the next
transaction. There are three options, any or all of which can be set by a
single SET TRANSACTION statement.
The first transaction characteristic is its access mode: a transaction can
either be a READ ONLY transaction or a READ WRITE transaction. The
second transaction characteristic is its isolation level: a transaction can
either allow READ UNCOMMITTED, READ COMMITTED, REPEATABLE READ or
SERIALIZABLE operations. The final transaction characteristic is the size
of its diagnostics area: you set this to the number of conditions you want your
DBMS to be able to provide you with information on (it must be at least one).
The <diagnostics size> is a transaction characteristic which has nothing to do
with concurrency, so we won’t discuss it further here – look for it in our
chapters on binding styles. That leaves us with the choice of specifying READ
ONLY versus READ WRITE, and the choice of specifying one of: READ
UNCOMMITTED, READ COMMITTED, REPEATABLE READ or SERIALIZABLE for
a transaction. Here’s some example SQL statements:
SET TRANSACTION
READ WRITE
ISOLATION LEVEL REPEATABLE READ;
SET TRANSACTION
READ ONLY
ISOLATION LEVEL READ UNCOMMITTED;
As the name suggests, SET TRANSACTION is only good for setting the
characteristics of one transaction (though there are a few exceptions to this
rule when we add the optional <keyword> LOCAL, a SQL3 feature). Unless
you’re using the SQL3 START TRANSACTION statement (which we’ll discuss
later in this chapter), the SET TRANSACTION statement should precede all
other statements in a transaction. If you don’t specify it, the default
situation is:
SET TRANSACTION
READ WRITE
ISOLATION LEVEL SERIALIZABLE;
Access Mode¶
If the transaction’s isolation level is READ UNCOMMITTED, then READ
ONLY is the default (and only legal) access mode option. For all other
isolation levels, either READ ONLY or READ WRITE are legal options, and
the default is READ WRITE.
The declaration READ ONLY tells the DBMS that all statements in the
upcoming transactions will be “read” statements: they only read SQL data, they
don’t make any changes. The declaration READ WRITE tells the DBMS that
there may be either “read” or “change” statements in the upcoming transaction.
Note
Changes to TEMPORARY Tables don’t count as “changes”, since
TEMPORARY Tables aren’t shared between different transactions anyway.
So regardless of what you do with TEMPORARY Tables, as long as you
make no changes to SQL-data or persistent SQL Objects (e.g.: Schemas,
Tables, Domains and so on), you can declare a transaction’s access mode
to be READ ONLY.
There are no guarantees that specifying READ ONLY will do any good at all,
but it certainly won’t hurt – and it might result in performance gains. Here’s
why: If your DBMS sees that all transactions are READ ONLY, then it doesn’t
have to set any locks at all – no isolation phenomena can arise when all jobs
are doing nothing but reading. If only one transaction is READ ONLY, there
is still a good strategy available: the DBMS can make a temporary copy of the
Tables that you SELECT from (or at least of the rows in the result sets).
Following that, all FETCH statements are operating on the temporary copy,
instead of on the original Table, and therefore collisions are impossible.
Typical application situations where READ ONLY is called for include:
report writers, screen displayers, file dumps. The option might have a
particularly good effect if the transaction contains statements which contain
set functions.
Isolation Level¶
The <isolation level> characteristic you specify in a SET TRANSACTION
statement determines the degree of “isolation” of the upcoming transaction.
This effectively means that the value you choose will tell your DBMS which
concurrency phenomena are tolerable or intolerable for the transaction. It’s up
to the DBMS to decide how precisely it will follow your instruction – the
Standard allows it to upgrade the specification (but never to downgrade it,
you’re always guaranteed at least the isolation level you’ve asked for). For
example, your DBMS could take a READ UNCOMMITTED specification and set the
next transaction’s isolation level to SERIALIZABLE (a higher level of
isolation) instead. But since your DBMS cannot downgrade the specification,
there is no harm in setting the <isolation level> as precisely as possible.
READ UNCOMMITTED¶
READ UNCOMMITTED is the lowest level of transaction isolation. If you
specify READ UNCOMMITTED, you are risking that the transaction – no matter
what it is doing – might deliver a “wrong” answer. Because it is always
unacceptable for the database itself to contain wrong data, it is illegal to
execute any “change” statements during a READ UNCOMMITTED transaction. That
is, READ UNCOMMITTED implies READ ONLY.
READ UNCOMMITTED means “allow reading of rows which have been written by
other transactions, but not committed” – so Dirty Reads, Non-Repeatable Reads
and Phantoms are all possible with this type of transaction. However, Lost
Update is not possible for the simple reason that, as already stated, changes
of any kind are illegal. Lost Updates are, in fact, prevented in all the
standard SQL isolation levels. The concurrency level is as high as it can be –
in a locking situation, no locks would be issued and no locks would be checked
for. We could say that, for this transaction, concurrency checking is turned
off.
The READ UNCOMMITTED level is a good choice if (a) the transaction is
usually slow, (b) errors are likely to be small and (c) errors are likely
to cancel each other out. The most certain example of such a situation is a
single SELECT statement containing a set function such as COUNT(*). Any
“report” where the user isn’t likely to care about details, is also a good
candidate. You tolerate a huge degree of error every time they use a search
engine on the World Wide Web.
READ COMMITTED¶
READ COMMITTED is the next level of transaction isolation. READ
COMMITTED means “allow reading of rows written by other transactions only
after they have been committed” – so Non-Repeatable Reads or Phantoms are both
possible with this type of transaction, but Update and Dirty Read are not. The
READ COMMITTED level allows for a reasonably high level of concurrency –
in a locking situation, shared locks must be made, but they can be released
again before the transaction ends. For any “optimistic” concurrency resolution
mechanism, READ COMMITTED is the favored level. Conventional wisdom says
that concurrency based on optimistic assumptions gets very slow if the
isolation level is high.
The READ COMMITTED level is always safe if there is only one SQL statement
in the transaction. Logic tells us that there will be no Repeatable Read errors
if there is only one “read”.
REPEATABLE READ¶
REPEATABLE READ is the next level of transaction isolation. By specifying
REPEATABLE READ, you are saying to your DBMS: don’t tolerate Non-Repeatable
Reads (or, for that matter, Dirty Reads or Lost Updates) for the next
transaction. Phantoms continue to be tolerated. With REPEATABLE READ, the
concurrency drops sharply. In a locking situation, the DBMS will be obliged to
put a “shared lock” on every row it fetches, and keep the lock throughout the
transaction. From the DBMS’s point of view, the difference between this level
and the previous one is: with READ COMMITTED the locks can be released
before the transaction ends, with REPEATABLE READ they can’t be.
The REPEATABLE READ level is what most programmers prefer for
multi-statement transactions that involve “changes”. Examples would be bank
transfers or library book checkouts.
SERIALIZABLE¶
SERIALIZABLE is the highest level of transaction isolation. At the
SERIALIZABLE isolation level, no concurrency phenomena – even Phantoms –
can arise to plague the programmer. This is the lowest level for concurrency.
Often the DBMS must respond by coarsening the granularity, and locking whole
Tables at once. Because the result is likely to be poor performance, this is
usually not the isolation level that the DBMS vendor manuals suggest – they’ll
steer you to REPEATABLE READ. But SERIALIZABLE is the default isolation
level in standard SQL and it’s the only level that all standards-compliant
vendors are guaranteed to support (theoretically a vendor could ignore the
lower levels and just “upgrade” all <isolation level> specifications to
SERIALIZABLE).
Since the SERIALIZABLE level won’t tolerate Phantoms, it’s especially
indicated for transactions which contain multiple SELECT statements, or for
when you don’t know what the statements will be, as in dynamic SQL. It is the
only level which assures safe, error-free transactions every time. If your
application consists wholly of short (i.e. fast-executing) statements which
affect only a few records at a time, don’t get fancy – leave everything at the
default SERIALIZABLE level. Nevertheless, we suspect that SERIALIZABLE
is used somewhat more often than appropriate. The choice of isolation level is
something you should at least give a moment’s thought to, on a case-by-case
basis.
The word SERIALIZABLE reflects the idea that, given two overlapping
transactions – Txn#1 and Txn#2 – we can get the same results as we would get
if the transactions were “serial” rather than “overlapping” – that is, if
Txn#1 followed Txn#2, or Txn#2 followed Txn#1, in time the end result would be
the same. This does not mean that the transactions are replayable, though. The
DBMS can only take responsibility for data stored in the database. It can not
guarantee that a transaction’s statements are replayable if the parameter
values from the host (application) program change or if the SQL statements
contain niladic functions such as CURRENT_DATE, CURRENT_TIME,
CURRENT_TIMESTAMP or CURRENT_USER.
SET LOCAL TRANSACTION¶
If your DBMS supports transaction that may affect more than one SQL-server, you
can use the <keyword> LOCAL (new to SQL with SQL3) to set the transaction
characteristics for the next local transaction. If you omit LOCAL, you’re
instructing your DBMS to set the transaction characteristics for the next
transaction executed by the program, regardless of location. If LOCAL is
specified, then you may not also specify the size of the transaction’s
diagnostics area.
Certain errors can arise when you try to use the SET TRANSACTION statement:
If you try to issue it when a transaction has already begun,
SET TRANSACTIONwill fail: your DBMS will return theSQLSTATE error 25001 "invalid transaction state-active SQL-transaction".If you issue it when there are holdable-cursors still open from the previous transaction and the isolation level of that transaction is not the same as the isolation level you’re specifying for the next transaction,
SET TRANSACTIONwill fail: your DBMS will return theSQLSTATE error 25008 "invalid transaction state-held cursor requires same isolation level".If you issue
SET LOCAL TRANSACTIONand your DBMS doesn’t support transactions that affect multiple SQL-servers,SET LOCAL TRANSACTIONwill fail: your DBMS will return theSQLSTATE error 0A001 "feature not supported-multiple server transactions".
If you want to restrict your code to Core SQL, don’t use SET LOCAL
TRANSACTION and don’t set any transaction’s isolation level to anything but
SERIALIZABLE.
START TRANSACTION Statement¶
In SQL3, you don’t need the SET TRANSACTION statement, except for setting
the characteristics of local transactions. Instead, you can use the START
TRANSACTION statement to both initiate the start of a new transaction and to
set that transaction’s characteristics. The required syntax for the START
TRANSACTION statement is:
START TRANSACTION <transaction mode> [ {,<transaction mode>}...]
<transaction mode> ::=
<isolation level> |
<transaction access mode> |
<diagnostics size>
Each of the transaction characteristics – <isolation level>, <transaction
access mode> and <diagnostics size> – works the same, and has the same options
as those we discussed for the SET TRANSACTION statement. The only real
difference between the two statements is that SET TRANSACTION is considered
to be outside of a transaction – it defines the characteristics for the next
transaction coming up – while START TRANSACTION is considered as the
beginning of a transaction – it defines the characteristics of the transaction
it begins.
One other thing of note: The characteristics of a transaction that you start
with a START TRANSACTION statement are as specified in that statement –
even if the specification is implicit because you leave out one or more of the
transaction mode options. That is, even if one or more characteristics are
omitted from START TRANSACTION, they will default to the appropriate values
– they will not take on any non-default characteristics even if you issue a
SET TRANSACTION statement that includes other specifications for those
characteristics just before you begin the transaction.
If you want to restrict your code to Core SQL, don’t use the START
TRANSACTION statement.
Special Problems¶
The SQL-Schema change statements (CREATE, ALTER, DROP, GRANT,
REVOKE) require drastic solutions to ensure concurrency. Typically, a DBMS
must lock all the INFORMATION_CATALOG descriptors. That means that
SQL-Schema statements cannot run concurrently with anything else.
The INSERT statement is more concurrent than the UPDATE or DELETE
statements. By definition, any “new” row is a not-yet-committed row and
therefore is invisible to all other transactions (except transactions which are
running at the READ UNCOMMITTED isolation level). Where INSERT will
have problems is in cases where the target Table includes a Column with a
serial data type (which is non-standard) – in such cases, the DBMS cannot
guarantee that the value will truly be serial unless the isolation level of all
transactions is SERIALIZABLE.
It is fairly easy to lock a “row” in a Base table, since there is usually some fixed physical file address which corresponds to the row. However, an “index key” in an index file is a slipperier thing. Index keys can move, as anyone who has studied B+trees can tell you. So, when you update a row, remember that you may be locking not only the row, but an entire page of an index.
Regardless of isolation level, none of the isolation phenomena should occur
during: (a) implied reading of Schema definitions (that is, while finding
Objects during the “prepare” phase … as opposed to “explicit” reading, which
is what happens if you SELECT ... FROM INFORMATION_SCHEMA.<Table name>);
(b) processing of integrity Constraints (but not Triggers). The implication
is that standard SQL DBMSs have to lock the whole database when preparing a
statement, and at the end of a statement’s execution phase. This is a difficult
requirement.
Transactions and Constraint Checking¶
There is one other SQL transaction management statement: SET CONSTRAINTS.
We talked about SET CONSTRAINTS a bit in our chapter on constraints and
assertions; basically, it allows you to tell the DBMS when you want it to check
any deferrable Constraints that were affected during a transaction. A
transaction always begins with an initial default constraint mode for every
Constraint that is used during the course of the transaction. A Constraint’s
initial constraint mode, specified when the Constraint was created, determines
when the Constraint will be checked for violation of its rule: immediately at
the end of each SQL statement executed, or later on in the transaction. The
SET CONSTRAINTS statement is used to specify a different constraint mode
for one or more DEFERRABLE Constraints during the course of a transaction.
SET CONSTRAINTS Statement¶
The required syntax for the SET CONSTRAINTS statement is:
SET CONSTRAINTS <Constraint name list> {DEFERRED | IMMEDIATE}
<Constraint name list> ::=
ALL |
<Constraint name> [ {,<Constraint name>}... ]
Remember that all Constraints and Assertions are defined with a deferral mode
of either NOT DEFERRABLE or DEFERRABLE. A deferral mode of NOT
DEFERRABLE means that the Constraint must be checked for violation as soon as
the SQL statement that affects it is executed – this type of Constraint can’t
be affected by the SET CONSTRAINTS statement and we’ll ignore it here. A
deferral mode of DEFERRABLE, on the other hand, allows you to specify when
you want your DBMS to check the Constraint for violation – the choices are at
statement end or at transaction end – and such Constraints may be affected by
the SET CONSTRAINTS statement.
Every transaction has a constraint mode for each integrity Constraint affected
by that transaction. If the Constraint is a NOT DEFERRABLE Constraint, the
constraint mode is always IMMEDIATE. But if the Constraint is a
DEFERRABLE Constraint, then its constraint mode at the beginning of a
transaction will either be IMMEDIATE or DEFERRED, depending on the way
you defined the Constraint – if you defined it as DEFERRABLE INITIALLY
IMMEDIATE, the Constraint’s constraint mode at transaction start will be
IMMEDIATE and if you defined it as DEFERRABLE INITIALLY DEFERRED, the
Constraint’s constraint mode at transaction start will be DEFERRED. You can
use the SET CONSTRAINTS statement to change these default constraint mode
settings for one or more Constraints – but only for the duration of the
transaction that you use it in. (You can actually issue SET CONSTRAINTS at
two different times. If you issue it during a transaction, you’re changing the
constraint mode of only those Constraints that are affected during that same
transaction. If you issue it when there is no current transaction, you’re
changing the constraint mode of only those Constraints that are affected during
the very next transaction.)
The SQL statement:
SET CONSTRAINTS ALL IMMEDIATE;
has the effect of setting the constraint mode of every DEFERRABLE
Constraint to IMMEDIATE. IMMEDIATE means that the Constraints must be
checked for violation after the execution of every SQL statement – including
after SET CONSTRAINTS.
The SQL statement:
SET CONSTRAINTS ALL DEFERRED;
has the effect of setting the constraint mode of every DEFERRABLE
Constraint to DEFERRED. DEFERRED means that the Constraints should not
be checked for violation after the execution of every SQL statement, but should
instead be checked at some later time, but no later than the end of the current
transaction. (COMMIT includes an implied SET CONSTRAINTS ALL IMMEDIATE
statement, so that all Constraints are checked at transaction end.)
If you provide a list of <Constraint name>s instead of using the <keyword>
ALL, the constraint mode of only those Constraints is affected. For
example, if you have these Constraints:
Constraint_1 DEFERRABLE INITIALLY IMMEDIATE
Constraint_2 DEFERRABLE INITIALLY IMMEDIATE
Constraint_3 DEFERRABLE INITIALLY IMMEDIATE
Constraint_4 DEFERRABLE INITIALLY DEFERRED
Constraint_5 DEFERRABLE INITIALLY DEFERRED
Constraint_6 DEFERRABLE INITIALLY DEFERRED
and you issue this SQL statement:
SET CONSTRAINTS Constraint_1,Constraint_3,Constraint_4 DEFERRED;
the result is that Constraint_1, Constraint_3, Constraint_4,
Constraint_5 and Constraint_6 will all have a constraint mode of
DEFERRED and Constraint_2 will continue to have a constraint mode of
IMMEDIATE.
All Constraints with a constraint mode of IMMEDIATE are checked for
violation at SQL statement. Constraints with a constraint mode of deferred, on
the other hand, are not checked until transaction end. This let’s you do
operations which temporarily puts your data into an unsound state and can thus
be very useful.
Dialects¶
Some DBMSs support locking, some support timestamping, some support both.
Beyond that fundamental point of difference, there are many
implementation-dependent features. For example: the granularity (by Column or
row or page or Table or database), the number of distinct isolation levels that
are actually supported (remember that pseudo-support is possible by simply
upgrading to the next level), and whether to support SQL-92 or SQL3 syntax.
Regrettably, most DBMSs are still idiosyncratic with respect to support for the
SET TRANSACTION statement.
IBM’s DB2 and its imitators have explicit statements for locking Tables: LOCK
TABLE <name> IN {EXCLUSIVE|SHARED} MODE;. This reduces the total number
of locks, and actually enhances concurrency if your intention is to access
every row in the Table.
ODBC specifies a variety of options which essentially are options for row identifiers used in scrolling. There are also CLI-specific commands for setting transaction isolation or other concurrency-related characteristics.
Oracle has Locks Display and other utilities that help administrators to monitor concurrency.