Chia sẻ: Thanh Cong | Ngày: | Loại File: PDF | Số trang:50

lượt xem


Mô tả tài liệu
  Download Vui lòng tải xuống để xem tài liệu đầy đủ

Tham khảo tài liệu 'mastering sql server 2000- p19', công nghệ thông tin, cơ sở dữ liệu phục vụ nhu cầu học tập, nghiên cứu và làm việc hiệu quả

Chủ đề:

Nội dung Text: MASTERING SQL SERVER 2000- P19

  1. This page intentionally left blank Please purchase PDF Split-Merge on to remove this watermark.
  2. PA R T VI Advanced Topics LEARN TO: • Use locking • Monitor and optimize SQL Server 2000 • Use replication • Use Analysis Services • Use Microsoft English Query • Troubleshoot
  3. This page intentionally left blank Please purchase PDF Split-Merge on to remove this watermark.
  4. CHAPTER 25 Locking F E AT U R I N G : Why Locking? 924 Isolation Levels 926 Locking Mechanics 927 Viewing Current Locks 931 Deadlocks 936 Customizing Locking Behavior 939 Application Locks 942 Summary 944
  5. O ne of the key features of SQL Server 2000 is that it’s been designed from the start to support many users of the same database at the same time. It’s this support that leads to the need for locking. Locking refers to the ability of the database server to reserve resources such as rows of data or pages of an index for the use of one particular user at a time. In this chapter, we’ll explore the reasons why locking is necessary in multiuser databases and see the details of SQL Server’s locking implementation. Why Locking? It may seem counterintuitive that a multiuser database would require the ability to lock users out of their data. Wouldn’t it make more sense to just let everyone get to the data, so they can get their business done as fast as possible and let the next person use the data? Unfortunately, this doesn’t work, because working with data often takes many operations that require everything to stay consistent. In this section, we’ll dis- cuss the specific problems that locking solves: • Lost updates • Uncommitted dependencies • Inconsistent analysis • Phantom reads We’ll also take a look at concurrency, and explain the difference between opti- mistic and pessimistic concurrency. Lost Updates One of the classic database problems is the lost update. Suppose Joe is on the phone with the Accounting Department of XYZ Corporation, and Mary, who is entering changes of address for customers, happens to find a change of address card for XYZ Corporation at roughly the same time. Both Joe and Mary display the record for XYZ from the Customers table on their computers at the same time. Joe comes to an agree- ment to raise XYZ’s credit limit, makes the change on his computer, and saves the change back to the SQL Server database. A few minutes later, Mary finishes updating XYZ’s address and saves her changes. Unfortunately, her computer didn’t know about the new credit limit (it had read the original credit limit before Joe raised it), so Joe’s change is overwritten without a trace. A lost update can happen anytime two independent transactions select the same row in a table and then update it based on the data that they originally selected. One
  6. WHY LOCKING? 925 way to solve this problem is to lock out the second update. In the example above, if Mary was unable to save changes without first retrieving the changes that Joe made, both the new credit limit and the new address would end up in the Customers table. Uncommitted Dependencies Uncommitted dependencies are sometimes called dirty reads. This problem happens when a record is read while it’s still being updated, but before the updates are final. For example, suppose Mary is entering a change of address for XYZ Corporation through a program that saves each changed field as it’s entered. She enters a wrong street address, then catches herself and goes back to correct it. However, before she can enter the correct address, Mark prints out an address label for the company. Even though Mary puts the correct data in before leaving the company’s record, Mark has read the wrong data from the table. One way to avoid the problem of dirty reads is to lock data while it’s being written, so no one else can read it before the changes are final. Inconsistent Analysis The inconsistent analysis problem is related to the uncommitted dependencies prob- lem. Inconsistent analysis is caused by nonrepeatable reads, which can happen when data is being read by one process while the data’s being written by another process. Suppose Betty is updating the monthly sales figures for each of the company’s divisions by entering new numbers into a row of the Sales table. Even though she puts all the changes on her screen to be saved at once, it takes SQL Server a little time to write the changes to the database. If Roger runs a query to total the monthly sales for the entire company while this data is being saved, the total will include some old data and some new data. If he runs the query again a moment later, it will include all new data and give a different answer. Thus, the original read PA R T was nonrepeatable. Inconsistent analysis can be avoided if reads are not allowed while data is being VI written. Phantom Reads Advanced Topics The final major problem that locking can help solve is the problem of phantom reads. These occur when an application thinks it has a stable set of data, but other applica- tions are inserting rows into the data. Suppose Roger retrieves a query that includes all of the sales for March. If he asks for sales for March 15 twice in a row, he should get the same answer. However, if Mildred was inserting data for March 15, and Roger’s
  7. 926 CHAPTER 25 • LOCKING application read the new data, he might get a different answer the second time. The new data is called phantom data, because it appeared mysteriously even though it wasn’t originally present in the data that was retrieved. Phantom reads can be avoided if some processes are locked out of inserting data into a set of data that another process is using. Optimistic and Pessimistic Concurrency There are two broad strategies for locking in the world of databases. These are referred to as concurrency control methods, because they control when users can work with resources that other users are also manipulating. With optimistic concurrency control, the server makes the assumption that resource conflicts are unlikely. In this case, resources (for example, a row in a table) are locked only while a change is about to be saved. This minimizes the amount of time that resources are locked. However, it increases the chance that another user will make a change in a resource before you can. For example, you might discover when trying to save a change that the data in the table is not the data that you originally read, and need to read the new data and make your change again. With pessimistic concurrency control, resources are locked when they are required and are kept locked throughout a transaction. This avoids many of the problems of optimistic concurrency control, but raises the possibility of deadlocks between processes. We’ll discuss deadlocks later in the chapter. In almost all situations, SQL Server uses pessimistic concurrency control. It’s possi- ble to use optimistic concurrency control by opening tables with a cursor instead of a query. Chapter 8 covers the use of cursors in T-SQL. Isolation Levels The ANSI SQL standard defines four different isolation levels for transactions. These levels specify how tolerant a transaction is of incorrect data. From lowest to highest, the four isolation levels are as follows: • Read Uncommitted • Read Committed • Repeatable Read • Serializable A lower isolation level increases concurrency and decreases waiting for other trans- actions, but increases the chance of reading incorrect data. A higher isolation level
  8. LOCKING MECHANICS 927 decreases concurrency and increases waiting for other transactions, but decreases the chance of reading incorrect data. With the highest level of isolation, transactions are completely serialized, which means that they are completely independent of one another. If a set of transactions is serialized, the transactions can be executed in any order, and the database will always end up in the same state. The default isolation level for SQL Server transactions is Read Committed, but as you’ll see later in this chapter, you can adjust this default for particular transactions. NOTE For a discussion of the properties that define transactions and the T-SQL state- ments that manage transactions, see Chapter 8. Table 25.1 shows which database problems can still occur with each isolation level. TABLE 25.1: ISOLATION LEVELS AND DATABASE PROBLEMS Isolation Level Lost Updates Dirty Reads Nonrepeatable Reads Phantom Reads Read Uncommitted Yes Yes Yes Yes Read Committed Yes No Yes Yes Repeatable Read No No No Yes Serializable No No No No Locking Mechanics PA R T VI To understand the way that SQL Server manages locks and properly interpret the dis- play of locking information in SQL Server Enterprise Manager, you need to under- stand a few technical concepts. In this section, we’ll cover the basics of these concepts, including locking granularity, locking modes, lock escalation, and dynamic locking. Advanced Topics Locking Granularity Locking granularity refers to the size of the resources being locked at any given time. For example, if a user is going to make a change to a single row in a table, it might make sense to lock just that row. However, if that same user were to make changes to
  9. 928 CHAPTER 25 • LOCKING multiple rows in a single transaction, it could make more sense for SQL Server to lock the entire table. The table locking has higher granularity than the row locking. SQL Server 2000 can provide locks on six levels of granularity: RID: RID stands for row ID. A RID lock applies a lock to a single row in a table. Key: Sometimes locks are applied to indexes rather than directly to tables. A key lock locks a single row within an index. Page: A single data page or index page contains 8KB of data. Extent: Internally, SQL Server organizes pages into groups of eight similar pages (either data pages or index pages) called extents. An extent lock thus locks 64KB of data. Table: A table lock locks an entire table. DB: Under exceptional circumstances, SQL Server may lock an entire data- base. For example, when a database is placed into single-user mode for mainte- nance, a DB lock may be used to prevent other users from entering the database. The smaller the lock granularity, the higher the concurrency in the database. For example, if you lock a single row rather than an entire table, other users can work with other rows in the same table. The trade-off is that smaller lock granularity gener- ally means more system resources are devoted to tracking locks and lock conflicts. Locking Modes All locks are not created equal. SQL Server recognizes that some operations need com- plete and absolute access to data, while others merely want to signal that they might change the data. To provide more flexible locking behavior and lower the overall resource use of locking, SQL Server provides the following types of locks (each type has an abbreviation that is used in SQL Server Enterprise Manager): Shared (S): Shared locks are used to ensure that a resource can be read. No transaction can modify the data in a resource while a shared lock is being held on that resource by any other transaction. Update (U): Update locks signal that a transaction intends to modify a resource. An update lock must be upgraded to an exclusive lock before the transaction actually makes the modification. Only one transaction at a time can hold an update lock on a particular resource. This limit helps prevent dead- locking (discussed in more detail later in the chapter). Exclusive (X): If a transaction has an exclusive lock on a resource, no other transaction can read or modify the data in that resource. This makes it safe for the transaction holding the lock to modify the data itself.
  10. LOCKING MECHANICS 929 Intent shared (IS): A transaction can place an intent shared lock on a resource to indicate that the transaction intends to place shared locks on resources at a lower level of granularity within the first resource. For example, a transaction that intends to read a row in a table can place a shared lock on the RID and an intent shared lock on the table itself. Intent shared locks help improve SQL Server performance by making it easier for SQL Server to deter- mine whether a transaction can be granted update or exclusive locks. If SQL Server finds an intent shared lock on the table, SQL Server doesn’t need to examine every RID looking for shared locks on a row-by-row basis. Intent exclusive (IX): A transaction can place an intent exclusive lock on a resource to indicate that the transaction intends to place exclusive locks on resources at a lower level of granularity within the first resource. Shared with intent exclusive (SIX): A transaction can place a shared with intent exclusive lock on a resource to indicate that the transaction intends to read all of the resources at a lower level of granularity within the first resource and modify some of those lower-level resources. Schema modification (Sch-M): SQL Server places schema modification locks on a table when DDL operations such as adding or dropping a column are being performed on that table. Schema modification locks prevent any other use of the table. Schema stability (Sch-S): SQL Server places schema stability locks on a table when compiling a query that is based at least in part on that table. Schema stability locks do not prevent operations on the data in the table, but they do prevent modifications to the structure of the table. Bulk update (BU): SQL Server places bulk update locks on a table when bulkcopying data into the table, if the TABLOCK hint is specified as part of the bulkcopy operation or the table lock on bulk load option is set with sp_tableop- PA R T tion. Bulk update locks allow any process to bulkcopy data into the table, but do not allow any other processes to use the data in the table. VI Later in the chapter, you’ll see how you can use locking hints in T-SQL to specify the exact lock mode that should be used for a particular operation. One of the factors that determines whether a lock can be granted on a resource is Advanced Topics whether another lock already exists on the resource. Here are the rules that SQL Server applies to determine whether a lock can be granted: • If an X lock exists on a resource, no other lock can be granted on that resource. • If an SIX lock exists on a resource, an IS lock can be granted on that resource. • If an IX lock exists on a resource, an IS or IX lock can be granted on that resource.
  11. 930 CHAPTER 25 • LOCKING • If a U lock exists on a resource, an IS or S lock can be granted on that resource. • If an S lock exists on a resource, an IS, S, or U lock can be granted on that resource. • If an IS lock exists on a resource, an IS, S, U, IX, or SIX lock can be granted on that resource. • If an Sch-S lock exists on a resource, any lock except an Sch-M lock can be granted on that resource. • If an Sch-M lock exists on a resource, no other lock can be granted on that resource. • If a BU lock exists on a resource, an Sch-S or a BU lock can be granted on that resource. Lock Escalation SQL Server continuously monitors lock usage to strike a balance between granularity of locks and resources devoted to locking. If a large number of locks on a resource with lesser granularity is acquired by a single transaction, SQL Server might escalate these locks to fewer locks with higher granularity. For example, suppose a process begins requesting rows from a table to read. SQL Server will place shared locks on the RIDs involved, and simultaneously place shared intent locks on the data page or pages holding the rows and the table itself. If the transaction reads most of the rows on a data page, SQL Server will discard the shared locks for the RIDs and place a shared lock on the page itself instead. If the transaction continues to read rows, SQL Server will eventually place the shared lock at the table level, and discard the locks at the page and RID level. The goal is to balance the number of locks that need to be monitored against the need to keep data as available to other processes as possible. SQL Server maintains its own dynamic lock escalation thresholds, and you can neither see nor change these thresholds. However, it’s important to understand that sometimes you might get more locking than you thought you asked for, due to lock escalation. Dynamic Locking SQL Server locking is dynamic. What this means to you as an application developer is that you almost never have to worry about locking. As part of generating the execu- tion plan for a query, SQL Server will determine the type of locks to place when that query is executed. This includes both the locking mode and the locking granularity. Lock escalation is also part of the dynamic locking strategy employed by SQL Server.
  12. VIEWING CURRENT LOCKS 931 Dynamic locking is designed to make life easier for database administrators and users alike. Administrators don’t need to constantly monitor locks (although, as you’ll see in the next section, it is possible to do so), nor do they need to manually establish lock escalation thresholds. Users don’t need to specify a locking mode for queries (though they can use locking hints to do so in special situations). SQL Server’s dynamic locking is usually oriented toward performance. By using the most appropriate level of locks for a particular operation (table locks, page locks, or row locks), SQL Server can minimize the overhead associated with locking and so improve overall performance. Viewing Current Locks As a database administrator, you may find that you need to investigate the locks that are in use on your server. Perhaps users are complaining of poor performance, and you suspect that some application is claiming more locks than it really needs. Or per- haps a resource is locked, and you can’t figure out what process owns the lock. Fortu- nately, SQL Server provides several tools that you can use to see what’s going on with SQL Server locking. In this section, we’ll demonstrate the use of the sp_lock stored procedure and show you how to use SQL Server Enterprise Manager to view locking activity. Using sp_lock If you want a quick snapshot of locking activity within SQL Server, you can run the sp_lock stored procedure in Query Analyzer. By default, any user in the public role can run sp_lock. The output of sp_lock will look something like this: spid dbid ObjId IndId Type Resource Mode Status ——— ——— —————- ——— —— ———————— ———— ——— PA R T 1 1 0 0 DB S GRANT VI 7 14 0 0 DB S GRANT 8 14 0 0 DB S GRANT 9 10 0 0 DB S GRANT 32 22 133575514 0 PAG 1:100 IS GRANT Advanced Topics 32 22 133575514 0 RID 1:96:16 S GRANT 32 22 133575514 0 PAG 1:96 IS GRANT 32 22 133575514 0 RID 1:100:20 S GRANT 32 22 133575514 255 PAG 1:181 IS GRANT 32 22 133575514 255 PAG 1:179 IS GRANT
  13. 932 CHAPTER 25 • LOCKING 32 22 133575514 255 RID 1:181:12 S GRANT 33 2 0 0 EXT 1:80 X GRANT 33 2 0 0 EXT 1:5776 U GRANT 71 14 0 0 PAG 1:113700 IX GRANT 71 14 1218103380 0 TAB IX GRANT The result set from sp_lock includes these columns: spid: The SQL Server process ID. SQL Server assigns a unique number to each active process. dbid: The SQL Server database ID for the database containing the lock. To see the database IDs on your server matched to database names, you can exe- cute SELECT * FROM master..sysdatabases. ObjId: The SQL Server object ID for the object being locked. You can retrieve the name of the object by executing SELECT object_name(ObjId). IndId: The SQL Server index ID for the index being locked. Type: The type of object being locked. This can be DB (database), FIL (file), IDX (index), PG (page), KEY (key), TAB (table), EXT (extent), or RID (row iden- tifier). Resource: Identifying information for the exact object being locked. Mode: The lock mode. Status: The lock request status. GRANT indicates that the lock was granted, WAIT indicates that the lock is blocked by a lock held by another process, and CNVT shows that a lock is trying to change modes (for example, shared to update) but that the change is blocked by a lock held by another process. There are two primary uses for the sp_lock stored procedure. First, you might think there’s a deadlock problem on your server and need to see all the locks on the server. If the sp_lock output contains many locks with a status of WAIT or CNVT, you should suspect a deadlock. Second, sp_lock can help you see the actual locks placed by a particular SQL state- ment, because you can retrieve the locks for a particular process. For example, con- sider this T-SQL batch: USE Northwind BEGIN TRANSACTION INSERT INTO Customers (CustomerID, CompanyName) VALUES (‘ZYXXX’, ‘ZYXXX Industries’) EXEC sp_lock @@spid ROLLBACK TRANSACTION
  14. VIEWING CURRENT LOCKS 933 After setting the database to use, this batch first begins a transaction, because locks are held for the duration of the current transaction. By holding the transaction open, you can examine the locks before SQL Server releases them. The next statement (the INSERT) is the one that will actually acquire the locks. The next statement is the form of sp_lock to show the locks for the current transaction. The @@spid system variable retrieves the spid for the current transaction. When you supply a parameter to sp_lock, it retrieves only the locks for that spid. Finally, the batch rolls back the trans- action so that no actual change is made to the database. Figure 25.1 shows the result of running this batch. As you can see, even a single SQL statement might need to lock many resources to properly execute. In the case of an INSERT statement, the indexes for the table must all be locked to insert the new row. FIGURE 25.1 Using sp_lock to investigate locks PA R T VI Advanced Topics TIP You’ll see several locks on dbid 1 in this figure. Those are the locks in the master data- base that the sp_lock stored procedure needs to retrieve the information that it displays.
  15. 934 CHAPTER 25 • LOCKING Using SQL Server Enterprise Manager You can also use SQL Server Enterprise Manager to display locking information. Of course, all of the information that Enterprise Manager will display is also available via sp_lock and other T-SQL statements, but you may find the graphical view in Enter- prise Manager more convenient. The locking information in Enterprise Manager is displayed in three nodes, all of them children of the Current Activity node in the Management folder: • Process Info • Locks/Process ID • Locks/Object Figure 25.2 shows some of this information on a test server. FIGURE 25.2 Displaying lock infor- mation in SQL Server Enterprise Manager The Process Info node displays the following information for each process cur- rently running on the server: spid: The process ID assigned to the process by SQL Server. This column also displays an icon that indicates the current status of the process. User: The SQL Server user who owns the process. Database: The database containing the data that the process is using.
  16. VIEWING CURRENT LOCKS 935 Status: Either Background, Sleeping, or Runnable. Background processes are generally automatic jobs that require no user intervention. Sleeping processes are awaiting a command. Runnable processes are actively manipulating data. Open Transactions: The number of open transactions that are a part of the process. Command: The most recent SQL Server command executed by the process. Application: The application name (if any) that the process has registered with SQL Server. Wait Type: Shows whether a process is waiting for another process to com- plete. Wait Resource: The name of the resource (if any) for which the process is waiting. CPU: The number of milliseconds of CPU time that have been used by the process. Physical IO: The number of physical input or output operations that have been performed by the process. Memory Usage: The number of kilobytes of memory in use by the process. Login Time: The date and time that the process connected to SQL Server. Last Batch: The date and time that the process last sent a command to SQL Server. Host: The server where the process is running. Network Library: The network library being used for connection to SQL Server by the process. Network Address: The physical network address of the process. Blocked By: The spid (if any) of another process that is blocking this PA R T process. Blocking: The spid (if any) of another process that is being blocked by this VI process. The Locks/Process ID node includes one child node for each process currently hold- ing locks on the server. Each of these child nodes displays the following information: Advanced Topics Object: The object being locked. This column also displays the SQL Server Enterprise Manager icon corresponding to the type of object being locked. Lock Type: The type of object being locked. This can be DB (database), FIL (file), IDX (index), PG (page), KEY (key), TAB (table), EXT (extent), or RID (row identifier).
  17. 936 CHAPTER 25 • LOCKING Mode: The locking mode of the lock. Status: GRANT, CNVT, or WAIT. Owner: Either Sess for a session lock or Xact for a transaction lock. Index: The index (if any) being locked. Resource: The resource (if any) being locked. The Locks/Object ID node includes one child node for each object that is currently locked on the server. Each of these child nodes displays the following information: spid: The SQL Server process ID of the process holding this lock. Lock Type: The type of object being locked. This can be DB (database), FIL (file), IDX (index), PG (page), KEY (key), TAB (table), EXT (extent), or RID (row identifier). Mode: The locking mode of the lock. Status: GRANT, CNVT, or WAIT. Owner: Either Sess for a session lock or Xact for a transaction lock. Index: The index (if any) being locked. Resource: The resource (if any) being locked. Deadlocks It’s possible for one process to block another process from acquiring a lock that the second process needs to succeed. For example, suppose that one application launches this batch: BEGIN TRANSACTION UPDATE Products SET Price = Price * 1.1 COMMIT TRANSACTION A moment later, a second process launches this batch: BEGIN TRANSACTION UPDATE Products SET Price = Price * 2 COMMIT TRANSACTION Assuming that nothing else is happening on the server at the time, the first process will ask for and receive an exclusive lock on the Products table. The second process will also ask for an exclusive lock on the Products table, but because only one process can have an exclusive lock on a table at a time, SQL Server won’t grant this lock. Instead, the second process’s lock request will be placed in the WAIT state by SQL Server. When
  18. DEADLOCKS 937 the first update finishes, the second process will be given its lock and can complete its update. Blocking is a normal consequence of locking resources. In this case, both processes are able to complete their work. SQL Server uses locking to ensure that they do their work in an orderly fashion. A deadlock is a situation in which multiple processes simultaneously require locks that are being held by other processes. For example, suppose the first transaction is as follows: BEGIN TRANSACTION UPDATE Products SET Price = Price * 1.1 UPDATE Orders SET Quantity = Quantity * 2 COMMIT TRANSACTION At the same time, a second application submits this batch: BEGIN TRANSACTION UPDATE Orders SET Quantity = Quantity + 1 UPDATE Products SET Price = Price * 2 COMMIT TRANSACTION If the timing is just right (or, depending on your point of view, just wrong), these batches will lead to this sequence of events: 1. The first application submits batch #1. 2. The second application submits batch #2. 3. The first application asks for and receives an exclusive lock on the Products table. 4. The second application asks for and receives an exclusive lock on the Orders table. 5. The first application asks for a lock on the Orders table, and this lock request is placed in the WAIT state, because the second application has a lock on the PA R T Orders table already. VI 6. The second application asks for a lock on the Products table, and this lock request is placed in the WAIT state, because the first application has a lock on the Products table already. That’s a deadlock. Neither application can complete its transaction, because each is Advanced Topics waiting for the other to release a lock. If something isn’t done about this situation, the locks will persist forever, and both applications will be hung.
  19. 938 CHAPTER 25 • LOCKING Deadlocks need not involve only two applications. It’s possible to have a chain of applications involving three or more transactions where each is waiting for a lock held by one of the others to be released, and all the applications are mutually deadlocked. SQL Server is designed to detect and eliminate deadlocks automatically. The server periodically scans all processes to see which ones are waiting for lock requests to be fulfilled. If a single process is waiting during two successive scans, SQL Server starts a more detailed search for deadlock chains. If it finds that a deadlock situation exists, SQL Server automatically resolves the deadlock. It does this by determining which transaction would be least expensive for SQL Server to undo and designating that transaction as the deadlock victim. SQL Server then automatically rolls back all the work that was performed by that transac- tion and returns error 1205: “Your transaction (process spid) was deadlocked with another process and has been chosen as the deadlock victim. Rerun your transaction.” If you like, you can tell SQL Server that your transaction should be preferentially chosen as the deadlock victim even if it’s not the least expensive transaction to roll back. You can do this by issuing the following statement in your batch: SET DEADLOCK_PRIORITY LOW To minimize the chance of deadlocks in your own applications, follow these rules: • Always access objects in the same order. For example, if the second transaction in the deadlock example above had updated the Products table before the Orders table, the deadlock would not have been possible. One of the processes would have locked and then released both tables, freeing the other process to do the same. • Keep transactions short. Remember that locks are always held for the duration of a transaction. The longer your application keeps a lock on an object and the more objects that it locks, the greater the chance that it will get into a deadlock situation with another application. One consequence of this rule is that you should not lock an object and then wait for user input. Hundreds or thousands of other processes could try to use the object while the user is thinking, because computers work so much more quickly than people do. • Use T-SQL to customize the locking behavior of your application to use the low- est possible isolation level and to hold only necessary locks. We’ll cover the ways in which you can customize locking behavior in the next section.
  20. CUSTOMIZING LOCKING BEHAVIOR 939 Customizing Locking Behavior Although SQL Server does an excellent job of handling locks automatically and trans- parently to the application developer, it’s not perfect for every application. Sometimes you’ll want to customize the locking behavior that SQL Server uses for your applica- tions. You can do this in four ways: • By marking a transaction as a preferential deadlock victim • By setting a lock timeout • By setting a transaction isolation level • By supplying a locking hint We covered the use of SET DEADLOCK_PRIORITY LOW to mark a transaction as a preferential deadlock victim earlier in the chapter. In this section, we’ll look at the other ways that you can customize locking behavior in your applications. Setting the Lock Timeout By default, there is no lock timeout for SQL Server transactions. That is, if a transac- tion is blocked (not deadlocked) waiting for another transaction to release a lock, the blocked transaction will wait forever. This is not always the best possible behavior, though it does maximize the chance of the blocked transaction being completed eventually. If you like, you can set a lock timeout within a transaction. To do this, use the fol- lowing T-SQL statement: SET LOCK_TIMEOUT timeout_period The lock timeout period is supplied in milliseconds. For example, to set a 2-second lock timeout, you could execute the following statement: PA R T SET LOCK_TIMEOUT 2000 SQL Server also supplies a global variable @@lock_timeout that allows an applica- VI tion to retrieve the current lock timeout. Figure 25.3 shows the use of both SET LOCK_TIMEOUT and @@lock_timeout within a T-SQL batch. Advanced Topics
Đồng bộ tài khoản