Data types: The first line of defense

SQL is a strongly typed language. That means that if a data entry is supposed to be of a particular type, the database engine will not accept any entry that does not conform to the rules of that type. The BOOLEAN type, for example, accepts only values of TRUE and FALSE; it rejects any and all other entries. The INTEGER type accepts only integers, the CHAR type accepts only valid alphanumeric characters, and so on. The strongly typed nature of SQL prevents a lot of bad stuff from being added to databases accidentally. Strong typing, however, does not prevent data of the correct type but with an incorrect value from being entered.

Constraints: The second line of defense

By applying constraints to your database tables, you can prevent incorrect data of the correct type from being accepted. (I cover constraints in Book 1, Chapter 5.) Several kinds of constraints exist, each of which prevents a certain class of problems. Probably the most flexible is the CHECK constraint because it enables you to specify exactly what values are acceptable for entry in a specific database field.

Here’s another look at an example of the use of a CHECK constraint that first appears in Book 1, Chapter 5:

CREATE TABLE TESTS (

TestName CHARACTER (30) NOT NULL,

StandardCharge NUMERIC (6,2)

CHECK (StandardCharge >= 0.00

AND StandardCharge <= 200.00)

) ;

This code guarantees that any entry in the StandardCharge field is of the NUMERIC type, with two decimal places, and that the value entered in the StandardCharge field must be no less than 0 and no more than 200.00. This kind of protection can prevent many errors due to a slip of the finger or the slip of a decimal point.

Sharp-eyed humans: The third line of defense

Strong typing can ensure that data being entered is of the proper type, and constraints can ensure that it is in the proper range of values. These defenses cannot ensure that the data is right, however. The only way to make sure that the data entered is indeed the data that is supposed to be entered is to have it checked by a human who knows what the data should be. In most cases, human checking is too costly and time-consuming, so it is not done. We accept a certain amount of bad data because having a human check it is not feasible in all but the most critical cases.

Using the SET TRANSACTION statement

Whatever DBMS you are using has default settings for how the transaction will be run. Although the default settings are perfectly fine most of the time, you can override them, if you want, with a SET TRANSACTION statement. Here’s the syntax:

<set transaction statement> ::=

SET [ LOCAL ] TRANSACTION <mode> [ , <mode> ] …

<mode> ::=

<isolation level>

| <access mode>

| <diagnostics size>

<isolation level> ::=

READ UNCOMMITTED

| READ COMMITTED

| REPEATABLE READ

| SERIALIZABLE

<access mode> ::=

READ ONLY

| READ WRITE

<diagnostics size> ::=

DIAGNOSTICS SIZE <simple value expression>

With the SET TRANSACTION statement, you can set the isolation level, the access mode, and the diagnostics size. Any one of the modes that you do not specify assumes the default value for that mode. If you specify the READ ONLY access mode, for example, any statements that change the database — such as UPDATE, INSERT, and DELETE — cannot execute. The default access mode is READ WRITE unless the isolation level is READ UNCOMMITTED. When you are running at the READ UNCOMMITTED isolation level, the default access mode is READ ONLY. The default isolation level is SERIALIZABLE. (I cover isolation levels in more detail in the next section. I defer discussion of DIAGNOSTICS SIZE to Book 4, Chapter 4.)

You can’t start a new transaction while an existing transaction is still active. If you do execute a SET TRANSACTION statement while a transaction is active, the modes specified in the statement apply only to the next transaction, not the current one. The LOCAL keyword restricts the mode settings specified to the local transaction included in a transaction that encompasses multiple databases.

Starting a transaction

As I mention earlier in this section, a transaction is started automatically when the database engine senses that the next statement to execute either reads from or writes to the database. Default modes are assumed unless a SET TRANSACTION statement has been executed. If one has, the modes specified in it are used rather than the default modes. The modes specified by a SET TRANSACTION statement are active only for the next transaction to execute. Any following transactions once again use the default modes unless another SET TRANSACTION statement is executed or a START TRANSACTION statement is executed.

