深入解析Microsoft SQL Server 2008

需积分: 9 20 浏览量更新于2024-07-15 收藏 893KB PDF 举报

"Microsoft SQL Server 2008 Internals.pdf" 本文档是关于Microsoft SQL Server 2008内部工作原理的专业书籍，由多位知名专家撰写，包括Conor Cunningham等。书中深入探讨了SQL Server 2008的架构、配置以及其各个组件的详细信息，对于数据库管理员和开发人员来说是一份宝贵的参考资料。文章引用次数为3次，阅读量达到1096次。作者们在研究领域有着丰富的经验和专业知识，如Conor Cunningham在英国诺丁汉大学有7篇出版物和38次引用。文档的原始内容由Conor Cunningham于2015年9月7日上传，并请求对下载的文件进行增强。书中包含以下主要内容： 1. **SQL Server 2008架构与配置**：介绍了SQL Server 2008的不同版本，如企业版、标准版等。此外，还详细阐述了SQL Server中的元数据，包括兼容性视图、目录视图和其他元数据，这些都是理解数据库结构和操作的关键。 2. **SQL Server引擎组件**：这部分详细讨论了SQL Server引擎的组成部分，包括用于观察引擎行为的各种协议，以及引擎如何处理关系型数据的逻辑部分——关系引擎，以及负责数据存储的物理部分——存储引擎。 3. **SQL Server操作系统（SQLOS）**：SQLOS是SQL Server的一个核心组件，负责资源调度、内存管理和线程管理等关键任务。这一部分深入解析了SQLOS的工作原理，对于优化数据库性能至关重要。书中的目录预览还包括其他章节，但未在提供的内容中完整列出。完整书籍可能涵盖了如锁、事务管理、查询优化、索引、备份恢复策略等更多主题。通过访问Microsoft Learning（http://www.microsoft.com/learning/en/us/books/12967.aspx），可以获取更多关于此书的信息，进一步学习SQL Server 2008的内部运作机制。了解SQL Server 2008的内部工作原理对于任何希望提高数据库性能、解决复杂问题或设计高效数据库解决方案的人来说都是必要的。这本书不仅提供了理论知识，还提供了实践经验，有助于读者提升在SQL Server环境中的专业技能。

587

Chapter 10

Transactions and Concurrency

Kalen Delaney

Concurrency can be deﬁned as the ability of multiple processes to access or change shared

data at the same time. The greater the number of concurrent user processes that can be active

without interfering with each other, the greater the concurrency of the database system.

Concurrency is reduced when a process that is changing data prevents other processes from

reading that data or when a process that is reading data prevents other processes from

changing that data. I use the terms reading or accessing to describe the impact of using

the SELECT statement on your data. Concurrency is also affected when multiple processes

attempt to change the same data simultaneously and they cannot all succeed without

sacriﬁcing data consistency. I use the terms modifying, changing, or writing to describe the

impact of using the INSERT, UPDATE, MERGE, or DELETE statements on your data. (Note that

MERGE is a new data modiﬁcation statement in SQL Server 2008, and you can think of it as a

combination of INSERT, UPDATE, and DELETE.)

In general, database systems can take two approaches to managing concurrent data access:

optimistic or pessimistic. Microsoft SQL Server 2008 supports both approaches. Pessimistic

concurrency was the only concurrency model available before SQL Server 2005. As of SQL

Server 2005, you specify which model to use by using two database options and a SET option

called TRANSACTION ISOLATION LEVEL.

After I describe the basic differences between the two models, we look at the ﬁve possible

isolation levels in SQL Server 2008, as well as the internals of how SQL Server controls

concurrent access using each model. We look at how to control the isolation level, and we

look at the metadata that shows you what SQL Server is doing.

Concurrency Models

In either concurrency model, a conﬂict can occur if two processes try to modify the same

data at the same time. The difference between the two models lies in whether conﬂicts can

