Databases Reference
In-Depth Information
teller's terminal stops working, then you expect your personal data to remain unchanged. In a data-
base, locks help to achieve this, by ensuring that a transaction has exclusive access to anything that
is being changed, so that it is either committed or rolled back completely. Anything short of that
would break this very basic property of transactions.
Consistency
Databases enforce logic in many different ways. When a change is attempted, it can't be allowed to
occur until the system is satisi ed that no rules are going to be broken. For example, suppose you
remove a value from a table but there are foreign keys referring to that column. The system must
verify that these kinds of associations are handled before it can agree to that change; but in order to
perform those checks and potentially roll them back if something has gone wrong, locks are needed.
For another example, it should be impossible to delete a row while something else is being inserted
in another table that relies on it.
Isolation
When the database engine inserts values into a table, nothing else should be able to change those
values at the same time. Similarly, if the database engine needs to roll back to a previous state,
nothing else should have affected that state or left it indeterminate. In other words, each action
must happen in isolation from all others.
In terms of what other users see when they look at a transaction, or the data that is being
considered, that's the domain of the isolation level, which is examined in much more detail later in
this chapter. This concept of isolation is very important to understand, as you can exercise a lot of
control over the environment.
Durability
Even if a failure occurs a split-second after your transaction has taken place, you need to be sure
that the transaction has been persisted in the database. This is achieved through one of the most
signii cant aspects of SQL Server — the behavior of the transaction log.
Most experienced database administrators have had to salvage MDF i les, where the databases' data
is stored, from a failed server, only to i nd that the MDF i les alone do not provide enough informa-
tion to recover the databases completely. Ideally, this situation prompts the DBA to learn why, after
which they understand that MDF i les without the accompanying LDF i les (the transaction log) do
not rel ect the whole story.
That's because the transaction log is not like many of the other logs on a Windows server, such as
the Windows Event Log. Those logs record information about what's going on, but only in order to
provide a report of what has happened — typically for troubleshooting purposes. The SQL Server
transaction log is much more than this.
When a transaction takes place, it is recorded in the transaction log. Everything that the transaction
is doing is recorded there, while the changes to the actual data are occurring in memory. Once the
transaction is complete and a commit command is sent, the changes are hardened, which is done in
the transaction log. Locks are released at this point (as shown later in this chapter), but the record
Search WWH ::
Custom Search