With a START TRANSACTION statement, you can specify modes the same way you can with a SET TRANSACTION statement. The difference is that a START TRANSACTION statement starts a transaction, whereas a SET TRANSACTION statement sets up the modes for a transaction but does not actually start one.

Committing a transaction

Although SQL doesn’t require an explicit transaction-starting keyword, it has two that terminate a transaction: COMMIT and ROLLBACK. Use COMMIT when you have come to the end of the transaction and want to make permanent the changes that you have made in the database (if any). You may include the optional keyword WORK (COMMIT WORK), if you want. If an error is encountered or the system crashes while a COMMIT is in progress, you may have to roll the transaction back and try it again.

Rolling back a transaction

When you come to the end of a transaction, you may decide that you don’t want to make permanent the changes that have occurred during the transaction. In fact, you want to restore the database to the state it was in before the transaction began. To do this, issue a ROLLBACK statement. ROLLBACK is a fail-safe mechanism. Even if the system crashes while a ROLLBACK is in progress, you can restart the ROLLBACK after the system is restored, and it restores the database to its pre-transaction state.

Implementing deferrable constraints

Ensuring the validity of the data in your database means doing more than just making sure that the data is of the right type. Perhaps some columns, for example, should never hold a null value — and maybe others should hold only values that fall within a certain range. Such restrictions are constraints, as discussed in Book 1, Chapter 5.

Constraints are relevant to transactions because they can conceivably prevent you from doing what you want. Suppose that you want to add data to a table that contains a column with a NOT NULL constraint. One common method of adding a record is to append a blank row to your table and then insert values into it later. The NOT NULL constraint on one column, however, causes the append operation to fail. SQL doesn’t allow you to add a row that has a null value in a column with a NOT NULL constraint, even though you plan to add data to that column before your transaction ends. To address this problem, SQL enables you to designate constraints as DEFERRABLE or NOT DEFERRABLE.

Constraints that are NOT DEFERRABLE are applied immediately. You can set DEFERRABLE constraints to be initially DEFERRED or IMMEDIATE. If a DEFERRABLE constraint is set to IMMEDIATE, it acts like a NOT DEFERRABLE constraint: It is applied immediately. If a DEFERRABLE constraint is set to DEFERRED, it is not enforced.

To append blank records or perform other operations that may violate constraints, ISO/IEC standard SQL allows you to use a statement similar to the following:

SET CONSTRAINTS ALL DEFERRED ;

This statement puts all DEFERRABLE constraints in the DEFERRED condition. It does not affect the NOT DEFERRABLE constraints. After you have performed all operations that could violate your constraints, and the table reaches a state that doesn’t violate them, you can reapply them. The statement that reapplies your constraints looks like this:

SET CONSTRAINTS ALL IMMEDIATE ;

If you made a mistake, and any of your constraints are still being violated, you find out as soon as this statement takes effect.

If you do not explicitly set your DEFERRED constraints to IMMEDIATE, SQL does it for you when you attempt to COMMIT your transaction. If a violation is still present at that time, the transaction does not COMMIT; instead, SQL gives you an error message.

SQL’s handling of constraints protects you from entering invalid data (or an invalid absence of data, which is just as important) while giving you the flexibility to violate constraints temporarily while a transaction is still active.

Consider a payroll example to see why being able to defer the application of constraints is important.

Assume that an EMPLOYEE table has columns EmpNo, EmpName, DeptNo, and Salary. EMPLOYEE.DeptNo is a foreign key referencing the DEPT table. Assume also that the DEPT table has columns DeptNo and DeptName. DEPT.DeptNo is the primary key.

In addition, you want to have a table like DEPT that also contains a Payroll column that holds the sum of the Salary values for employees in each department.

You can create the equivalent of this table with the following view:

CREATE VIEW DEPT2 AS

SELECT D.*, SUM(E.Salary) AS Payroll

FROM DEPT D, EMPLOYEE E

