Datasheet

Exclusive Locks

SQL Server typically issues Exclusive locks when a modification is executed. To change the value of a

field in a row SQL Server grants exclusive access of that row to the calling process. This exclusive access

prevents a process from any concurrent transaction or connection from reading, updating, or deleting

the data being modified. Exclusive locks are not compatible with any other lock types.

Intent Locks

SQL Server issues Intent locks to prevent a process from any concurrent transaction or connection from

placing a more exclusive lock on a resource that contains a locked resource from a separate process. For

example, if you execute a transaction that updates a single row in a table, SQL Server grants the transac-

tion an Exclusive lock on the row, but also grants an Intent lock on the table containing the row. This pre-

vents another process from placing an Exclusive lock on the table.

Here is an analogy I often use to explain the Intent lock behavior in SQL programming classes: You

check in to room 404 at the SQL Hotel. You now have exclusive use of Room 4 on the fourth floor. No

other hotel patron will be allowed access to this room. In addition, no other patron will be allowed to

buy out every room in the hotel because you have already been given exclusive control to one of the

rooms. You have what amounts to an Intent Exclusive lock on the hotel and an Exclusive lock on Room

404. Intent locks are compatible with any less-exclusive lock, as illustrated in the following table on lock

compatibility.

Existing Granted Lock

Requested Lock Type IS S U IX X

Intent Shared (IS) Yes Yes Yes Yes No

Shared (S) Yes Yes Yes No No

Update(U) Yes Yes No No No

Intent Exclusive (IX) Yes No No Yes No

Exclusive (X) No No No No No

SQL Server and Other Products

Microsoft has plenty of competition in the client/server database world and SQL Server is a relatively

young product by comparison. However, it has enjoyed wide acceptance in the industry due to its ease

of use and attractive pricing. If our friends at Microsoft know how to do anything exceptionally well, it’s

taking a product to market so it becomes very mainstream and widely accepted.

Microsoft SQL Server

Here is a short history lesson on Microsoft’s SQL Server. SQL Server was originally a Sybase product cre-

ated for IBM’s OS/2 platform. Microsoft Engineers worked with Sybase and IBM but eventually with-

drew from the project. Microsoft licensed the Sybase SQL Server code and ported the product to work

Chapter 1

04_57955x ch01.qxd 9/19/05 12:44 PM Page 18