Tài liệu SQL Anywhere Studio 9- P7 docx

flexible facility that was described in Section 9.4, “Savepoints and

Subtransactions.”

9.6 Locks

In order to improve overall productivity, different transactions are allowed to

overlap one another in a multi-user environment. For example, if SQL Any￾where has processed an UPDATE and is waiting to receive the next SQL

command that is part of the same transaction, and a SELECT that is part of a

different transaction arrives in the meantime, it will try to process the SELECT

immediately. If SQL Anywhere only worked on one transaction at a time, no

one would get any work done; in reality, the database engine can switch back

and forth among hundreds of overlapping transactions in a busy environment.

The ability of SQL Anywhere to process overlapping transactions is called

concurrency, and it may conflict with two of the basic requirements of a transac￾tion: consistency and isolation. For example, if two overlapping transactions

were allowed to update the same row, the requirement that changes made by dif￾ferent transactions must be isolated from one another would be violated.

Another example is a transaction design that requires data to remain unchanged

between retrieval and update in order for the final result to be consistent; that

requirement would be violated by an overlapping transaction that changed the

data after the first transaction retrieved it, even if the second transaction com￾mitted its change before the first transaction performed its update.

SQL Anywhere uses locks to preserve isolation and consistency while

allowing concurrency. A lock is a piece of data stored in an internal table main￾tained by SQL Anywhere. Each lock represents a requirement that must be met

before a particular connection can proceed with its work, and logically it is

implemented as a temporary relationship between that connection and a single

row or table. While it exists, a lock serves to prevent any other connection from

performing certain operations on that table or row.

When a lock is needed by a connection in order to proceed, it is said to be

requested by that connection. If SQL Anywhere creates the lock, the request is

said to be granted, the lock is said to be acquired, and the work of that connec￾tion can proceed. If SQL Anywhere does not create the lock because some other

conflicting lock already exists, the request is said to be blocked, the lock cannot

be acquired, and the connection cannot proceed.

Locks fall into two broad categories: short-term and long-term. A

short-term lock is only held for the duration of a single SQL statement or less,

whereas a long-term lock is held for a longer period, usually until the end of a

transaction. This chapter concentrates on the discussion of long-term locks

because short-term locks are not visible from an administrative point of view.

Unless otherwise noted, the term “lock” means “long-term lock” in this chapter.

The built-in procedure sa_locks can be used to show all the locks held at a

given point in time. Here is an example of a call:

CALL sa_locks();

The following shows what the output from sa_locks looks like; each entry rep￾resents one or more locks associated with a particular table or row. The

connection column identifies the connection that is holding the locks, the

336 Chapter 9: Protecting

Please purchase PDF Split-Merge on www.verypdf.com to remove this watermark.

user_id column contains the user id that was used to make the connection, the

table_name shows which table the locks are associated with, the lock_type iden￾tifies the different kinds of locks represented by this entry, and the lock_name

column is an internal row identifier or NULL for an sa_locks entry that is asso￾ciated with an entire table.

connection user_id table_name lock_type lock_name

========== ======= ========== ========= =========

508116521 DBA DBA.t1 E 473

508116521 DBA DBA.t3b EPA* 4294967836

508116521 DBA DBA.t1b EPA0000 4294967834

508116521 DBA DBA.t1u EPA0001 12884902403

508116521 DBA DBA.t1n EPT 528

508116521 DBA DBA.t3 S 4294967821

508116521 DBA DBA.t1 SPA0000 1095216660986

508116521 DBA DBA.t1u SPA0001 1095216661028

508116521 DBA DBA.t3n SPT 553

508116521 DBA DBA.e4b E NULL

508116521 DBA DBA.e4 EPT NULL

508116521 DBA DBA.t2n S NULL

508116521 DBA DBA.e1b SAT NULL

508116521 DBA DBA.e3 SPAT NULL

508116521 DBA DBA.t2b SPT NULL

Here is what the various characters in the lock_type column mean for lines in

the sa_locks output that have non-NULL row identifiers in the lock_name

column:

“E” represents an exclusive row write lock. This kind of lock won’t be

granted if any other connection has an exclusive row write lock or a shared