be avoided before they occur or can be dealt with in some manner after they occur.

Pessimistic Concurrency

With pessimistic concurrency, the default behavior is for SQL Server to acquire locks to

block access to data that another process is using. Pessimistic concurrency assumes that

enough data modiﬁcation operations are in the system that any given read operation is likely

588 Microsoft SQL Server 2008 Internals

affected by a data modiﬁcation made by another user. In other words, the system behaves

pessimistically and assumes that a conﬂict will occur. Pessimistic concurrency avoids conﬂicts

by acquiring locks on data that is being read, so no other processes can modify that data. It

also acquires locks on data being modiﬁed, so no other processes can access that data for

either reading or modifying. In other words, readers block writers and writers block readers

in a pessimistic concurrency environment.

Optimistic Concurrency

Optimistic concurrency assumes that there are sufﬁciently few conﬂicting data modiﬁcation

operations in the system that any single transaction is unlikely to modify data that another

transaction is modifying. The default behavior of optimistic concurrency is to use row

versioning to allow data readers to see the state of the data before the modiﬁcation occurs.

Older versions of data rows are saved, so a process reading data can see the data as it was

when the process started reading and not be affected by any changes being made to that

data. A process that modiﬁes the data is unaffected by processes reading the data because

the reader is accessing a saved version of the data rows. In other words, readers do not block

writers and writers do not block readers. Writers can and will block writers, however, and this

is what causes conﬂicts. SQL Server generates an error message when a conﬂict occurs, but it

is up to the application to respond to that error.

Transaction Processing

No matter what concurrency model you’re working with, an understanding of transactions

is crucial. A transaction is the basic unit of work in SQL Server. Typically, it consists of several

SQL commands that read and update the database, but the update is not considered ﬁnal

until a COMMIT command is issued (at least for an explicit transaction). In general, when I

talk about a modiﬁcation operation or a read operation, I am talking about the transaction

that performs the data modiﬁcation or the read, which is not necessarily a single SQL

statement. When I say that writers will block readers, I mean that so long as the transaction that

performed the write operation is active, no other process can read the modiﬁed data.

The concept of a transaction is fundamental to understanding concurrency control. The

mechanics of transaction control from a programming perspective are beyond the scope

of this book, but I discuss basic transaction properties. I also go into detail about the

transaction isolation levels because that has a direct impact on how SQL Server manages the

data being accessed in your transactions.

An implicit transaction is any individual INSERT, UPDATE, DELETE, or MERGE statement. (You

can also consider SELECT statements to be implicit transactions, although SQL Server does

not write to the log when SELECT statements are processed.) No matter how many rows are

affected, the statement must exhibit all the ACID properties of a transaction, which I tell you

Chapter 10 Transactions and Concurrency 589

about in the next section. An explicit transaction is one whose beginning is marked with a

BEGIN TRAN statement and whose end is marked by a COMMIT TRAN or ROLLBACK TRAN

statement. Most of the examples I present use explicit transactions because it is the only way

to show the state of SQL Server in the middle of a transaction. For example, many types of

locks are held for only the duration of the transaction. I can begin a transaction, perform some

operations, look around in the metadata to see what locks are being held, and then end the

transaction. When the transaction ends, the locks are released; I can no longer look at them.

ACID Properties

Transaction processing guarantees the consistency and recoverability of SQL Server databases.

It ensures that all transactions are performed as a single unit of work—even in the presence

of a hardware or general system failure. Such transactions are referred to as having the ACID

properties, with ACID standing for atomicity, consistency, isolation, and durability. In addition

to guaranteeing that explicit multistatement transactions maintain the ACID properties, SQL

Server guarantees that an implicit transaction also maintains the ACID properties.

Here’s an example in pseudocode of an explicit ACID transaction:

BEGIN TRANSACTION DEBIT_CREDIT

Debit savings account $1000

Credit checking account $1000

COMMIT TRANSACTION DEBIT_CREDIT

