Database Reference
In-Depth Information
In other works, I recommend using delayed durability only if you meet all those criteria, with the first being the
most important.
■
out of the four aCid properties, the
isolation
property, which is also used to ensure atomicity and
consistency, is the main cause of blocking in a sQl server database. in sQl server, isolation is implemented using
locks, as explained in the next section.
Note
Locks
When a session executes a query, SQL Server determines the database resources that need to be accessed; and,
if required, the lock manager grants different types of locks to the session. The query is blocked if another session
has already been granted the locks; however, to provide both transaction isolation and concurrency, SQL Server uses
different lock granularities, as explained in the sections that follow.
Lock Granularity
SQL Server databases are maintained as files on the physical disk. In the case of a traditional nondatabase file such
as an Excel file on a desktop machine, the file may be written to by only one user at a time. Any attempt to write to the
file by other users fails. However, unlike the limited concurrency on a nondatabase file, SQL Server allows multiple
users to modify (or access) contents simultaneously, as long as they don't affect one another's data consistency. This
decreases blocking and improves concurrency among the transactions.
To improve concurrency, SQL Server implements lock granularities at the following resource levels and in
this order:
•
Row (
RID
)
KEY
)
•
Key (
PAG
)
•
Page (
EXT
)
•
Extent (
HoBT
)
•
Heap or B-tree (
TAB
)
•
Table (
FIL
)
•
File (
APP
)
•
Application (
MDT
)
•
MetaData (
AU
)
•
Allocation Unit (
DB
)
•
Database (
Let's take a look at these lock levels in more detail.
