So far in this book, we have assumed that you are the only user of the database. If you do the examples and exercises at home, that assumption is probably correct. But if you work with SQL in your company, for example, the odds are good that you share the database with many other users. We call this multi-user usage as opposed to single-user usage. Actually, in a multi-user environment, you should not be aware that other users are accessing the database concurrently because SQL hides this from you as much as possible. Still, the following question might occur to you: What will happen if I access a row that is already in use by someone else? In short, that question is the subject of this chapter. We start with the description of a concept that forms the basis of multi-user usage: the transaction (also called unit of work). The concepts savepoint, lock, deadlock, and isolation level also are discussed, and we consider the LOCK TABLE statement.
In this chapter, we look inside SQL. If that does not interest you, you can skip this chapter. For those who will develop real-life applications with SQL, we recommend studying this chapter carefully.
What exactly is a transaction? In this book, we define a transaction as a set of SQL statements that are entered by one user and that are ended by specifying whether all changes are to be made permanent or rolled back (or undone). By a “change,” we mean each UPDATE, DELETE, and INSERT statement. SQL statements entered by different users cannot belong to the same transaction. At the end of this section, we explain why we might want to undo changes.
Many products for interactive SQL are set up in such a way that, first, each SQL statement is seen as a complete transaction and, second, each transaction (read: individual update) is automatically made permanent. This mode of working is called autocommit. Changes can be undone by the user only if he or she executes compensating changes. For example, if rows are added with an INSERT statement, this change can be undone only by executing one or more DELETE statements. However, we can turn off this automatic commitment of transactions.
If you use WinSQL as a product for interactive SQL, it works as follows. When a new connection is created, the check mark in the Autocommit Transactions box must be removed; see Figure 27.1. At the bottom of the screen, a little red ball appears (instead of a green one) to indicate that the user is now responsible for ending the transactions. In other products, the autocommit must be turned off in another way.
However, MySQL does not settle for that. When a session is started, the AUTOCOMMIT system parameter of MySQL likely is turned on. An SQL statement must be used to turn it off. So, after turning off autocommit for WinSQL, we have to do the same for MySQL. The statement to turn off autocommit is simple:
SET AUTOCOMMIT = 0
When autocommit must be turned on again, you issue this statement:
SET AUTOCOMMIT = 1
After the autocommit has been turned off, a transaction can consist of multiple SQL statements, and you must indicate the end of each transaction. Two separate SQL statements accomplish this. In the next example, we illustrate how all this works.
Example 27.1. Imagine that all penalties of player 44 are to be deleted.
DELETE FROM PENALTIES WHERE PLAYERNO = 44
The effect of this statement becomes apparent when you issue the following SELECT statement:
SELECT * FROM PENALTIES
The result is:
PAYMENTNO PLAYERNO PAYMENT_DATE AMOUNT
--------- -------- ------------ ------
1 6 1980-12-08 100.00
3 27 1983-09-10 100.00
4 104 1984-12-08 50.00
6 8 1980-12-08 25.00
8 27 1984-11-12 75.00
Three rows have been deleted from the table. However, the change is not yet permanent (even though it looks that way) because autocommit has been turned off. The user (or application) has a choice now. The change can be undone with the SQL statement ROLLBACK or made permanent with the COMMIT statement.
<commit statement> ::= COMMIT [ WORK ] <rollback statement> ::= ROLLBACK [ WORK ] |
Let us take the first choice, and use the following statement:
ROLLBACK WORK
Explanation. If we repeat the SELECT statement used previously now, it returns the entire PENALTIES table. The three deleted rows appear in the result again. If we wanted to make the change permanent, we should have used the COMMIT statement:
COMMIT WORK
After this statement, the three rows would have been deleted from the table for good; the change would have been permanent.
We can omit the word WORK because it does not affect the processing.
COMMIT statements make the changes permanent and ROLLBACK statements undo them. Now the question is, which changes will be rolled back? Is it only the last change, or everything from the moment you started the application? To answer this, we return to the concept of a transaction. As we have already mentioned, a transaction is a set of SQL statements. For example, the earlier DELETE and SELECT statements form a (small) transaction. COMMIT and ROLLBACK statements always relate to the so-called current transaction. In other words, these statements relate to all SQL statements executed during the current transaction. Now the question is, how do we mark the beginning and end of a transaction? For now, we assume that the beginning of a transaction cannot be marked explicitly. (We return to this subject in Section 27.11.) The first SQL statement executed in an application is considered to be the beginning of the first transaction. The end of a transaction is marked by using a COMMIT or ROLLBACK statement. From this, you can conclude that an SQL statement that follows a COMMIT or ROLLBACK statement is the first statement of the new current transaction.
Example 27.2. To illustrate all this, here is a series of statements that are entered consecutively. It is not important whether these statements are entered interactively (with SQL, for example) or whether they have been embedded within a host language program:
INSERT ... DELETE ... ROLLBACK WORK UPDATE ... ROLLBACK WORK INSERT ... DELETE ... COMMIT WORK UPDATE ... end of program
Explanation:
Lines 1–2: These two changes are not yet permanent.
Line 3: A
ROLLBACKstatement is executed. All changes of the current transaction are undone. These are the changes on lines 1 and 2.Line 4: This change is not yet permanent. Because this statement follows a
ROLLBACKstatement, a new transaction is started.Line 5: A
ROLLBACKstatement is executed. All changes of the current transaction are undone. This is the change on line 4.Lines 6–7: These two changes are not yet permanent. Because the statement on line 6 follows a
ROLLBACKstatement, a new transaction is started.Line 8: A
COMMITstatement is executed. All changes of the current transaction become permanent. These are the changes on lines 6 and 7.Line 9: This change is not yet permanent. Because this statement follows a
COMMITstatement, a new transaction is started.Line 10: Here the program is ended. All changes of the current transaction are undone—in this case, the change on line 9.
When a program stops without marking the end of a transaction, SQL automatically executes a ROLLBACK statement. We advise you, however, to make the last SQL statement executed by a program always a COMMIT or ROLLBACK statement.
Why would we want to undo transactions? This question can be formulated in another way: Why not always execute a COMMIT statement immediately after each change? There are two main reasons. The first deals with the fact that during the processing of SQL statements, something can go wrong, for whatever reason. For example, when you add new data, the database might become full, the computer might break down during the processing of an SQL statement, or a division by zero might occur during a calculation. Imagine that one of these problems occurs when you process one of the statements in the next example.
Example 27.3. Delete all data for player 6. We assume that no foreign keys have been defined.
DELETE FROM PLAYERS WHERE PLAYERNO = 6 DELETE FROM PENALTIES WHERE PLAYERNO = 6 DELETE FROM MATCHES WHERE PLAYERNO = 6 DELETE FROM COMMITTEE_MEMBERS WHERE PLAYERNO = 6 UPDATE TEAMS SET PLAYERNO = 83 WHERE PLAYERNO = 6
Five statements are required to remove all the information about a particular player: four DELETE statements and one UPDATE statement. In the last statement, player 6 is not removed from the TEAMS table, but replaced by player 83 because player 6 can no longer be captain (because he no longer occurs in the PLAYERS table). A new captain must be registered as well because the PLAYERNO column in the TEAMS table is defined as NOT NULL. If you use a DELETE statement instead of an UPDATE statement, data about the team captained by player 6 will also be deleted, and that is not what is intended. These five changes together form a unit and must be dealt with as one transaction. Imagine that the third DELETE statement goes wrong. At that moment, two changes of the transaction have been executed and three have not. The first two changes cannot be undone. In other words, the MATCHES and TEAMS tables contain data about a player who does not occur in the PLAYERS table, which is an unwanted situation. We conclude that either all five changes must be executed or none at all. Therefore, we must be able to undo the changes that have already been carried out.
The second reason concerns the user’s own mistakes. Imagine that a user changes a large amount of data in different tables concerning a particular player and discovers later that he chose the wrong player. He must be able to roll back these changes. Here, the ROLLBACK statement can be useful.
In most SQL products, statements that change the catalog, such as CREATE TABLE, GRANT, and DROP INDEX, cannot be undone. Before and after the processing of such a statement, SQL automatically executes a COMMIT statement. This type of statement, therefore, ends any current transaction. Turning autocommit on or off has no effect.
The first SQL statement of an application or the first SQL statement after a COMMIT or ROLLBACK starts a new transaction. This is called an implicit start of a transaction. However, it is possible to start a transaction explicitly with the START TRANSACTION statement.
Example 27.4. Rewrite Example 27.2 so that transactions are started explicitly.
START TRANSACTION INSERT ... DELETE ... ROLLBACK WORK START TRANSACTION UPDATE ... ROLLBACK WORK START TRANSACTION INSERT ... DELETE ... COMMIT WORK START TRANSACTION UPDATE ... end of program
A START TRANSACTION statement automatically leads to a COMMIT of the changes that are not permanent yet. In addition, the autocommit is turned off. The SET AUTOCOMMIT statement is, therefore, not required. If the transaction is ended, the value of the AUTOCOMMIT variable is reset to the old value, regardless of what it was.
Instead of START TRANSACTION, you can also use the statement BEGIN WORK. However, the statement mentioned first is preferable because many other SQL products support it.
As already mentioned, the concept of a transaction and the statements COMMIT and ROLLBACK also apply to SQL statements that are included in a host language: see Chapter 26, “Introduction to Embedded SQL.”
Example 27.5. Extend the RAISE_WON_3 program from Section 26.15 with COMMIT and ROLLBACK statements.
PROGRAM RAISE_WON_4;
DECLARATIONS
pno : SMALLINT;
won : INTEGER;
choice : CHAR(1);
stop : CHAR(1);
BEGIN
DECLARE c_mat CURSOR FOR
SELECT PLAYERNO, WON
FROM MATCHES
WHERE TEAMNO = 1
FOR UPDATE OF WON;
#
stop := 'N';
OPEN c_mat;
FETCH c_mat INTO :pno, :won;
WHILE sqlcode = 0 AND stop = 'N' DO
WRITE 'Do you want the number of sets won for ',
player ';
WRITE 'pno, ' to be increased by 1 (Y/N)?';
READ choice;
IF choice = 'Y' THEN
UPDATE MATCHES
SET WON = WON + 1
WHERE CURRENT OF c_mat;
IF sqlcode < 0 THEN
ROLLBACK WORK;
stop := 'Y';
ELSE
FETCH c_mat INTO :pno, :won;
ENDIF;
ENDIF;
ENDWHILE;
CLOSE c_mat;
COMMIT WORK;
WRITE 'Ready';
END
Explanation. A COMMIT statement is added at the end of the program. So, after the last player has been processed, the cursor is closed and all changes become permanent. Within the WHILE-DO statement, a ROLLBACK statement is included. If the value of the SQLCODE host variable is negative after the UPDATE statement, something has gone wrong. In that case, all changes that have been executed so far are undone. Thus, even if a mistake occurs with the last player, all changes will still be undone.
In the previous sections, we discussed how complete transactions can be undone. It is also possible to undo only a part of a current transaction by using savepoints.
<savepoint statement> ::= SAVEPOINT <savepoint name> |
To use savepoints, we must extend the definition of the ROLLBACK statement somewhat:
<rollback statement> ::= ROLLBACK [ WORK ] [ TO SAVEPOINT <savepoint name> ] |
Here is another example to show how this works:
UPDATE ... INSERT ... SAVEPOINT S1 INSERT ... SAVEPOINT S2 DELETE ... ROLLBACK WORK TO SAVEPOINT S2 UPDATE ... ROLLBACK WORK TO SAVEPOINT S1 UPDATE ... DELETE ... COMMIT WORK
Explanation:
Lines 1–2: These two changes are not yet permanent.
Line 3: A savepoint is defined with the name
S1.Line 4: This change is not yet permanent.
Line 5: A savepoint is defined with the name
S2.Line 6: This change is not yet permanent.
Line 7. A
ROLLBACKis issued. However, not all changes are undone—only those performed after savepointS2. This is the change on line 6. The changes on lines 1 and 2 are not yet permanent but are still present.Line 8: This change is not yet permanent.
Line 9: A
ROLLBACKto savepointS1is entered. All changes performed after savepointS1are undone. These are the changes on lines 4 and 8.Lines 10–11: These two changes are not yet permanent.
Line 12: All nonpermanent changes are made permanent. These are the changes on lines 1, 2, 10, and 11.
When a change is undone to a certain savepoint, only the last changes of the current transaction can be undone.
Imagine that you have removed all rows from the PENALTIES table in a transaction, but you have not yet ended the transaction. What will the other users see if they query the PENALTIES table? Will they also see an empty table, or will they still see all the original rows? Are they allowed to see the changes that you have not yet made permanent? These problems are comparable to the problems of a policeman on a crossing. Whatever the policeman does and however he moves his arms, he must ensure that two cars do not use the crossing at the same time at the same place. SQL (the policeman) must ensure that two users (the cars) do not access the same data (the crossing) simultaneously in the wrong way.
The problem described here is just one of the possible problems due to the effects of multi-user usage, but there are more. In this section, we paint a picture of the four best-known problems by using a few examples. For more detailed descriptions and for other problems, we refer to [BERN97] and [GRAY93].
The problem when one SQL users sees data that has not been committed yet by another user is called a dirty read or uncommitted read.
Example 27.6. Assume the following series of events. These events are entered consecutively.
User U1 wants to increase the amount of the penalty with payment number 4 by $25. For this, he uses the following
UPDATEstatement:UPDATE PENALTIES SET AMOUNT = AMOUNT + 25 WHERE PAYMENTNO = 4
Before U1 ends the transaction with a
COMMITstatement, user U2 accesses the same penalty with the following SELECT statement and sees the updated amount:SELECT * FROM PENALTIES WHERE PAYMENTNO = 4
U1 rolls back the
UPDATEstatement with aROLLBACKstatement.
The result is that U2 has seen data that was never “committed.” In other words, he saw data that never even existed. The SELECT statement that U2 executed is called a dirty read. User U2 has seen “dirty” data.
A special version of the dirty read is the nonrepeatable read, nonreproducible read, or inconsistent read. Here, a user reads partly dirty and partly clean data, and combines it. The same user is not aware that this result is based upon data that is only partly clean.
Example 27.7. The following events are entered consecutively.
With the following
SELECTstatement, user U1 retrieves all players resident in Stratford and writes their player numbers on a piece of paper:SELECT PLAYERNO FROM PLAYERS WHERE TOWN = 'Stratford'
The result is:
6, 83, 2, 7, 57, 39,and100. Then, U1 starts a new transaction.A few seconds later, user U2 changes the address of player 7 (who lives in Stratford) with the following
UPDATEstatement:UPDATE PLAYERS SET TOWN = 'Eltham' WHERE PLAYERNO = 7
Next, user U2 ends the transaction with a
COMMITstatement.Now U1 queries one by one the addresses of the players that were written on the piece of paper, using the following
SELECTstatement, and prints them on labels:SELECT PLAYERNO, NAME, INITIALS, STREET, HOUSENO, POSTCODE, TOWN FROM PLAYERS WHERE PLAYERNO IN (6, 83, 2, 7, 57, 39, 100)
The result of these two changes is that U1 also prints a label for player 7 because he assumed that player 7 still lived in Stratford. This means that the second SELECT statement in the same transaction does not give the same picture of the database. The result of the first SELECT statement cannot be reproduced, which, of course, is not desirable.
The following problem is known as phantom read.
Example 27.8. The following events are again entered consecutively.
With the following
SELECTstatement, user U1 is looking for all players resident in Stratford:SELECT PLAYERNO FROM PLAYERS WHERE TOWN = 'Stratford'
The result is:
6, 83, 2, 7, 57, 39,and100. However, user U1 does not end the transaction.Some time later, user U2 adds a new player who lives in Stratford and ends the transaction with a
COMMITstatement.User U1 sees one more row when he executes the same
SELECTstatement: the row that was entered by user U2.
This means that the second SELECT statement in the same transaction (just like the last example) does not present the same picture of the database. The difference between phantom read and nonrepeatable read is that, with the former, new data becomes available, and, with the latter, data is changed.
The final problem that we discuss is called lost update in the literature. The change of one user is overwritten by that of another.
Example 27.9. The following events are entered consecutively again.
User U1 wants to increase the amount of the penalty with payment number 4 by $25. First, he queries the penalty amount with a
SELECTstatement (a transaction starts). The penalty appears to be $50.A few seconds later, user U2 wants to do the same. User U2 wants to increase the amount of the penalty with payment number 4 by $30. He also queries the current value with a
SELECTstatement and sees $50. A second transaction begins here.User U1 executes the following
UPDATEstatement (notice theSETclause):UPDATE PENALTIES SET AMOUNT = AMOUNT + 25 WHERE PAYMENTNO = 4
Next, user U1 ends his transaction with a
COMMITstatement.User U2 executes his
UPDATEstatement (notice theSETclause):UPDATE PENALTIES SET AMOUNT = AMOUNT + 30 WHERE PAYMENTNO = 4
User U2 also ends his transaction with a
COMMITstatement.
The result of these two changes is that both users think that their change has been executed (“committed”). However, the change of user U1 has disappeared. His change of $25 is overwritten by the change of user U2. Losing changes, of course, is not desirable. SQL must take care that, after changes have been “committed,” they actually are permanent.
All the problems we have described here can be solved easily by not allowing two users to run a transaction simultaneously. If the transaction of U2 can start only if that of U1 has ended, nothing will go wrong. In other words, the transactions are processed serially. However, imagine that you share the database with more than a hundred users. If you end a transaction, it will probably be a long time before it is your turn again. We then say that the level of concurrency is low: no two users can work simultaneously. Therefore, it is necessary to process transactions simultaneously, or in parallel. But to do this, SQL needs a mechanism to prevent the previously mentioned problems from occurring. This is the subject of the remaining of the chapter.
A number of different mechanisms exist to keep the level of concurrency high and still prevent problems. In this section, we discuss the mechanism that has been implemented in most SQL products: locking.
The basic principle of locking is simple. If a user accesses a certain piece of data, such as a row from the PLAYERS table, the row will be locked and other users will not be able to access that row. Only the user who has locked the row can access it. Locks are released when the transaction ends. In other words, the life of a lock is never longer than that of the transaction in which the lock is created.
Let us see what will happen with two of the problems discussed in the previous section. For the problem of the lost update (see Example 27.9), user U1 accesses penalty number 4 first. SQL automatically places a lock on that row. Then user U2 tries to do the same. This user, however, gets a message indicating that the row is not available. He must wait until U1 has finished. This means that the final penalty amount will be $105 (work it out for yourself). In this case, the transactions of U1 and U2 are processed not in parallel, but serially. Other users who do not work with penalty number 4, but with another number, are processed concurrently.
For the problem of the nonrepeatable read (see Example 27.7), we now have a comparable situation. Only after U1 has printed the labels can user U2 change the address, which will no longer cause problems.
A locking mechanism works correctly if it meets the serializability criterion. This means that a mechanism works correctly if the contents of the database after (concurrently) processing a set of transactions are the same as the contents of the database after processing the same set of transactions serially (order is irrelevant). The state of the database after problem 1 is such that the penalty amount of penalty number 4 is $80. You will never manage to get the same amount by processing the two transactions of U1 and U2 serially. Whether you execute U1’s transaction first and then U2’s, or vice versa, the result will be $105, not $80.
Where does the database keep track of all those locks? This lock administration is kept in internal memory of the computer. Usually, a large part of the internal memory is reserved for this. This space is called the buffer. Therefore, locks are not stored in the database. We also mention, probably unnecessarily, that users do not see locks.
We stated that the transactions of users U1 and U2 are processed serially after locks have been placed. This is not ideal, of course. To increase the level of concurrency, most products support two types of locks: share and exclusive. (Sometimes, these locks are called read and write, respectively.) If a user has a share lock on a row, other users can read that row but cannot change it. The advantage is that users who only execute SELECT statements in their transactions do not hold each other up. If a user has an exclusive lock, other users cannot reach the row at all, even to read it. In the previous sections, we have assumed that each lock was an exclusive lock.
No separate SQL statement exists to indicate that you want to work with share locks, for example. SQL determines this itself. The type of lock is derived from the SQL statement. For example, if a SELECT statement is executed, a share lock is implemented. On the other hand, when you use an UPDATE statement, an exclusive lock is set.
A well-known phenomenon that can occur if many users access the database simultaneously is what we call a deadlock. Simply put, a deadlock arises if two users wait for each other’s data. Imagine that user U1 has a lock on row R1 and that he or she wants to place one on row R2. Assume also that user U2 is the “owner” of the lock on row R2 and wants to place a lock on R1. These two users are waiting for each other. If we go back to the analogy of a road crossing, have you ever been at a crossroads when four cars approach at the same time? Who can drive on first? This is also deadlock.
Some SQL products can discover that a deadlock has arisen. From time to time, such a product checks whether there are users waiting for each other. If they are found, SQL automatically aborts one of the transactions. It will feel as if SQL executed a ROLLBACK statement of its own accord. The transaction that is chosen differs for each product.
So far, we have described locking generally and in a rudimentary way. We have assumed that locks can be placed on individual rows. Some SQL products do not lock rows; they lock the entire physical page in which the row is stored (see also Section 20.2, in Chapter 20, “Using Indexes”). This means that if a row is changed, not only that single row, but also a set of rows, is locked. If the row is short, this number can be large. Some products lock even more data so that if a row is accessed, the entire table is locked.
The amount of data that is locked is called the granularity. The larger the granularity of a lock is, the lower the level of concurrency is and the simpler the internal administration is for SQL. In practice, we usually work with a granularity of one row or one physical page.
What does this mean for SQL? Not much because locking is hidden from the program and the user. For some SQL products, the granularity is fixed and the programmer cannot change it. With products that do support multiple levels of granularity, the granularity required must be specified. For example, with DB2, this can be specified per table with the ALTER TABLE statement. Here is an example.
Example 27.10. Define the granularity of locks for the PENALTIES table on row level.
ALTER TABLE PENALTIES LOCKSIZE ROW
Only a few products can indicate granularity at the beginning of a transaction. Still, this would be a very valuable feature.
As we have already mentioned, during a transaction, all the data in use is locked against other users. To keep track of which data has been locked by which application, SQL must keep some internal administration. A user can execute many changes on a particular table within one transaction. For example, he might have a program that changes a column value of all rows of a table. These changes will be responsible for a huge amount of internal administrative work. To avoid this, you can lock the entire table in one process at the beginning of a transaction using the LOCK TABLE statement.
<lock table statement> ::= LOCK TABLE <table specification> IN <lock type> MODE <lock type> ::= SHARE | EXCLUSIVE |
Only base tables (tables that have been created with a CREATE TABLE statement) can be locked. At the end of a transaction, a lock is released automatically.
Explanation: In MySQL, a somewhat different syntax is used. The statement looks as follows:
LOCK TABLE PLAYERS READ
SQL supports the following lock types:
SHARE—. A lock of this type ensures that the application can read the table; other applications are also allowed to read the table, but they cannot change it.
EXCLUSIVE—. A lock of this type ensures that the application can change the table; other applications cannot gain access to the table and can neither read it nor change it.
One further complication exists. When starting a transaction, you can set a so-called isolation level. This isolation level shows (the word says it already) to what extent the users are isolated from each other, or, in other words, to what extent they interfere with each other. So far, we have assumed only one isolation level. In SQL, we find the following levels:
Serializable—. If the isolation level is serializable, the users are the most separated from each other.
Repeatable read—. If the isolation level is repeatable read (read repeatability), share locks are set on all data that a user reads, and exclusive locks are placed on data that is changed. These locks exist as long as the transaction runs. This means that if a user executes the same
SELECTstatement several times within the same transaction, the result will always be the same. In previous sections, we assumed that this isolation level was desirable.Cursor stability or read committed—. With cursor stability, the same locks are placed as for repeatable read. The difference is that share locks are released if the
SELECTstatement is processed. In other words, after theSELECTstatement has been processed, but before the transaction ends, data becomes available for other users. This does not apply, of course, to changes. An exclusive lock is set on data that has been changed and remains there until the end of the transaction.Dirty read or read uncommitted—. For reading data, dirty read is equal to cursor stability. However, with dirty read, a user can see the changes carried out by another user before that user has made his changes permanent with a
COMMITstatement. In other words, the exclusive lock is released immediately after a change but before the transaction ends. This means that if you work with dirty read, the locking mechanism does not meet the serializability criterion.
In summary, with the isolation level called serializable, users have the greatest isolation from each other, but the level of concurrency is the lowest. This is the opposite of dirty read, in which users will definitely notice that they are not alone in using the system. They can read data that does not exist a few seconds later. However, the level of concurrency is the highest. It will rarely happen that a user will have to wait for another user. Table 27.1 indicates for each type of problem described in Section 27.6 whether this can occur for a specific isolation level.
How the required isolation level is specified depends on the product. Some products support no SQL statement for specifying the isolation level. The level is set during pre-compilation for most products (see Section 26.4, in Chapter 26) and applies to all SQL statements in the precompiled program.
With other SQL products, the isolation level can be set using a specific SQL statement. This is the SET TRANSACTION statement:
<set transaction statement> ::= SET TRANSACTION ISOLATION LEVEL <isolation level> <isolation level> ::= READ UNCOMMITTED | READ COMMITTED | REPEATABLE READ | SERIALIZABLE |
In Section 27.2, we mentioned that the beginning of a transaction cannot be indicated explicitly. The first statement is the beginning of the transaction. However, it is possible to define the beginning of a transaction by using a SET TRANSACTION statement. In other words, when you enter a SET TRANSACTION statement, a new transaction starts automatically.