row read lock on the row. Once an exclusive row write lock has been

acquired, no other connection can obtain any kind of lock on the row.

“S” represents a shared row read lock. This kind of lock may coexist with

other shared row read locks on the same row that have been granted to

other connections.

“P” represents an insert, or anti-phantom, row position lock, which reserves

the right to insert a row in the position immediately ahead of the row identi￾fied by the lock_name column. The row position is determined in one of

three ways: with respect to the order of a particular index, with respect to

the order of a sequential table scan, or with respect to all index and sequen￾tial orderings on the table. An exclusive row write lock or a shared read

row lock is always granted at the same time as an insert row position lock.

“A” represents an anti-insert, or phantom, row position lock, which pre￾vents any other connection from inserting a row in the position immediately

ahead of the row identified by the lock_name column. The row position is

determined in the same manner as for an insert lock. An exclusive row

write lock or a shared read row lock is always granted at the same time as

an anti-insert row position lock. Also, anti-insert and insert locks may be

granted at the same time; e.g., the combinations “EPA” and “SPA” mean

that three locks associated with the same row are represented by one entry

in the sa_locks output.

A four-digit integer like 0000 or 0001 identifies the index used to determine

the row ordering for insert and anti-insert row position locks.

Chapter 9: Protecting 337

Please purchase PDF Split-Merge on www.verypdf.com to remove this watermark.

“T” specifies that a sequential table scan is used to determine the row

ordering for insert and anti-insert row position locks.

The asterisk (*) specifies that the insert and anti-insert locks apply to all

index and sequential orders.

Here is what the various characters in the lock_type column mean for lines in

the sa_locks output that have NULL values in the lock_name column:

“E” represents an exclusive table schema lock.

“S” represents a shared table schema lock.

“PT” represents a table contents update intent lock.

“AT” represents a table contents read lock.

“PAT” represents a combination of two table contents locks: update intent

and read.

Here are all the combinations of lock_type and lock_name from the earlier

example of sa_locks output, together with a description of the locks they repre￾sent according to the definitions given above:

Table 9-2. lock_type and lock_name combinations

lock_type lock_name Description

E 473 Exclusive row write lock

EPA* 4294967836 Exclusive row write lock, plus insert and

anti-insert row position locks with respect to

all orders

EPA0000 4294967834 Exclusive row write lock, plus insert and

anti-insert row position locks with respect to

index 0000

EPA0001 12884902403 Exclusive row write lock, plus insert and

anti-insert row position locks with respect to

index 0001

EPT 528 Exclusive row write lock, plus anti-insert row

position lock with respect to sequential order

S 4294967821 Shared row read lock

SPA0000 1095216660986 Shared row read lock, plus insert and

anti-insert row position locks with respect to

index 0000

SPA0001 1095216661028 Shared row read lock, plus insert and

anti-insert row position locks with respect to

index 0001

SPT 553 Shared row read lock, plus anti-insert row

position lock with respect to sequential order

E (NULL) Exclusive table schema lock

EPT (NULL) Exclusive table schema lock, plus update

intent table contents lock

338 Chapter 9: Protecting

Please purchase PDF Split-Merge on www.verypdf.com to remove this watermark.

lock_type lock_name Description

S (NULL) Shared table schema lock

SAT (NULL) Shared table schema lock, plus table contents

read lock

SPAT (NULL) Shared table schema lock, plus table contents

read and update intent locks

SPT (NULL) Shared table schema lock, plus table contents

update intent lock

A single connection isn’t prevented from obtaining different kinds of locks on

the same table or row; conflicts only arise between different connections. For

example, one connection cannot obtain an insert lock on a row position while

another connection has an anti-insert lock on the same row position, but a single

connection can obtain both kinds of locks on the same position.

When a lock is no longer needed by a connection, it is said to be released,

and SQL Anywhere deletes the entry from the internal lock table. Most locks

persist from the time they are acquired by a connection until the next time that

connection performs a COMMIT or ROLLBACK operation. However, some

locks are released earlier, and others can last longer. For example, a read lock

that is acquired by a FETCH operation in order to ensure cursor stability at iso￾lation level 1 will be released as soon as the next row is fetched. Also, the