WHERE D.DeptNo = E.DeptNo

GROUP BY D.DeptNo ;

You can also define this same view as follows:

CREATE VIEW DEPT3 AS

SELECT D.*,

(SELECT SUM(E.Salary)

FROM EMPLOYEE E

WHERE D.DeptNo = E.DeptNo) AS Payroll

FROM DEPT D ;

But suppose that for efficiency, you don’t want to calculate the sum every time you reference DEPT3.Payroll. Instead, you want to store an actual Payroll column in the DEPT table. Then you will update that column every time you change a salary.

To make sure that the Salary column is accurate, you can include a CONSTRAINT in the table definition, as follows:

CREATE TABLE DEPT

(DeptNo CHAR(5),

DeptName CHAR(20),

Payroll DECIMAL(15,2),

CHECK (Payroll = (SELECT SUM(Salary)

FROM EMPLOYEE E WHERE E.DeptNo= DEPT.DeptNo)));

Now suppose that you want to increase the salary of employee 123 by 100. You can do it with the following update:

UPDATE EMPLOYEE

SET Salary = Salary + 100

WHERE EmpNo = '123' ;

You must remember to do the following as well:

UPDATE DEPT D

SET Payroll = Payroll + 100

WHERE D.DeptNo = (SELECT E.DeptNo

FROM EMPLOYEE E

WHERE E.EmpNo = '123') ;

(You use the subquery to reference the DeptNo of employee 123.)

But there’s a problem: Constraints are checked after each statement. In principle, all constraints are checked. In practice, implementations check only the constraints that reference the values modified by the statement.

After the first preceding UPDATE statement, the implementation checks all constraints that reference values that the statement modifies. This check includes the constraint defined in the DEPT table, because that constraint references the Salary column of the EMPLOYEE table, and the UPDATE statement is modifying that column. After the first UPDATE statement, that constraint is violated. You assume that before you execute the UPDATE statement, the database is correct, and each Payroll value in the DEPT table equals the sum of the Salary values in the corresponding columns of the EMPLOYEE table. When the first UPDATE statement increases a Salary value, this equality is no longer true. The second UPDATE statement corrects this problem and again leaves the database values in a state for which the constraint is True. Between the two updates, the constraint is False.

The SET CONSTRAINTS DEFERRED statement lets you temporarily disable or suspend all constraints or only specified constraints. The constraints are deferred until you execute a SET CONSTRAINTS IMMEDIATE statement or a COMMIT or ROLLBACK statement. So you surround the previous two UPDATE statements with SET CONSTRAINTS statements. The code looks like this:

SET CONSTRAINTS DEFERRED ;

UPDATE EMPLOYEE

SET Salary = Salary + 100

WHERE EmpNo = '123' ;

UPDATE DEPT D

SET Payroll = Payroll + 100

WHERE D.DeptNo = (SELECT E.DeptNo

FROM EMPLOYEE E

WHERE E.EmpNo = '123') ;

SET CONSTRAINTS IMMEDIATE ;

This procedure defers all constraints. If you insert new rows into DEPT, the primary keys won’t be checked; you have removed protection that you may want to keep. Specifying the constraints that you want to defer is preferable. To do this, name the constraints when you create them, as follows:

CREATE TABLE DEPT

(DeptNo CHAR(5),

DeptName CHAR(20),

Payroll DECIMAL(15,2),

CONSTRAINT PayEqSumSal

CHECK (Payroll = SELECT SUM(Salary)

FROM EMPLOYEE E

WHERE E.DeptNo = DEPT.DeptNo)) ;

With constraint names in place, you can reference your constraints individually, as follows:

SET CONSTRAINTS PayEqSumSal DEFERRED;

UPDATE EMPLOYEE

SET Salary = Salary + 100

WHERE EmpNo = '123' ;

UPDATE DEPT D

SET Payroll = Payroll + 100

WHERE D.DeptNo = (SELECT E.DeptNo

FROM EMPLOYEE E

WHERE E.EmpNo = '123') ;