Now let’s take a closer look at each of the ACID properties.

Atomicity

SQL Server guarantees the atomicity of its transactions. Atomicity means that each

transaction is treated as all or nothing—it either commits or aborts. If a transaction commits,

all its effects remain. If it aborts, all its effects are undone. In the preceding DEBIT_CREDIT

example, if the savings account debit is reﬂected in the database but the checking account

credit isn’t, funds essentially disappear from the database—that is, funds are subtracted from

the savings account but never added to the checking account. If the reverse occurs (if the

checking account is credited and the savings account is not debited), the customer’s checking

account mysteriously increases in value without a corresponding customer cash deposit or

account transfer. Because of the atomicity feature of SQL Server, both the debit and credit must

be completed or else neither event is completed.

Consistency

The consistency property ensures that a transaction won’t allow the system to arrive at an

incorrect logical state—the data must always be logically correct. Constraints and rules are

honored even in the event of a system failure. In the DEBIT_CREDIT example, the logical rule

590 Microsoft SQL Server 2008 Internals

is that money can’t be created or destroyed: a corresponding, counterbalancing entry must

be made for each entry. (Consistency is implied by, and in most situations redundant with,

atomicity, isolation, and durability.)

Isolation

Isolation separates concurrent transactions from the updates of other incomplete transactions.

In the DEBIT_CREDIT example, another transaction can’t see the work in progress while the

transaction is being carried out. For example, if another transaction reads the balance of the

savings account after the debit occurs, and then the DEBIT_CREDIT transaction is aborted,

the other transaction is working from a balance that never logically existed.

SQL Server accomplishes isolation among transactions automatically. It locks data or creates

row versions to allow multiple concurrent users to work with data while preventing side

effects that can distort the results and make them different from what would be expected if

users were to serialize their requests (that is, if requests were queued and serviced one at a

time). This serializability feature is one of the isolation levels that SQL Server supports. SQL

Server supports multiple isolation levels so that you can choose the appropriate tradeoff

between how much data to lock, how long to hold locks, and whether to allow users access

to prior versions of row data. This tradeoff is known as concurrency vs. consistency.

Durability

After a transaction commits, the durability property of SQL Server ensures that the effects

of the transaction persist even if a system failure occurs. If a system failure occurs while a

transaction is in progress, the transaction is completely undone, leaving no partial effects

on the data. For example, if a power outage occurs in the middle of a transaction before the

transaction is committed, the entire transaction is rolled back when the system is restarted.

If the power fails immediately after the acknowledgment of the commit is sent to the calling

application, the transaction is guaranteed to exist in the database. Write-ahead logging and

automatic rollback and roll-forward of transactions during the recovery phase of SQL Server

startup ensure durability.

Transaction Dependencies

In addition to supporting all four ACID properties, a transaction might exhibit several

other behaviors. Some people call these behaviors “dependency problems” or “ consistency

problems,” but I don’t necessarily think of them as problems. They are merely possible

behaviors, and except for lost updates, which are never considered desirable, you can

determine which of these behaviors you want to allow and which you want to avoid.

Your choice of isolation level determines which of these behaviors is allowed.

剩余89页未读，继续阅读

weixin_55287800

粉丝: 0
资源: 5

深入解析Microsoft SQL Server 2008

Microsoft SQL Server 2008 Internals_中文

Microsoft SQL Server 2008 Internals (PDF 高清版)

SQL Server数据库索引设计的研究.pdf

基于SQL Server数据库的水雨情数据查询优化应用.pdf

Microsoft SQL Server 2008 - T-SQL Querying March 2009

实现安全机制内部结构的协调,优化SQL Server数据库的应用性能.pdf

深入理解Microsoft SQL Server 2008

PDF-MicrosoftSQLServer2012Internals-英文版.rar

[SQL Server] Microsoft SQL Server 2012 技术内幕 (英文版)

深入解析SQL Server 2008

最新资源