社区
Delphi
帖子详情
[SQL Server]Divide by Zero error encountered????
narduo
2002-05-29 04:38:34
这是什么错误?系统一运行时就出错,程序没改过啊,昨天还好好的,急!!!
...全文
973
3
打赏
收藏
[SQL Server]Divide by Zero error encountered????
这是什么错误?系统一运行时就出错,程序没改过啊,昨天还好好的,急!!!
复制链接
扫一扫
分享
转发到动态
举报
写回复
配置赞助广告
用AI写文章
3 条
回复
切换为时间正序
请发表友善的回复…
发表回复
打赏红包
narduo
2002-05-30
打赏
举报
回复
是数据库中有空记录,删掉就OK了:-)
narduo
2002-05-29
打赏
举报
回复
原来一直好好的啊,通过SQL EXPLORER连接数据库正常啊
taidy
2002-05-29
打赏
举报
回复
好像是SQL Server的驱动的错,没有遇到过,关注!
微软内部资料-
SQL
性能优化3
Cont
en
ts Overview 1 Lesson 1: Concepts – Locks and Lock Manager 3 Lesson 2: Concepts – Batch and Transaction 31 Lesson 3: Concepts – Locks and Applications 51 Lesson 4: Information Collection and Analysis 63 Lesson 5: Concepts – Formulating and Implem
en
ting Resolution 81 Module 4: Troubleshooting Locking and Blocking Overview At the
en
d of this module, you will be able to: Discuss how lock manager uses lock mode, lock resources, and lock compatibility to achieve transaction isolation. Describe the various transaction types and how transactions differ from batches. Describe how to troubleshoot blocking and locking issues. Analyze the output of blocking scripts and Microsoft®
SQL
Server
™ Profiler to troubleshoot locking and blocking issues. Formulate hypothesis to resolve locking and blocking issues. Lesson 1: Concepts – Locks and Lock Manager This lesson outlines some of the common causes that contribute to the perception of a slow
server
. What You Will Learn After completing this lesson, you will be able to: Describe locking architecture used by
SQL
Server
. Id
en
tify the various lock modes used by
SQL
Server
. Discuss lock compatibility and concurr
en
t access. Id
en
tify differ
en
t types of lock resources. Discuss dynamic locking and lock escalation. Differ
en
tiate locks, latches, and other
SQL
Server
internal “locking” mechanism such as spinlocks and other synchronization objects. Recomm
en
ded Reading Chapter 14 “Locking”, Inside
SQL
Server
2000 by Kal
en
Delaney SOX000821700049 –
SQL
7.0 How to interpret lock resource Ids SOX000925700237 – TITLE: Lock escalation in
SQL
7.0 SOX001109700040 – INF: Queries with PREFETCH in the plan hold lock until the
en
d of transaction Locking Concepts Delivery Tip Prior to delivering this material, test the class to see if they fully understand the differ
en
t isolation levels. If the class is not confid
en
t in their understanding, review app
en
dix A04_Locking and its accompanying PowerPoint® file. Transactions in
SQL
Server
provide the ACID properties: Atomicity A transaction either commits or aborts. If a transaction commits, all of its effects remain. If it aborts, all of its effects are undone. It is an “all or nothing” operation. Consist
en
cy An application should maintain the consist
en
cy of a database. For example, if you defer constraint checking, it is your responsibility to
en
sure that the database is consist
en
t. Isolation Concurr
en
t transactions are isolated from the updates of other incomplete transactions. These updates do not constitute a consist
en
t state. This property is oft
en
called serializability. For example, a second transaction traversing the doubly linked list m
en
tioned above would see the list before or after the insert, but it will see only complete changes. Durability After a transaction commits, its effects will persist ev
en
if there are system failures. Consist
en
cy and isolation are the most important in describing
SQL
Server
’s locking model. It is up to the application to define what consist
en
cy means, and isolation in some form is needed to achieve consist
en
t results.
SQL
Server
uses locking to achieve isolation. Definition of Dep
en
d
en
cy: A set of transactions can run concurr
en
tly if their outputs are disjoint from the union of one another’s input and output sets. For example, if T1 writes some object that is in T2’s input or output set, there is a dep
en
d
en
cy betwe
en
T1 and T2. Bad Dep
en
d
en
cies These include lost updates, dirty reads, non-repeatable reads, and phantoms. ANSI
SQL
Isolation Levels An isolation level determines the degree to which data is isolated for use by one process and guarded against interfer
en
ce from other processes. Prior to
SQL
Server
7.0, REPEATABLE READ and SERIALIZABLE isolation levels were synonymous. There was no way to prev
en
t non-repeatable reads while not prev
en
ting phantoms. By default,
SQL
Server
2000 operates at an isolation level of READ COMMITTED. To make use of either more or less strict isolation levels in applications, locking can be customized for an
en
tire session by setting the isolation level of the session with the SET TRANSACTION ISOLATION LEVEL statem
en
t. To determine the transaction isolation level curr
en
tly set, use the DBCC USEROPTIONS statem
en
t, for example: USE pubs GO SET TRANSACTION ISOLATION LEVEL REPEATABLE READ GO DBCC USEROPTIONS GO Multigranular Locking Multigranular Locking In our example, if one transaction (T1) holds an exclusive lock at the table level, and another transaction (T2) holds an exclusive lock at the row level, each of the transactions believe they have exclusive access to the resource. In this sc
en
ario, since T1 believes it locks the
en
tire table, it might inadvert
en
tly make changes to the same row that T2 thought it has locked exclusively. In a multigranular locking
en
vironm
en
t, there must be a way to effectively overcome this sc
en
ario. Int
en
t lock is the answer to this problem. Int
en
t Lock Int
en
t Lock is the term used to mean placing a marker in a higher-level lock queue. The type of int
en
t lock can also be called the multigranular lock mode. An int
en
t lock indicates that
SQL
Server
wants to acquire a sha
red
(S) lock or exclusive (X) lock on some of the resources lower down in the hierarchy. For example, a sha
red
int
en
t lock placed at the table level means that a transaction int
en
ds on placing sha
red
(S) locks on pages or rows within that table. Setting an int
en
t lock at the table level prev
en
ts another transaction from subsequ
en
tly acquiring an exclusive (X) lock on the table containing that page. Int
en
t locks improve performance because
SQL
Server
examines int
en
t locks only at the table level to determine whether a transaction can safely acquire a lock on that table. This removes the requirem
en
t to examine every row or page lock on the table to determine whether a transaction can lock the
en
tire table. Lock Mode The code shown in the slide repres
en
ts how the lock mode is sto
red
internally. You can see these codes by querying the master.dbo.spt_values table: SELECT * FROM master.dbo.spt_values WHERE type = N'L' However, the req_mode column of master.dbo.syslockinfo has lock mode code that is one less than the code values shown here. For example, value of req_mode = 3 repres
en
ts the Sha
red
lock mode rather than the Schema Modification lock mode. Lock Compatibility These locks can apply at any coarser level of granularity. If a row is locked,
SQL
Server
will apply int
en
t locks at both the page and the table level. If a page is locked,
SQL
Server
will apply an int
en
t lock at the table level. SIX locks imply that we have sha
red
access to a resource and we have also placed X locks at a lower level in the hierarchy.
SQL
Server
never asks for SIX locks directly, they are always the result of a conversion. For example, suppose a transaction scanned a page using an S lock and th
en
subsequ
en
tly decided to perform a row level update. The row would obtain an X lock, but now the page would require an IX lock. The resultant mode on the page would be SIX. Another type of table lock is a schema stability lock (Sch-S) and is compatible with all table locks except the schema modification lock (Sch-M). The schema modification lock (Sch-M) is incompatible with all table locks. Locking Resources Delivery Tip Note the differ
en
ces betwe
en
Key and Key Range locks. Key Range locks will be cove
red
in a couple of slides.
SQL
Server
can lock these resources: Item Description DB A database. File A database file Index An
en
tire index of a table. Table An
en
tire table, including all data and indexes. Ext
en
t A contiguous group of data pages or index pages. Page An 8-KB data page or index page. Key Row lock within an index. Key-range A key-range. Used to lock ranges betwe
en
records in a table to prev
en
t phantom insertions or deletions into a set of records.
En
sures serializable transactions. RID A Row Id
en
tifier. Used to individually lock a single row within a table. Application A lock resource defined by an application. The lock manager knows nothing about the resource format. It simply compares the 'strings' repres
en
ting the lock resources to determine whether it has found a match. If a match is found, it knows that resource is already locked. Some of the resources have “sub-resources.” The followings are sub-resources displayed by the sp_lock output: Database Lock Sub-Resources: Full Database Lock (default) [BULK-OP-DB] – Bulk Operation Lock for Database [BULK-OP-LOG] – Bulk Operation Lock for Log Table Lock Sub-Resources: Full Table Lock (default) [UPD-STATS] – Update statistics Lock [COMPILE] – Compile Lock Index Lock sub-Resources: Full Index Lock (default) [INDEX_ID] – Index ID Lock [INDEX_NAME] – Index Name Lock [BULK_ALLOC] – Bulk Allocation Lock [DEFRAG] – Defragm
en
tation Lock For more information, see also… SOX000821700049
SQL
7.0 How to interpret lock resource Ids Lock Resource Block The resource type has the following resource block format: Resource Type (Code) Cont
en
t DB (2) Data 1: sub-resource; Data 2: 0; Data 3: 0 File (3) Data 1: File ID; Data 2: 0; Data 3: 0 Index (4) Data 1: Object ID; Data 2: sub-resource; Data 3: Index ID Table (5) Data 1: Object ID; Data 2: sub-resource; Data 3: 0. Page (6) Data 1: Page Number; Data 3: 0. Key (7) Data 1: Object ID; Data 2: Index ID; Data 3: Hashed Key Ext
en
t (8) Data 1: Ext
en
t ID; Data 3: 0. RID (9) Data 1: RID; Data 3: 0. Application (10) Data 1: Application resource name The rsc_bin column of master..syslockinfo contains the resource block in hexadecimal format. For an example of how to decode value from this column using the information above, let us assume we have the following value: 0x000705001F83D775010002014F0BEC4E With byte swapping within each field, this can be decoded as: Byte 0: Flag – 0x00 Byte 1: Resource Type – 0x07 (Key) Byte 2-3: DBID – 0x0005 Byte 4-7: ObjectID – 0x 75D7831F (1977058079) Byte 8-9: IndexID – 0x0001 Byte 10-16: Hash Key value – 0x 02014F0BEC4E For more information about how to decode this value, see also… Inside
SQL
Server
2000, pages 803 and 806. Key Range Locking Key Range Locking To support SERIALIZABLE transaction semantics,
SQL
Server
needs to lock sets of rows specified by a p
red
icate, such as WHERE salary BETWE
EN
30000 AND 50000
SQL
Server
needs to lock data that does not exist! If no rows satisfy the WHERE condition the first time the range is scanned, no rows should be returned on any subsequ
en
t scans. Key range locks are similar to row locks on index keys (whether cluste
red
or not). The locks are placed on individual keys rather than at the node level. The hash value consists of all the key compon
en
ts and the locator. So, for a noncluste
red
index over a heap, where columns c1 and c2 where indexed, the hash would contain contributions from c1, c2 and the RID. A key range lock applied to a particular key means that all keys betwe
en
the value locked and the next value would be locked for all data modification. Key range locks can lock a slightly larger range than that implied by the WHERE clause. Suppose the following select was executed in a transaction with isolation level SERIALIZABLE: SELECT * FROM members WHERE first_name betwe
en
‘Al’ and ‘Carl’ If 'Al', 'Bob', and 'Dave' are index keys in the table, the first two of these would acquire key range locks. Although this would prev
en
t anyone from inserting either 'Alex' or 'B
en
', it would also prev
en
t someone from inserting 'Dan', which is not within the range of the WHERE clause. Prior to
SQL
Server
7.0, page locking was used to prev
en
t phantoms by locking the
en
tire set of pages on which the phantom would exist. This can be too conservative. Key Range locking lets
SQL
Server
lock only a much more restrictive area of the table. Impact Key-range locking
en
sures that these sc
en
arios are SERIALIZABLE: Range scan query Singleton fetch of nonexist
en
t row Delete operation Insert operation However, the following conditions must be satisfied before key-range locking can occur: The transaction-isolation level must be set to SERIALIZABLE. The operation performed on the data must use an index range access. Range locking is activated only wh
en
query processing (such as the optimizer) chooses an index path to access the data. Key Range Lock Mode Again, the req_mode column of master.dbo.syslockinfo has lock mode code that is one less than the code values shown here. Dynamic Locking Wh
en
modifying individual rows,
SQL
Server
typically would take row locks to maximize concurr
en
cy (for example, OLTP, order-
en
try application). Wh
en
scanning larger volumes of data, it would be more appropriate to take page or table locks to minimize the cost of acquiring locks (for example, DSS, data warehouse, reporting). Locking Decision The decision about which unit to lock is made dynamically, taking many factors into ac
count
, including other activity on the system. For example, if there are multiple transactions curr
en
tly accessing a table,
SQL
Server
will t
en
d to favor row locking more so than it otherwise would. It may mean the differ
en
ce betwe
en
scanning the table now and paying a bit more in locking cost, or having to wait to acquire a more coarse lock. A preliminary locking decision is made during query optimization, but that decision can be adjusted wh
en
the query is actually executed. Lock Escalation Wh
en
the lock
count
for the transaction exceeds and is a multiple of ESCALATION_THRESHOLD (1250), the Lock Manager attempts to escalate. For example, wh
en
a transaction acqui
red
1250 locks, lock manager will try to escalate. The number of locks held may continue to increase after the escalation attempt (for example, because new tables are accessed, or the previous lock escalation attempts failed due to incompatible locks held by another spid). If the lock
count
for this transaction reaches 2500 (1250 * 2), Lock Manager will attempt escalation again. The Lock Manager looks at the lock memory it is using and if it is more than 40 perc
en
t of
SQL
Server
’s allocated buffer pool memory, it tries to find a scan (SDES) where no escalation has already be
en
performed. It th
en
repeats the search operation until all scans have be
en
escalated or until the memory used drops under the MEMORY_LOAD_ESCALATION_THRESHOLD (40%) value. If lock escalation is not possible or fails to significantly
red
uce lock memory footprint,
SQL
Server
can continue to acquire locks until the total lock memory reaches 60 perc
en
t of the buffer pool (MAX_LOCK_RESOURCE_MEMORY_PERC
EN
TAGE=60). Lock escalation may be also done wh
en
a single scan (SDES) holds more than LOCK_ESCALATION_THRESHOLD (765) locks. There is no lock escalation on temporary tables or system tables. Trace Flag 1211 disables lock escalation. Important Do not relay this to the customer without careful consideration. Lock escalation is a necessary feature, not something to be avoided completely. Trace flags are global and disabling lock escalation could lead to out of memory situations, extremely poor performing queries, or other problems. Lock escalation tracing can be se
en
using the Profiler or with the g
en
eral locking trace flag, -T1200. However, Trace Flag 1200 shows all lock activity so it should not be usable on a production system. For more information, see also… SOX000925700237 “TITLE:
SQL
7.0 Lock escalation in
SQL
7.0” Lock Timeout Application Lock Timeout An application can set lock timeout for a session with the SET option: SET LOCK_TIMEOUT N where N is a number of milliseconds. A value of -1 means that there will be no timeout, which is equival
en
t to the version 6.5 behavior. A value of 0 means that there will be no waiting; if a process finds a resource locked, it will g
en
erate
error
message 1222 and continue with the next statem
en
t. The curr
en
t value of LOCK_TIMEOUT is sto
red
in the global variable @@lock_timeout. Note After a lock timeout any transaction containing the statem
en
t, is rolled back or canceled by
SQL
Server
2000 (bug#352640 was filed). This behavior is differ
en
t from that of
SQL
Server
7.0. With
SQL
Server
7.0, the application must have an
error
handler that can trap
error
1222 and if an application does not trap the
error
, it can proceed unaware that an individual statem
en
t within a transaction has be
en
canceled, and
error
s can occur because statem
en
ts later in the transaction may dep
en
d on the statem
en
t that was never executed. Bug#352640 is fixed in hotfix build 8.00.266 whereby a lock timeout will only Internal Lock Timeout At time, internal operations within
SQL
Server
will attempt to acquire locks via lock manager. Typically, these lock requests are issued with “no waiting.” For example, the ghost record processing might try to clean up rows on a particular page, and before it can do that, it needs to lock the page. Thus, the ghost record manager will request a page lock with no wait so that if it cannot lock the page, it will just move on to other pages; it can always come back to this page later. If you look at
SQL
Profiler Lock: Timeout ev
en
ts, internal lock timeout typically have a duration value of
zero
. Lock Duration Lock Mode and Transaction Isolation Level For REPEATABLE READ transaction isolation level, update locks are held until data is read and processed, unless promoted to exclusive locks. "Data is processed" means that we have decided whether the row in question matched the search criteria; if not th
en
the update lock is released, otherwise, we get an exclusive lock and make the modification. Consider the following query: use northwind go dbcc traceon(3604, 1200, 1211) -- turn on lock tracing -- and disable escalation go set transaction isolation level repeatable read begin tran update dbo.[order details] set dis
count
= convert (real, dis
count
) where dis
count
= 0.0 exec sp_lock Update locks are promoted to exclusive locks wh
en
there is a match; otherwise, the update lock is released. The sp_lock output verifies that the SPID does not hold any update locks or sha
red
locks at the
en
d of the query. Lock escalation is turned off so that exclusive table lock is not held at the
en
d. Warning Do not use trace flag 1200 in a production
en
vironm
en
t because it produces a lot of output and slows down the
server
. Trace flag 1211 should not be used unless you have done ext
en
sive study to make sure it helps with performance. These trace flags are used here for illustration and learning purposes only. Lock Ownership Most of the locking discussion in this lesson relates to locks owned by “transactions.” In addition to transaction, cursor and session can be owners of locks and they both affect how long locks are held. For every row that is fetched, wh
en
SCROLL_LOCKS option is used, regardless of the state of a transaction, a cursor lock is held until the next row is fetched or wh
en
the cursor is closed. Locks owned by session are outside the scope of a transaction. The duration of these locks are bounded by the connection and the process will continue to hold these locks until the process disconnects. A typical lock owned by session is the database (DB) lock. Locking – Read Committed Scan Under read committed isolation level, wh
en
database pages are scanned, sha
red
locks are held wh
en
the page is read and processed. The sha
red
locks are released “behind” the scan and allow other transactions to update rows. It is important to note that the sha
red
lock curr
en
tly acqui
red
will not be released until sha
red
lock for the next page is successfully acqui
red
(this is commonly know as “crabbing”). If the same pages are scanned again, rows may be modified or deleted by other transactions. Locking – Repeatable Read Scan Under repeatable read isolation level, wh
en
database pages are scanned, sha
red
locks are held wh
en
the page is read and processed.
SQL
Server
continues to hold these sha
red
locks, thus prev
en
ting other transactions to update rows. If the same pages are scanned again, previously scanned rows will not change but new rows may be added by other transactions. Locking – Serializable Read Scan Under serializable read isolation level, wh
en
database pages are scanned, sha
red
locks are held not only on rows but also on scanned key range.
SQL
Server
continues to hold these sha
red
locks until the
en
d of transaction. Because key range locks are held, not only will this prev
en
t other transactions from modifying the rows, no new rows can be inserted. Prefetch and Isolation Level Prefetch and Locking Behavior The prefetch feature is available for use with
SQL
Server
7.0 and
SQL
Server
2000. Wh
en
searching for data using a noncluste
red
index, the index is searched for a particular value. Wh
en
that value is found, the index points to the disk address. The traditional approach would be to immediately issue an I/O for that row, giv
en
the disk address. The result is one synchronous I/O per row and, at most, one disk at a time working to evaluate the query. This does not take advantage of striped disk sets. The prefetch feature takes a differ
en
t approach. It continues looking for more record pointers in the noncluste
red
index. Wh
en
it has collected a number of them, it provides the storage
en
gine with prefetch hints. These hints tell the storage
en
gine that the query processor will need these particular records soon. The storage
en
gine can now issue several I/Os simultaneously, taking advantage of striped disk sets to execute multiple operations simultaneously. For example, if the
en
gine is scanning a noncluste
red
index to determine which rows qualify but will ev
en
tually need to visit the data page as well to access columns that are not in the index, it may decide to submit asynchronous page read requests for a group of qualifying rows. The prefetch data pages are th
en
revisited later to avoid waiting for each individual page read to complete in a serial fashion. This data access path requires that a lock be held betwe
en
the prefetch request and the row lookup to stabilize the row on the page so it is not to be moved by a page split or cluste
red
key update. For our example, the isolation level of the query is escalated to REPEATABLE READ, overriding the transaction isolation level. With
SQL
Server
7.0 and
SQL
Server
2000, portions of a transaction can execute at a differ
en
t transaction isolation level than the
en
tire transaction itself. This is implem
en
ted as lock classes. Lock classes are used to control lock lifetime wh
en
portions of a transaction need to execute at a stricter isolation level than the underlying transaction. Unfortunately, in
SQL
Server
7.0 and
SQL
Server
2000, the lock class is created at the topmost operator of the query and h
en
ce released only at the
en
d of the query. Curr
en
tly there is no support to release the lock (lock class) after the row has be
en
discarded or fetched by the filter or join operator. This is because isolation level can be set at the query level via a lock class, but no lower. Because of this, locks acqui
red
during the query will not be released until the query completes. If prefetch is occurring you may see a single SPID that holds hund
red
s of Sha
red
KEY or PAG locks ev
en
though the connection’s isolation level is READ COMMITTED. Isolation level can be determined from DBCC PSS output. For details about this behavior see “SOX001109700040 INF: Queries with PREFETCH in the plan hold lock until the
en
d of transaction”. Other Locking Mechanism Lock manager does not manage latches and spinlocks. Latches Latches are internal mechanisms used to protect pages while doing operations such as placing a row physically on a page, compressing space on a page, or retrieving rows from a page. Latches can roughly be
divide
d into I/O latches and non-I/O latches. If you see a high number of non-I/O related latches,
SQL
Server
is usually doing a large number of hash or sort operations in tempdb. You can monitor latch activities via DBCC
SQL
PERF(‘WAITSTATS’) command. Spinlock A spinlock is an internal data structure that is used to protect vital information that is sha
red
within
SQL
Server
. On a multi-processor machine, wh
en
SQL
Server
tries to access a particular resource protected by a spinlock, it must first acquire the spinlock. If it fails, it executes a loop that will check to see if the lock is available and if not, decrem
en
ts a
count
er. If the
count
er reaches
zero
, it yields the processor to another thread and goes into a “sleep” (wait) state for a pre-determined amount of time. Wh
en
it wakes, hopefully, the lock is free and available. If not, the loop starts again and it is terminated only wh
en
the lock is acqui
red
. The reason for implem
en
ting a spinlock is that it is probably less costly to “spin” for a short time rather than yielding the processor. Yielding the processor will force an exp
en
sive context switch where: The old thread’s state must be saved The new thread’s state must be reloaded The data sto
red
in the L1 and L2 cache are useless to the processor On a single-processor computer, the loop is not useful because no other thread can be running and thus, no one can release the spinlock for the curr
en
tly executing thread to acquire. In this situation, the thread yields the processor immediately. Lesson 2: Concepts – Batch and Transaction This lesson outlines some of the common causes that contribute to the perception of a slow
server
. What You Will Learn After completing this lesson, you will be able to: Review batch processing and
error
checking. Review explicit, implicit and autocommit transactions and transaction nesting level. Discuss how commit and rollback transaction done in sto
red
procedure and trigger affects transaction nesting level. Discuss various transaction isolation level and their impact on locking. Discuss the differ
en
ce betwe
en
aborting a statem
en
t, a transaction, and a batch. Describe how @@
error
, @@trans
count
, and @@row
count
can be used for
error
checking and handling. Recomm
en
ded Reading Charter 12 “Transactions and Triggers”, Inside
SQL
Server
2000 by Kal
en
Delaney Batch Definition
SQL
Profiler Statem
en
ts and Batches To help further your understanding of what is a batch and what is a statem
en
t, you can use
SQL
Profiler to study the definition of batch and statem
en
t. Try This: Using
SQL
Profiler to Analyze Batch 1. Log on to a
server
with Query Analyzer 2. Startup the
SQL
Profiler against the same
server
3. Start a trace using the “Standard
SQL
Profiler” template 4. Execute the following using Query Analyzer: SELECT @@VERSION SELECT @@SPID The ‘
SQL
:BatchCompleted’ ev
en
t is captu
red
by the trace. It shows both the statem
en
ts as a single batch. 5. Now execute the following using Query Analyzer {call sp_who()} What shows up? The ‘RPC:Completed’ with the sp_who information. RPC is simply another
en
try point to the
SQL
Server
to call sto
red
procedures with native data types. This allows one to avoid parsing. The ‘RPC:Completed’ ev
en
t should be conside
red
the same as a batch for the purposes of this discussion. Stop the curr
en
t trace and start a new trace using the “
SQL
ProfilerT
SQL
_SPs” template. Issue the same command as outlines in step 5 above. Looking at the output, not only can you see the batch markers but each statem
en
t as executed within the batch. Autocommit, Explicit, and Implicit Transaction Autocommit Transaction Mode (Default) Autocommit mode is the default transaction managem
en
t mode of
SQL
Server
. Every Transact-
SQL
statem
en
t, whether it is a standalone statem
en
t or part of a batch, is committed or rolled back wh
en
it completes. If a statem
en
t completes successfully, it is committed; if it
en
count
ers any
error
, it is rolled back. A
SQL
Server
connection operates in autocommit mode wh
en
ever this default mode has not be
en
overridd
en
by either explicit or implicit transactions. Autocommit mode is also the default mode for ADO, OLE DB, ODBC, and DB-Library. A
SQL
Server
connection operates in autocommit mode until a BEGIN TRANSACTION statem
en
t starts an explicit transaction, or implicit transaction mode is set on. Wh
en
the explicit transaction is committed or rolled back, or wh
en
implicit transaction mode is turned off,
SQL
Server
returns to autocommit mode. Explicit Transaction Mode An explicit transaction is a transaction that starts with a BEGIN TRANSACTION statem
en
t. An explicit transaction can contain one or more statem
en
ts and must be terminated by either a COMMIT TRANSACTION or a ROLLBACK TRANSACTION statem
en
t. Implicit Transaction Mode
SQL
Server
can automatically or, more precisely, implicitly start a transaction for you if a SET IMPLICIT_TRANSACTIONS ON statem
en
t is run or if the implicit transaction option is turned on globally by running sp_configure ‘user options’ 2. (Actually, the bit mask 0x2 must be turned on for the user option so you might have to perform an ‘OR’ operation with the existing user option value.) See
SQL
Server
2000 Books Online on how to turn on implicit transaction under ODBC and OLE DB (acdata.chm::/ac_8_md_06_2g6r.htm). Transaction Nesting Explicit transactions can be nested. Committing inner transactions is igno
red
by
SQL
Server
other than to decrem
en
ts @@TRAN
COUNT
. The transaction is either committed or rolled back based on the action tak
en
at the
en
d of the outermost transaction. If the outer transaction is committed, the inner nested transactions are also committed. If the outer transaction is rolled back, th
en
all inner transactions are also rolled back, regardless of whether the inner transactions were individually committed. Each call to COMMIT TRANSACTION applies to the last executed BEGIN TRANSACTION. If the BEGIN TRANSACTION statem
en
ts are nested, th
en
a COMMIT statem
en
t applies only to the last nested transaction, which is the innermost transaction. Ev
en
if a COMMIT TRANSACTION transaction_name statem
en
t within a nested transaction refers to the transaction name of the outer transaction, the commit applies only to the innermost transaction. If a ROLLBACK TRANSACTION statem
en
t without a transaction_name parameter is executed at any level of a set of nested transaction, it rolls back all the nested transactions, including the outermost transaction. The @@TRAN
COUNT
function records the curr
en
t transaction nesting level. Each BEGIN TRANSACTION statem
en
t increm
en
ts @@TRAN
COUNT
by one. Each COMMIT TRANSACTION statem
en
t decrem
en
ts @@TRAN
COUNT
by one. A ROLLBACK TRANSACTION statem
en
t that does not have a transaction name rolls back all nested transactions and decrem
en
ts @@TRAN
COUNT
to 0. A ROLLBACK TRANSACTION that uses the transaction name of the outermost transaction in a set of nested transactions rolls back all the nested transactions and decrem
en
ts @@TRAN
COUNT
to 0. Wh
en
you are unsure if you are already in a transaction, SELECT @@TRAN
COUNT
to determine whether it is 1 or more. If @@TRAN
COUNT
is 0 you are not in a transaction. You can also find the transaction nesting level by checking the sysprocess.op
en
_tran column. See
SQL
Server
2000 Books Online topic “Nesting Transactions” (acdata.chm::/ac_8_md_06_66nq.htm) for more information. Statem
en
t, Transaction, and Batch Abort One batch can have many statem
en
ts and one transaction can have multiple statem
en
ts, also. One transaction can span multiple batches and one batch can have multiple transactions. Statem
en
t Abort Curr
en
tly executing statem
en
t is aborted. This can be a bit confusing wh
en
you start talking about statem
en
ts in a trigger or sto
red
procedure. Let us look closely at the following trigger: CREATE TRIGGER TRG8134 ON TBL8134 AFTER INSERT AS BEGIN SELECT 1/0 SELECT 'Next command in trigger'
EN
D To fire the INSERT trigger, the batch could be as simple as ‘INSERT INTO TBL8134 VALUES(1)’. However, the trigger contains two statem
en
ts that must be executed as part of the batch to satisfy the cli
en
ts insert request. Wh
en
the ‘SELECT 1/0’ causes the
divide
by
zero
error
, a statem
en
t abort is issued for the ‘SELECT 1/0’ statem
en
t. Batch and Transaction Abort On
SQL
Server
2000 (and
SQL
Server
7.0) wh
en
ever a non-informational
error
is
en
count
e
red
in a trigger, the statem
en
t abort is promoted to a batch and transactional abort. Thus, in the example the statem
en
t abort for ‘select 1/0’ promotion results in an
en
tire batch abort. No further statem
en
ts in the trigger or batch will be executed and a rollback is issued. On
SQL
Server
6.5, the statem
en
t aborts immediately and results in a transaction abort. However, the rest of the statem
en
ts within the trigger are executed. This trigger could return ‘Next command in trigger’ as a result set. Once the trigger completes the batch abort promotion takes effect. Conversely, submitting a similar set of statem
en
ts in a standalone batch can result in differ
en
t behavior. SELECT 1/0 SELECT 'Next command in batch' Not considering the set option possibilities, a
divide
by
zero
error
g
en
erally results in a statem
en
t abort. Since it is not in a trigger, the promotion to a batch abort is avoided and subsequ
en
t SELECT statem
en
t can execute. The programmer should add an “if @@
ERROR
” check immediately after the ‘select 1/0’ to T-
SQL
execution to control the flow correctly. Aborting and Set Options ARITHABORT If SET ARITHABORT is ON, these
error
conditions cause the query or batch to terminate. If the
error
s occur in a transaction, the transaction is rolled back. If SET ARITHABORT is OFF and one of these
error
s occurs, a warning message is displayed, and NULL is assigned to the result of the arithmetic operation. Wh
en
an INSERT, DELETE, or UPDATE statem
en
t
en
count
ers an arithmetic
error
(overflow,
divide
-by-
zero
, or a domain
error
) during expression evaluation wh
en
SET ARITHABORT is OFF,
SQL
Server
inserts or updates a NULL value. If the target column is not nullable, the insert or update action fails and the user receives an
error
. XACT_ABORT Wh
en
SET XACT_ABORT is ON, if a Transact-
SQL
statem
en
t raises a run-time
error
, the
en
tire transaction is terminated and rolled back. Wh
en
OFF, only the Transact-
SQL
statem
en
t that raised the
error
is rolled back and the transaction continues processing. Compile
error
s, such as syntax
error
s, are not affected by SET XACT_ABORT. For example: CREATE TABLE t1 (a int PRIMARY KEY) CREATE TABLE t2 (a int REFER
EN
CES t1(a)) GO INSERT INTO t1 VALUES (1) INSERT INTO t1 VALUES (3) INSERT INTO t1 VALUES (4) INSERT INTO t1 VALUES (6) GO SET XACT_ABORT OFF GO BEGIN TRAN INSERT INTO t2 VALUES (1) INSERT INTO t2 VALUES (2) /* Foreign key
error
*/ INSERT INTO t2 VALUES (3) COMMIT TRAN SELECT 'Continue running batch 1...' GO SET XACT_ABORT ON GO BEGIN TRAN INSERT INTO t2 VALUES (4) INSERT INTO t2 VALUES (5) /* Foreign key
error
*/ INSERT INTO t2 VALUES (6) COMMIT TRAN SELECT 'Continue running batch 2...' GO /* Select shows only keys 1 and 3 added. Key 2 insert failed and was rolled back, but XACT_ABORT was OFF and rest of transaction succeeded. Key 5 insert
error
with XACT_ABORT ON caused all of the second transaction to roll back. Also note that 'Continue running batch 2...' is not Returned to indicate that the batch is aborted. */ SELECT * FROM t2 GO DROP TABLE t2 DROP TABLE t1 GO Compile and Run-time
Error
s Compile
Error
s Compile
error
s are
en
count
e
red
during syntax checks, security checks, and other g
en
eral operations to prepare the batch for execution. These
error
s can prev
en
t the optimization of the query and thus lead to immediate abort. The statem
en
t is not run and the batch is aborted. The transaction state is g
en
erally left untouched. For example, assume there are four statem
en
ts in a particular batch. If the third statem
en
t has a syntax
error
, none of the statem
en
ts in the batch is executed. Optimization
Error
s Optimization
error
s would include rare situations where the statem
en
t
en
count
ers a problem wh
en
attempting to build an optimal execution plan. Example: “too many tables refer
en
ced in the query”
error
is reported because a “work table” was added to the plan. Runtime
Error
s Runtime
error
s are those that are
en
count
e
red
during the execution of the query. Consider the following batch: SELECT * FROM pubs.dbo.titles UPDATE pubs.dbo.authors SET au_lname = au_lname SELECT * FROM foo UPDATE pubs.dbo.authors SET au_lname = au_lname If you run the above statem
en
ts in a batch, the first two statem
en
ts will be executed, the third statem
en
t will fail because table foo does not exist, and the batch will terminate. Defer
red
Name Resolution is the feature that allows this batch to start executing before resolving the object foo. This feature allows
SQL
Server
to delay object resolution and place a “placeholder” in the query’s execution. The object refer
en
ced by the placeholder is resolved until the query is executed. In our example, the execution of the statem
en
t “SELECT * FROM foo” will trigger another compile process to resolve the name again. This time,
error
message 208 is returned.
Error
: 208, Level 16, State 1, Line 1 Invalid object name 'foo'. Message 208 can be
en
count
e
red
as a runtime or compile
error
dep
en
ding on whether the Defer
red
Name Resolution feature is available. In
SQL
Server
6.5 this would be conside
red
a compile
error
and on
SQL
Server
2000 (and
SQL
Server
7.0) as a runtime
error
due to Defer
red
Name Resolution. In the following example, if a trigger refer
en
ced authors2, the
error
is detected as
SQL
Server
attempts to execute the trigger. However, under
SQL
Server
6.5 the create trigger statem
en
t fails because authors2 does not exist at compile time. Wh
en
error
s are
en
count
e
red
in a trigger, g
en
erally, the statem
en
t, batch, and transaction are aborted. You should be able to observe this by running the following script in pubs database: Create table tblTest(iID int) go create trigger trgInsert on tblTest for INSERT as begin select * from authors select * from authors2 select * from titles
en
d go begin tran select 'Before' insert into tblTest values(1) select 'After' go select @@TRAN
COUNT
go Wh
en
run in a batch, the statem
en
t and the batch are aborted but the transaction remains active. The follow script illustrates this: begin tran select 'Before' select * from authors2 select 'After' go select @@TRAN
COUNT
go One other factor in a compile versus runtime
error
is implicit data type conversions. If you were to run the following statem
en
ts on
SQL
Server
6.5 and
SQL
Server
2000 (and
SQL
Server
7.0): create table tblData(dtData datetime) go select 1 insert into tblData values(12/13/99) go On
SQL
Server
6.5, you get an
error
before execution of the batch begins so no statem
en
ts are executed and the batch is aborted.
Error
: 206, Level 16, State 2, Line 2 Operand type clash: int is incompatible with datetime On
SQL
Server
2000, you get the default value (1900-01-01 00:00:00.000) inserted into the table.
SQL
Server
2000 implicit data type conversion treats this as integer division. The integer division of 12/13/99 is 0, so the default date and time value is inserted, no
error
returned. To correct the problem on either version is to wrap the date string with quotes. See Bug #56118 (
sql
bug_70) for more details about this situation. Another example of a runtime
error
is a 605 message.
Error
: 605 Attempt to fetch logical page %S_PGID in database '%.*ls' belongs to object '%.*ls', not to object '%.*ls'. A 605
error
is always a runtime
error
. However, dep
en
ding on the transaction isolation level, (e.g. using the NOLOCK lock hint), established by the SPID the handling of the
error
can vary. Specifically, a 605
error
is conside
red
an ACCESS
error
.
Error
s associated with buffer and page access are found in the 600 series of
error
s. Wh
en
the
error
is
en
count
e
red
, the isolation level of the SPID is examined to determine proper handling based on information or fatal
error
level. Transaction
Error
Checking Not all
error
s cause transactions to automatically rollback. Although it is difficult to determine exactly which
error
s will rollback transactions and which
error
s will not, the main idea here is that programmers must perform
error
checking and handle
error
s appropriately.
Error
Handling Rais
error
Details Rais
error
seems to be a source of confusion but is really rather simple. Rais
error
with severity levels of 20 or higher will terminate the connection. Of course, wh
en
the connection is terminated a full rollback of any op
en
transaction will immediately be instantiated by the
SQL
Server
(except distributed transaction with DTC involved). Severity levels lower than 20 will simply result in the
error
message being returned to the cli
en
t. They do not affect the transaction scope of the connection. Consider the following batch: use pubs begin tran update authors set au_lname = 'smith' rais
error
('This is bad', 19, 1) with log select @@tran
count
With severity set at 19, the 'select @@tran
count
' will be executed after the rais
error
statem
en
t and will return a value of 1. If severity is changed to 20, th
en
the select statem
en
t will not run and the connection is brok
en
. Important
Error
handling must occur not only in T-
SQL
batches and sto
red
procedures, but also in application program code. Transactions and Triggers (1 of 2) Basic behavior assumes the implicit transactions setting is set to OFF. This behavior makes it possible to id
en
tify business logic
error
s in a trigger, raise an
error
, rollback the action, and add an audit table
en
try. Logically, the insert to the audit table cannot take place before the ROLLBACK action and you would not want to build in the audit table insert into every applications
error
handler that violated the business rule of the trigger. For more information, see also…
SQL
Server
2000 Books Online topic “Rollbacks in sto
red
procedure and triggers“ (acdata.chm::/ac_8_md_06_4qcz.htm) IMPLICIT_TRANSACTIONS ON Behavior The behavior of firing other triggers on the same table can be tricky. Say you added a trigger that checks the CODE field. Read only versions of the rows contain the code ‘RO’ and read/write versions use ‘RW.’ Wh
en
ever someone tries to delete a row with a code ‘RO’ the trigger issues the rollback and logs an audit table
en
try. However, you also have a second trigger that is responsible for cascading delete operations. One cli
en
t could issue the delete without implicit transactions on and only the curr
en
t trigger would execute and th
en
terminate the batch. However, a second cli
en
t with implicit transactions on could issue the same delete and the secondary trigger would fire. You
en
d up with a situation in which the cascading delete operations can take place (are committed) but the initial row remains in the table because of the rollback operation. None of the delete operations should be allowed but because the transaction scope was restarted because of the implicit transactions setting, they did. Transactions and Triggers (2 of 2) It is extremely difficult to determine the execution state of a trigger wh
en
using explicit rollback statem
en
ts in combination with implicit transactions. The RETURN statem
en
t is not allowed to return a value. The only way I have found to set the @@
ERROR
is using a ‘rais
error
’ as the last execution statem
en
t in the last trigger to execute. If you modify the example, this following RAIS
ERROR
statem
en
t will set @@
ERROR
to 50000: CREATE TRIGGER trgTest on tblTest for INSERT AS BEGIN ROLLBACK INSERT INTO tblAudit VALUES (1) RAIS
ERROR
('This is bad', 14,1)
EN
D However, this value does not carry over to a secondary trigger for the same table. If you raise an
error
at the
en
d of the first trigger and th
en
look at @@
ERROR
in the secondary trigger the @@
ERROR
remains 0. Carrying Forward an Active/Op
en
Transaction It is possible to exit from a trigger and carry forward an op
en
transaction by issuing a BEGIN TRAN or by setting implicit transaction on and doing INSERT, UPDATE, or DELETE. Warning It is never recomm
en
ded that a trigger call BEGIN TRANSACTION. By doing this you increm
en
t the transaction
count
. Invalid code logic, not calling commit transaction, can lead to a situation where the transaction
count
remains elevated upon exit of the trigger. Transaction
Count
The behavior is better explained by understanding how the
server
works. It does not matter whether you are in a transaction, wh
en
a modification takes place the transaction
count
is increm
en
ted. So, in the simplest form, during the processing of an insert the transaction
count
is 1. On completion of the insert, the
server
will commit (and thus decrem
en
t the transaction
count
). If the commit id
en
tifies the transaction
count
has returned to 0, the actual commit processing is completed. Issuing a commit wh
en
the transaction
count
is greater than 1 simply decrem
en
ts the nested transaction
count
er. Thus, wh
en
we
en
ter a trigger, the transaction
count
is 1. At the completion of the trigger, the transaction
count
will be 0 due to the commit issued at the
en
d of the modification statem
en
t (insert). In our example, if the connection was already in a transaction and called the second INSERT, since implicit transaction is ON, the transaction
count
in the trigger will be 2 as long as the ROLLBACK is not executed. At the
en
d of the insert, the commit is again issued to decrem
en
t the transaction refer
en
ce
count
to 1. However, the value does not return to 0 so the transaction remains op
en
/active. Subsequ
en
t triggers are only fi
red
if the transaction
count
at the
en
d of the trigger remains greater than or equal to 1. The key to continuation of secondary triggers and the batch is the transaction
count
at the
en
d of a trigger execution. If the trigger that performs a rollback has done an explicit begin transaction or uses implicit transactions, subsequ
en
t triggers and the batch will continue. If the transaction
count
is not 1 or greater, subsequ
en
t triggers and the batch will not execute. Warning Forcing the transaction
count
after issuing a rollback is dangerous because you can easily loose track of your transaction nesting level. Wh
en
performing an explicit rollback in a trigger, you should immediately issue a return statem
en
t to maintain consist
en
t behavior betwe
en
a connection with and without implicit transaction settings. This will force the trigger(s) and batch to terminate immediately. One of the methods of dealing with this issue is to run ‘SET IMPLICIT_TRANSACTIONS OFF’ as the first statem
en
t of any trigger. Other methods may
en
tails checking @@TRAN
COUNT
at the
en
d of the trigger and continue to COMMIT the transaction as long as @@TRAN
COUNT
is greater than 1. Examples The following examples are based on this table: create table tbl50000Insert (iID int NOT NULL) go Note If more than one trigger is used, to guarantee the trigger firing sequ
en
ce, the sp_settriggerorder command should be used. This command is omitted in these examples to simplify the complexity of the statem
en
ts. First Example In the first example, the second trigger was never fi
red
and the batch, starting with the insert statem
en
t, was aborted. Thus, the print statem
en
t was never issued. print('Trigger issues rollback - cancels batch') go create trigger trg50000Insert on tbl50000Insert for INSERT as begin select 'Inserted', * from inserted rollback tran select '
En
d of trigger', @@TRAN
COUNT
as 'TRAN
COUNT
'
en
d go create trigger trg50000Insert2 on tbl50000Insert for INSERT as begin select 'In Trigger2' select 'Trigger 2 Inserted', * from inserted
en
d go insert into tbl50000Insert values(1) print('---------------------- In same batch') select * from tbl50000Insert go -- Cleanup drop trigger trg50000Insert drop trigger trg50000Insert2 go delete from tbl50000Insert Second Example The next example shows that since a new transaction is started, the second trigger will be fi
red
and the print statem
en
t in the batch will be executed. Note that the insert is rolled back. print('Trigger issues rollback - increases tran
count
to continue batch') go create trigger trg50000Insert on tbl50000Insert for INSERT as begin select 'Inserted', * from inserted rollback tran begin tran
en
d go create trigger trg50000Insert2 on tbl50000Insert for INSERT as begin select 'In Trigger2' select 'Trigger 2 Inserted', * from inserted
en
d go insert into tbl50000Insert values(2) print('---------------------- In same batch') select * from tbl50000Insert go -- Cleanup drop trigger trg50000Insert drop trigger trg50000Insert2 go delete from tbl50000Insert Third Example In the third example, the rais
error
statem
en
t is used to set the @@
ERROR
value and the BEGIN TRAN statem
en
t is used in the trigger to allow the batch to continue to run. print('Trigger issues rollback - uses rais
error
to set @@
ERROR
') go create trigger trg50000Insert on tbl50000Insert for INSERT as begin select 'Inserted', * from inserted rollback tran begin tran -- Increase @@tran
count
to allow -- batch to continue select @@tran
count
as ‘Tran
count
’ rais
error
('This is from the trigger', 14,1)
en
d go insert into tbl50000Insert values(3) select @@
ERROR
as '
ERROR
', @@TRAN
COUNT
as 'Tran
count
' go -- Cleanup drop trigger trg50000Insert go delete from tbl50000Insert Fourth Example For the fourth example, a second trigger is added to illustrate the fact that @@
ERROR
value set in the first trigger will not be se
en
in the second trigger nor will it show up in the batch after the second trigger is fi
red
. print('Trigger issues rollback - uses rais
error
to set @@
ERROR
, not se
en
in second trigger and clea
red
in batch') go create trigger trg50000Insert on tbl50000Insert for INSERT as begin select 'Inserted', * from inserted rollback begin tran -- Increase @@tran
count
to -- allow batch to continue select @@TRAN
COUNT
as 'Tran
count
' rais
error
('This is from the trigger', 14,1)
en
d go create trigger trg50000Insert2 on tbl50000Insert for INSERT as begin select @@
ERROR
as '
ERROR
', @@TRAN
COUNT
as 'Tran
count
'
en
d go insert into tbl50000Insert values(4) select @@
ERROR
as '
ERROR
', @@TRAN
COUNT
as 'Tran
count
' go -- Cleanup drop trigger trg50000Insert drop trigger trg50000Insert2 go delete from tbl50000Insert Lesson 3: Concepts – Locks and Applications This lesson outlines some of the common causes that contribute to the perception of a slow
server
. What You Will Learn After completing this lesson, you will be able to: Explain how lock hints are used and their impact. Discuss the effect on locking wh
en
an application uses Microsoft Transaction
Server
. Id
en
tify the differ
en
t kinds of deadlocks including distributed deadlock. Recomm
en
ded Reading Charter 14 “Locking”, Inside
SQL
Server
2000 by Kal
en
Delaney Charter 16 “Query Tuning”, Inside
SQL
Server
2000 by Kal
en
Delaney Q239753 – Deadlock Situation Not Detected by
SQL
Server
Q288752 – Blocked SPID Not Participating in Deadlock May Incorrectly be Chos
en
as victim Locking Hints UPDLOCK If update locks are used instead of sha
red
locks while reading a table, the locks are held until the
en
d of the statem
en
t or transaction. UPDLOCK has the advantage of allowing you to read data (without blocking other readers) and update it later with the assurance that the data has not changed since you last read it. READPAST READPAST is an optimizer hint for use with SELECT statem
en
ts. Wh
en
this hint is used,
SQL
Server
will read past locked rows. For example, assume table T1 contains a single integer column with the values of 1, 2, 3, 4, and 5. If transaction A changes the value of 3 to 8 but has not yet committed, a SELECT * FROM T1 (READPAST) yields values 1, 2, 4, 5. Tip READPAST only applies to transactions operating at READ COMMITTED isolation and only reads past row-level locks. This lock hint can be used to implem
en
t a work queue on a
SQL
Server
table. For example, assume there are many external work requests being thrown into a table and they should be serviced in approximate insertion order but they do not have to be completely FIFO. If you have 4 worker threads consuming work items from the queue they could each pick up a record using read past locking and th
en
delete the
en
try from the queue and commit wh
en
they're done. If they fail, they could rollback, leaving the
en
try on the queue for the next worker thread to pick up. Caution The READPAST hint is not compatible with HOLDLOCK. Try This: Using Locking Hints 1. Op
en
a Query Window and connect to the pubs database. 2. Execute the following statem
en
ts (--Conn 1 is optional to help you keep track of each connection): BEGIN TRANSACTION -- Conn 1 UPDATE titles SET price = price * 0.9 WHERE title_id = 'BU1032' 3. Op
en
a second connection and execute the following statem
en
ts: SELECT @@lock_timeout -- Conn 2 GO SELECT * FROM titles SELECT * FROM authors 4. Op
en
a third connection and execute the following statem
en
ts: SET LOCK_TIMEOUT 0 -- Conn 3 SELECT * FROM titles SELECT * FROM authors 5. Op
en
a fourth connection and execute the following statem
en
t: SELECT * FROM titles (READPAST) -- Conn 4 WHERE title_ID < 'C' SELECT * FROM authors How many records were returned? 3 6. Op
en
a fifth connection and execute the following statem
en
t: SELECT * FROM titles (NOLOCK) -- Conn 5 WHERE title_ID 0 the lock manager also checks for deadlocks every time a SPID gets blocked. So a single deadlock will trigger 20 seconds of more immediate deadlock detection, but if no additional deadlocks occur in that 20 seconds, the lock manager no longer checks for deadlocks at each block and detection again only happ
en
s every 5 seconds. Although normally not needed, you may use trace flag -T1205 to trace the deadlock detection process. Note Please note the distinction betwe
en
application lock and other locks’ deadlock detection. For application lock, we do not rollback the transaction of the deadlock victim but simply return a -3 to sp_getapplock, which the application needs to handle itself. Deadlock Resolution How is a deadlock resolved?
SQL
Server
picks one of the connections as a deadlock victim. The victim is chos
en
based on either which is the least exp
en
sive transaction (calculated using the number and size of the log records) to roll back or in which process “SET DEADLOCK_PRIORITY LOW” is specified. The victim’s transaction is rolled back, held locks are released, and
SQL
Server
s
en
ds
error
1205 to the victim’s cli
en
t application to notify it that it was chos
en
as a victim. The other process can th
en
obtain access to the resource it was waiting on and continue.
Error
1205: Your transaction (process ID #%d) was deadlocked with another process and has be
en
chos
en
as the deadlock victim. Rerun your transaction. Symptoms of deadlocking
Error
1205 usually is not writt
en
to the
SQL
Server
error
log. Unfortunately, you cannot use sp_altermessage to cause 1205 to be writt
en
to the
error
log. If the cli
en
t application does not capture and display
error
1205, some of the symptoms of deadlock occurring are: Cli
en
ts complain of mysteriously canceled queries wh
en
using certain features of an application. May be accompanied by excessive blocking. Lock cont
en
tion increases the chances that a deadlock will occur. Triggers and Deadlock Triggers promote the deadlock priority of the SPID for the life of the trigger execution wh
en
the DEADLOCK PRIORITY is not set to low. Wh
en
a statem
en
t in a trigger causes a deadlock to occur, the SPID executing the trigger is giv
en
prefer
en
tial treatm
en
t and will not become the victim. Warning Bug 235794 is filed against
SQL
Server
2000 where a blocked SPID that is not a participant of a deadlock may incorrectly be chos
en
as a deadlock victim if the SPID is blocked by one of the deadlock participants and the SPID has the least amount of transaction logging. See KB article Q288752: “Blocked Spid Not Participating in Deadlock May Incorrectly be Chos
en
as victim” for more information. Distributed Deadlock – Sc
en
ario 1 Distributed Deadlocks The term distributed deadlock is ambiguous. There are many types of distributed deadlocks. Sc
en
ario 1 Cli
en
t application op
en
s connection A, begins a transaction, acquires some locks, op
en
s connection B, connection B gets blocked by A but the application is designed to not commit A’s transaction until B completes. Note
SQL
Server
has no way of knowing that connection A is somehow dep
en
d
en
t on B – they are two distinct connections with two distinct transactions. This situation is discussed in sc
en
ario #4 in “Q224453 INF: Understanding and Resolving
SQL
Server
7.0 Blocking Problems”. Distributed Deadlock – Sc
en
ario 2 Sc
en
ario 2 Distributed deadlock involving bound connections. Two connections can be bound into a single transaction context with sp_getbindtok
en
/sp_bindsession or via DTC. Spid 60
en
lists in a transaction with spid 61. A third spid 62 is blocked by spid 60, but spid 61 is blocked by spid 62. Because they are doing work in the same transaction, spid 60 cannot commit until spid 61 finishes his work, but spid 61 is blocked by 62 who is blocked by 60. This sc
en
ario is described in article “Q239753 - Deadlock Situation Not Detected by
SQL
Server
.” Note
SQL
Server
6.5 and 7.0 do not detect this deadlock. The
SQL
Server
2000 deadlock detection algorithm has be
en
en
hanced to detect this type of distributed deadlock. The diagram in the slide illustrates this situation. Resources locked by a spid are below that spid (in a box). Arrows indicate blocking and are drawn from the blocked spid to the resource that the spid requires. A circle repres
en
ts a transaction; spids in the same transaction are shown in the same circle. Distributed Deadlock – Sc
en
ario 3 Sc
en
ario 3 Distributed deadlock involving linked
server
s or
server
-to-
server
RPC. Spid 60 on
Server
1 executes a sto
red
procedure on
Server
2 via linked
server
. This sto
red
procedure does a loopback linked
server
query against a table on
Server
1, and this connection is blocked by a lock held by Spid 60. Note No version of
SQL
Server
is curr
en
tly designed to detect this distributed deadlock. Lesson 4: Information Collection and Analysis This lesson outlines some of the common causes that contribute to the perception of a slow
server
. What You Will Learn After completing this lesson, you will be able to: Id
en
tify specific information needed for troubleshooting issues. Locate and collect information needed for troubleshooting issues. Analyze output of DBCC Inputbuffer, DBCC PSS, and DBCC Page commands. Review information collected from master.dbo.sysprocesses table. Review information collected from master.dbo.syslockinfo table. Review output of sp_who, sp_who2, sp_lock. Analyze Profiler log for query usage pattern. Review output of trace flags to help troubleshoot deadlocks. Recomm
en
ded Reading Q244455 - INF: Definition of Sysprocesses Waittype and Lastwaittype Fields Q244456 - INF: Description of DBCC PSS Command for
SQL
Server
7.0 Q271509 - INF: How to Monitor
SQL
Server
2000 Blocking Q251004 - How to Monitor
SQL
Server
7.0 Blocking Q224453 - Understanding and Resolving
SQL
Server
7.0 Blocking Problem Q282749 – BUG: Deadlock information reported with
SQL
Server
2000 Profiler Locking and Blocking Try This: Examine Blocked Processes 1. Op
en
a Query Window and connect to the pubs database. Execute the following statem
en
ts: BEGIN TRAN -- connection 1 UPDATE titles SET price = price + 1 2. Op
en
another connection and execute the following statem
en
t: SELECT * FROM titles-- connection 2 3. Op
en
a third connection and execute sp_who; note the process id (spid) of the blocked process. (Connection 3) 4. In the same connection, execute the following: SELECT spid, cmd, waittype FROM master..sysprocesses WHERE waittype 0 -- connection 3 5. Do not close any of the connections! What was the wait type of the blocked process? Try This: Look at locks held Assumes all your connections are still op
en
from the previous exercise. • Execute sp_lock -- Connection 3 What locks is the process from the previous example holding? Make sure you run ROLLBACK TRAN in Connection 1 to clean up your transaction. Collecting Information See Module 2 for more about how to gather this information using various tools. Recognizing Blocking Problems How to Recognize Blocking Problems Users complain about poor performance at a certain time of day, or after a certain number of users connect. SELECT * FROM sysprocesses or sp_who2 shows non-
zero
values in the blocked or BlkBy column. More severe blocking incid
en
ts will have long blocking chains or large sysprocesses.waittime values for blocked spids. Possibl
SQL
SERVER
2005 中的错误捕捉与处理
在
SQL
SERVER
2005中对于错误的处理,引入了try catch块. 例如: 代码 declare @ErrMsg varchar(1000)begin try select 5/0
en
d trybegin catch set @ErrMsg =
ERROR
_MESSAGE() rais
error
(@ErrMsg, 14, 1) print 'Err...
SQL
Server
异常代码处理
SQL
Server
使用TRY...CATCH 结构实现T
SQL
语句的错误处理,TRY命令负责监控语句执行的情况,如果有T
SQL
语句发生异常,并且严重级别(Severity Level)大于10,并且小于20,那么CATCH命令会捕获到异常的错误。 BEGIN TRY {
sql
_statem
en
t | statem
en
t_block }
EN
D TRY BEGIN CA...
sql
除以_避免
SQL
除以零错误的方法
sql
除以 This article explores the
SQL
divide
by
zero
error
and various methods for eliminating this. 本文探讨了
SQL
除以零错误以及消除该错误的各种方法。 介绍 (Introduction) We all know that in math, it is not possible...
SQL
Server
2012 错误处理增强THROW
在C#中开发人员可以使用TryCatch/Throw语句对错误进行处理,虽然在2005后,
SQL
Server
也引入了Try/Catch语句,但是Throw没有被移植过来。开发者需要使用RAIS
ERROR
语句将错误消息返回到应用程序中,对于自定义的错误信息,需要先在sys.Messages创建错误才可以在RAISE
ERROR
中使用。 在2012中,微软终于增加了THROW语句,THROW包
Delphi
5,388
社区成员
262,731
社区内容
发帖
与我相关
我的任务
Delphi
Delphi 开发及应用
复制链接
扫一扫
分享
社区描述
Delphi 开发及应用
社区管理员
加入社区
获取链接或二维码
近7日
近30日
至今
加载中
查看更多榜单
社区公告
暂无公告
试试用AI创作助手写篇文章吧
+ 用AI写文章