SET CONSTRAINTS PayEqSumSal IMMEDIATE;

Without a constraint name in the CREATE statement, SQL generates one implicitly. That implicit name is in the schema information (catalog) tables, but specifying the names explicitly is more straightforward.

Now suppose that in the second UPDATE statement, you mistakenly specified an increment value of 1000. This value is allowed in the UPDATE statement because the constraint has been deferred. But when you execute SET CONSTRAINTS IMMEDIATE, the specified constraints are checked. If they fail, SET CONSTRAINTS raises an exception. If, rather than a SET CONSTRAINTS IMMEDIATE statement, you execute COMMIT, and the constraints are found to be false, COMMIT instead performs a ROLLBACK.

Bottom line: You can defer the constraints only within a transaction. When the transaction is terminated by a ROLLBACK or a COMMIT, the constraints are both enabled and checked. The SQL capability of deferring constraints is meant to be used within a transaction. If used properly, it doesn’t create any data that violates a constraint available to other transactions.

Two-phase locking

Two-phase locking is a protocol designed to guarantee serializability. In the first phase, a transaction can acquire shared and exclusive locks, and may also upgrade a shared lock to an exclusive lock. It may not release any locks or downgrade an exclusive lock to a shared lock, however. In the second phase, the transaction may release shared and exclusive locks, as well as downgrade an exclusive lock to a shared lock, but it may not acquire a new shared or exclusive lock, or upgrade a shared lock to an exclusive lock.

In the strictest form of two-phase locking, the second phase, in which locks are released or downgraded, cannot occur until the transaction either commits or rolls back. This restriction protects a competing transaction from acquiring a lock on, and reading a value from, a resource that the original transaction released before aborting. In such a case, the second transaction would potentially read a value that no longer existed in the resource. In fact, after a rollback, it is as though that value never existed.

Granularity

The granularity of a lock determines the size of the resource being locked. Locks that are coarse-grained take rather large resources out of circulation. Fine-grained locks sequester relatively small resources. Course-grained locks deny access to big things, such as tables. Fine-grained locks protect smaller things, such as rows in a table.

This list describes four types of locks and the granularity of each type:

• Database locks: The database lock is the ultimate in coarse-grained locks. If a transaction puts an exclusive lock on a database, no other transaction can access the database at all until the lock is released. As you might imagine, database locks have a disastrous effect on overall productivity and should be avoided if at all possible. Sometimes, a database administrator must apply a database lock to prevent other transactions from corrupting the database while she is making alterations in the database structure.
• Table locks: Table locks, by locking an entire database table, are not as restrictive as database locks but are still pretty coarse. Generally, you would impose a table lock only if you were altering the structure of the table or if you were changing data in most or all of the rows in the table.
• Row locks: Row locks are fine-grained in that they lock only a single row in a table. If you’re changing only a value in a single row, there is no point in locking any rows other than that one target row. The only transactions that are affected by a row lock are those that want to do something to the very same row of the very same table.
• Page locks: A page lock — which has an intermediate granularity between a table lock and a row lock — locks an entire page in the page buffer. Because information gets transferred between the page buffer and disk a page at a time, some DBMSs provide locks at the page level. As processing proceeds, requiring pages currently residing in the page buffer to be swapped out in favor of pages on disk that are currently needed, the DBMS will resist, if possible, the urge to swap out any page locked by an active transaction. Swapping it out and then swapping it back in again soon would waste a tremendous amount of time.

Deadlock

A deadlock is not a type of lock or an example of granularity, but a problem that can arise in even a well-designed system that uses locking for concurrency control. To illustrate how a deadlock can happen, look again at the example in which you and your friend Calypso share a bank account (refer to “Solving Concurrent-Operation Conflicts,” earlier in this chapter). Now that you are using transactions with two-phase locking, you don’t have to worry about the lost-update problem anymore. A potential problem still exists, however. Once again, you and Calypso arrive at two different ATM machines at about the same time.

