Q&A
| Q1: | Can I lock tables and rows myself? |
| A1: | No. Locking is automatic. You can execute statements that obtain locks, and you can use locking hints to change locking modes and levels, but you cannot explicitly lock tables or rows. |
| Q2: | When does it make sense to use dirty reads? It sounds like that will just create problems. |
| A2: | Dirty reads (isolation level read uncommitted) help you perform large-scale selects against a database without running into locks or creating locks of your own. I use them to monitor performance on a production system, with the idea that I don't want to slow people down while I look around. If you need absolutely correct answers, you should avoid read uncommitted, but dirty reads are a lifesaver for quick, large selects. |
| Q3: | In the locking hints, you list both nolock and read uncommitted, as well as holdlock and serializable. Why are there multiple terms for the same hints? |
| A3: | The terms nolock and holdlock are left over from earlier versions of SQL Server. In those earlier versions, there was no formal set of transaction isolation levels. You had only a few keywords to manipulate locks in special situations. They appear in this version of SQL Server to maintain compatibility with legacy programs. Avoid the older terms in favor of the ANSI standard ones. |
| Q4: | Why not always set transaction isolation as high as possible? Won't that improve data integrity? |
| A4: | There's no question that the higher the transaction isolation level, the greater the guarantee of data integrity. The problem is that higher isolation levels cause performance and concurrency problems. The more resources you lock and the more restrictive those locks, the more likely another user will need to wait for a lock to clear. It's a balancing act. |
| Q5: | Can I always avoid deadlocks? |
| A5: | Not really. Most heavily used systems will have some level of deadlocking. Some deadlocks occur within a single table when two processes need to perform maintenance on a single index page. (This is more common when many users are actively modifying a small table.) |
| Q6: | Can I choose which process becomes the deadlock victim when a deadlock occurs? |
| A6: | No. The server selects a victim on the basis of accumulated CPU time. |
..................Content has been hidden....................
You can't read the all page of ebook, please click here login for view all page.