exclusive table lock acquired by a LOCK TABLE statement using the WITH

HOLD clause will persist past a COMMIT; indeed, if the table is dropped and

recreated, the table lock will be resurrected automatically, and it won’t released

until the connection is dropped. Cursor stability is discussed in the following

section, as are some performance improvements made possible by the LOCK

TABLE statement.

For all practical purposes, however, all row locks acquired during a transac￾tion are held until the transaction ends with a COMMIT or ROLLBACK, and at

that point all the locks are released. This is true of statements that fail as well as

those that succeed. Single SQL statements like INSERT, UPDATE, and

DELETE are atomic in nature, which means that if the statement fails, any

changes it made to the database will be automatically undone. That doesn’t

apply to the locks, however; any locks obtained by a failed statement will per￾sist until the transaction ends.

9.7 Blocks and Isolation Levels

A block occurs when a connection requests a lock that cannot be granted. By

default, a block causes the blocked connection to wait until all conflicting locks

are released. The database option BLOCKING may be set to 'OFF' so that a

blocked operation will be immediately cancelled and an error will be returned to

the blocked connection. The cancellation of a blocked operation does not imply

an automatic rollback, however; the affected connection may proceed forward

and it still holds any locks it may have acquired earlier, including locks acquired

during earlier processing of the failed statement.

Chapter 9: Protecting 339

Please purchase PDF Split-Merge on www.verypdf.com to remove this watermark.

The number of locks held at any one time by a single connection can vary

from zero to several million. The actual number depends on two main factors:

the kinds of SQL operations performed during the current transaction and the

setting of the ISOLATION_LEVEL database option for the connection when

each operation was performed. Some operations, such as UPDATE, require

locks regardless of the isolation level. Other operations, such as SELECT, may

or may not require locks depending on the isolation level.

The isolation level is a number 0, 1, 2, or 3, which represents the degree to

which this connection will be protected from operations performed by other

connections.

Isolation level 0 prevents overlapping data changes, data retrievals overlap￾ping with schema changes, and deadlock conditions. Figures 9-2 through

9-5 and 9-20 show how overlapping transactions are affected by isolation

level 0.

Isolation level 1 prevents dirty reads and cursor instability, in addition to

the protection provided by isolation level 0. Figures 9-6 through 9-9 dem￾onstrate the effects of isolation level 1.

Isolation level 2 prevents non-repeatable reads and update instability, in

addition to the protection provided by isolation levels 0 and 1. Figures 9-10

through 9-13 show how repeatable reads and update stability is achieved at

isolation level 2.

Isolation level 3 prevents phantom rows and a particular form of lost

update, in addition to the protection provided by isolation levels 0, 1, and 2.

Figures 9-14 through 9-17 demonstrate the effects of isolation level 3.

Isolation levels 2 and 3 result in the largest number of locks and the highest

level of protection at the cost of the lowest level of concurrency. Figures 9-18

and 9-19 show how high isolation levels affect concurrency.

9.7.1 Isolation Level 0

Isolation level 0 is the default; it results in the fewest number of locks and the

highest degree of concurrency at the risk of allowing inconsistencies that would

be prevented by higher isolation levels.

Figure 9-2 is the first of several demonstrations of locks and blocks, all of

which involve two connections, one table, and various values of isolation level.

Here is the script used to create and fill the table with five rows; this script is the

starting point for Figures 9-2 through 9-20:

CREATE TABLE DBA.t1 (

k1 INTEGER NOT NULL PRIMARY KEY,

c1 VARCHAR ( 100 ) NOT NULL );

INSERT t1 VALUES ( 1, 'clean' );

INSERT t1 VALUES ( 3, 'clean' );

INSERT t1 VALUES ( 5, 'clean' );

INSERT t1 VALUES ( 7, 'clean' );

INSERT t1 VALUES ( 9, 'clean' );

COMMIT;

Figure 9-2 shows what happens when Connection A updates a row and then

Connection B attempts to update and delete the same row before Connection A

executes a COMMIT or ROLLBACK; both operations performed by

340 Chapter 9: Protecting

Please purchase PDF Split-Merge on www.verypdf.com to remove this watermark.