1. You insert your ATM card into your ATM.
2. The DBMS’s lock manager grants you a shared lock on your account record, enabling you to read your balance of $47.17.
3. Calypso inserts her ATM card into her ATM.
4. The DBMS’s lock manager grants Calypso a shared lock on your account record, enabling her to read the balance of $47.17.
5. You insert $100 in cash into your ATM.
6. The DBMS’s lock manager attempts to upgrade your shared lock to an exclusive lock but cannot because of Calypso’s shared lock. It goes into a wait loop, waiting for Calypso’s lock to be released.
7. Calypso inserts $100 in cash into her ATM.
8. The DBMS’s lock manager attempts to upgrade Calypso’s shared lock to an exclusive lock but cannot because of your shared lock. It goes into a wait loop, waiting for your lock to be released.
9. The machine is deadlocked. Neither you nor Calypso can complete the transaction.
10. After some period of time, the DBMS recognizes the deadlock situation and aborts one or both of the deadlocked transactions.

Ideally, instances such as this don’t occur too often. By putting your account update in a transaction, you have traded the lost-update problem for the deadlock problem. This situation is an improvement. At least you don’t end up with incorrect data in your database. It’s a hassle to have to redo your ATM transaction, however. If all goes well, you and Calypso don’t try to redo your transactions at the same time.

Measuring performance with throughput

What is performance, anyway? There are many ways of thinking about performance and many system parameters that you can measure to glean information about one aspect of performance or another. In this book, when I talk about performance, I am referring to throughput — a measure of the amount of work that gets completed per unit time. It is an overall measure that takes into account all the jobs running on a system. If one job is running really fast, but its locks are slowing all the other jobs running at the same time, throughput may well be lower than it would be if the first job were reconfigured so that it did not run quite so fast and held its locks for a shorter period.

Eliminating unneeded locks

Locks are designed to prevent concurrently running transactions from interfering with one another. They are applied automatically by the DBMS, so the application programmer does not have to worry about whether he should apply a lock. At times, however, such interference is not possible. In those cases, the locking overhead is a burden on the system, but no corresponding benefit in data integrity occurs.

This is true when only one transaction is running at a time, such as when a database is loading. It is also true when all queries are guaranteed to be read-only — when mining archived information, for example. In such cases, it makes sense to take advantage of the option to suppress the acquisition of locks.

Shortening transactions

Long transactions, which do a lot of things, tend to hold locks for a long time. This situation, of course, has a negative effect on the performance of all the other transactions running at the same time. If everyone followed the discipline of making transactions as short as possible, everyone would benefit by being able to acquire needed locks sooner.

Albert Einstein once said, with regard to physics, “Make everything as simple as possible, but not simpler.” The same logic applies here. Make transactions as short as possible, but not shorter. If you chop up transactions too finely, you could lose serializability, which means that you could lose accuracy. To continue the lost-update example (refer to “Solving Concurrent-Operation Conflicts,” earlier in this chapter), if you acquired a shared lock on your bank account, viewed your bank balance, and then dropped your shared lock before acquiring an exclusive lock to make a withdrawal, Calypso could have sneaked in while your locks were down and cleaned out your account. This situation would have been both surprising and disappointing, because you would just have read that there was plenty of money in your account.

Weakening isolation levels (ver-r-ry carefully)

If you weaken your isolation level from SERIALIZABLE to REPEATABLE READ or perhaps READ COMMITTED, you can increase your throughput. A good chance exists that in doing so, however, bad data will creep into your database. In most cases, weakening your isolation level in that way isn’t worthwhile.

In a few scenarios, perfect accuracy in data is not required, and system response time is very important. In such cases, weakening the isolation level may be appropriate.

Consider an airline’s reservation system. Suppose that a traveler goes to an airline’s online website, which is running at the READ COMMITTED isolation level, to look up a particular flight. Checking available seats is a read-only operation that puts a shared lock on the entire airplane. As soon as the cabin image is transmitted, the shared lock is dropped. The traveler decides that she would like to sit in seat 10-C, which the website shows as available. She clicks that seat on her screen image to indicate that she wants to reserve that seat. The database attempts to put an exclusive lock on the record for seat 10-C but fails. In the small interval of time between the database read and the attempted database write, someone else has reserved that seat, and it is no longer available.

This scenario could clearly happen with READ COMMITTED isolation. Is this a problem? Many airlines would think that it is not. Although such a sequence of events is possible, it also tends to be extremely rare. When it does occur, it is not a big deal. The traveler just directs a few choice words at the computer (the universal scapegoat) and successfully selects another seat. In exchange, all the travelers who use the reservation system benefit from faster response to their actions, which might be considered to be a reasonable trade-off.

Be sure to think through the possible problems that could occur if you weaken the isolation level of your database and the consequences that follow from those problems. If you do decide to weaken the isolation level, it should be with full knowledge of the consequences.

Controlling lock granularity

In most systems, row-level locking, which is fine-grained, is the default. This is the best choice for maximizing throughput in an online transaction environment, such as an airline reservation system or a banking system. It’s not necessarily best in a system that runs long transactions that involve most of the rows in a table, however. In that kind of environment, a table lock — one with lower overhead than a large number of row locks — could well deliver better overall throughput. Concurrent transactions could be delayed to a lesser extent than they would be with row-level locking.

Don’t assume that the finest-grained locking setting is the best. Consider the types of jobs that are typically run, and choose lock granularity accordingly.

Scheduling DDL statements correctly

Data Definition Language (DDL) statements such as CREATE TABLE, DROP INDEX, and ALTER TABLE operate on the system catalog. Because these operations are of such a fundamental nature, ordinary traffic comes to a standstill while they are active. In any normal installation, you are going to need to run some DDL statements from time to time. Just keep in mind the way that they monopolize system resources, and schedule them at a time when your normal transaction volume is light.

Partitioning insertions

Sequential insertion of records can be a bottleneck. If multiple transactions are inserting records sequentially into the tail end of a table, all of them will be hitting the same buffer page at about the same time and running into page locks. One way to relieve the congestion is to partition insertions into the table across different pages or even different disks. One way to achieve this result is to set up a clustering index based on something other than the time of insertion. This method spreads out the inserted records to different pages. I define clustered indexes in Book 2, Chapter 3, and will discuss their effect on performance in Book 7, Chapter 1.

Cooling hot spots

Hot spots are those records, pages, or tables that everybody wants access to at the same time. When a lengthy transaction acquires a lock on a hot item, everybody else suffers.

You can do a couple of things to lower the “temperature” of chronic hot spots:

• Partition transactions as described in the preceding section.
• Access hot spots as late as possible in a long transaction. Within a transaction, you have some control over the order in which you do things. When possible, place a lock on a hot resource as late as possible. This method makes the overheated resource unavailable for the shortest amount of time.

Tuning the deadlock interval

Earlier in this chapter, in the section titled “Deadlock,” I mention deadlock as being a possible problem, even when you are running with a serializable isolation level. The common solution to the deadlock problem starts when the system senses that two or more transactions have not made any progress for an extended period. To break the deadlock, the system forces the abort and rollback of one or perhaps all of the transactions involved. The deadlock interval is the period that the system waits before allowing an aborted transaction to restart. Clearly, you don’t want to give all the aborted transactions the same deadlock interval. Doing so would just be asking for another deadlock. Even if only one transaction is aborted, restarting it too soon could conflict with a lengthy transaction that is still running.

So how do you choose a good deadlock interval? There is no one good answer, although the interval should have an element of randomness so that you don’t assign the same interval to both participants in a deadlock. Make an educated guess based on the types of transactions that you are running. If excessive deadlocks ensue, try changing the interval or the average difference between the deadlock interval of one aborted transaction and the deadlock interval of the other participant in the deadly embrace that started the whole mess.