Το αρχείο INF: Ανάλυση και αποφυγή προβλήματα στον SQL Server

Μεταφράσεις άρθρων Μεταφράσεις άρθρων
Αναγν. άρθρου: 169960 - Δείτε τα προϊόντα στα οποία αναφέρεται το συγκεκριμένο άρθρο.
Ανάπτυξη όλων | Σύμπτυξη όλων

Σε αυτήν τη σελίδα

Περίληψη

Microsoft SQL Server διατηρεί συναλλακτική συνέπεια ακεραιότητα και τη βάση δεδομένων με χρήση κλειδώματος. Προαιρετικά SQL Server έκδοση 6.5 χρησιμοποιεί το κλείδωμα σε επίπεδο γραμμών για λειτουργίες εισαγωγής και χρησιμοποιεί το κλείδωμα σε επίπεδο σελίδας για άλλες λειτουργίες. Όπως και με οποιοδήποτε σύστημα σχεσιακή βάση δεδομένων, το κλείδωμα ενδέχεται να οδηγήσει σε προβλήματα μεταξύ των χρηστών.

Για παράδειγμα, έστω ότι Χρήστης1 (ή Connection1) έχει ένα κλείδωμα δεδομένων στοιχείου "A" και θέλει κλείδωμα σε στοιχείο δεδομένων "β." User2 έχει ένα κλείδωμα στοιχείου δεδομένων "B" και τώρα θέλει κλείδωμα σε στοιχείο δεδομένων "Α" Σε αυτό το σενάριο του SQL Server, Χρήστης1 ή User2 θα πέσει θύμα αδιέξοδο και ο άλλος χρήστης θα παραχωρηθεί το απαιτούμενο κλείδωμα.

Στον SQL Server, ο προγραμματιστής εφαρμογών να αποφασίσετε ποια σύνδεση θα τον υποψήφιο για θύμα αδιέξοδο χρησιμοποιώντας DEADLOCK_PRIORITY SET. Εάν ο κατασκευαστής δεν ορίζετε προτεραιότητα για προβλήματα, SQL Server επιλέγει το θύμα αδιέξοδο, επιλέγοντας τη διαδικασία που ολοκληρώνει την κυκλική αλυσίδα των κλειδωμάτων.

Εφαρμογή βάσης δεδομένων, συστήματα ενδέχεται να συμπεριφέρονται διαφορετικά όταν να μεταφερθούν από μια σχεσιακή βάση δεδομένων σε μια άλλη, με βάση την υλοποίηση του συστήματος σχεσιακής βάσης δεδομένων. Μία από τις περιοχές για αλλαγές συμπεριφοράς κλειδώματος. Αυτό το άρθρο εξηγεί πώς μπορείτε να αναλύσετε τα προβλήματα στον SQL Server και τις τεχνικές που μπορείτε να χρησιμοποιήσετε για να αποφύγετε τους.

Περισσότερες πληροφορίες

Αυτό το άρθρο δίνει έμφαση στη χρήση της σημαίας παρακολούθησης T1204 εξόδου για να αναλύσετε τα προβλήματα. Όταν έχει οριστεί η σημαία παρακολούθησης T1204, το SQL Server εκτυπώνει πληροφορίες για το αδιέξοδο, όταν παρουσιάζεται. Για να χρησιμοποιήσετε αυτήν τη σημαία παρακολούθησης, χρησιμοποιήστε την ακόλουθη εντολή σε μια γραμμή εντολών για να ξεκινήσετε τον SQL Server:
   sqlservr -c -T1204
				

Τα αποτελέσματα ανίχνευσης αποστέλλονται στο παράθυρο της κονσόλας, εκτός αν ορίσετε σημαία παρακολούθησης T3605, τα οποία αποστέλλει την έξοδο παρακολούθησης στο αρχείο καταγραφής σφαλμάτων.

Deadlocks can occur when two connections update tables in opposite order. For example, one connection inserts into table "example1" first and then into "example2," while another connection inserts into table "example2" first and then into "example1" within a transaction. An example scenario is useful to illustrate how to avoid deadlocks.

The following are the SQL statements used to create the table used for this example:
   create table example1 (column1 int, column2 char(20), column3 char(50))
   go
   create table example2 (column1 int, column2 char(20), column3 char(50))
   go
   declare @lvar int
   select @lvar = 0
   while @lvar < 500
   begin
   insert into example1 values (@lvar, 'AAA', 'CCC')
   insert into example2 values (@lvar, 'AAA', 'CCC')
   select @lvar = @lvar + 1
   end
   go
   create unique clustered index ex1ind1 on example1 (column1, column2)
   with fill factor = 90, PAD_INDEX
   go
   create unique clustered index ex2ind1 on example2 (column1, column2)
   with fill factor = 90, PAD_INDEX
   go
				

Example 1: Table Insertions in Opposite Order

In this example, two tables were inserted in opposite order and a deadlock occurred. Deadlocks can also occur when two or more connections perform updates or deletes on tables in opposite order.
   Connection1 > BEGIN TRANSACTION
   Connection2 > BEGIN TRANSACTION
   Connection1 > INSERT INTO example1 VALUES (100, 'AAAA', 'CCC')
   Connection2 > INSERT INTO example2 VALUES (200, 'AAAB', 'CCC')
   Connection2 > INSERT INTO example1 VALUES (200, 'AAAB', 'CCC')
				

At this point, Connection1 may block Connection2 because the row Connection2 is inserting may be on the same page where Connection1 has already inserted a row and is holding a lock.
   Connection1 > INSERT INTO example2 VALUES (100, 'AAAA', 'CCC')
				

At this point, Connection2 may block Connection1, because the row Connection1 is inserting may be on the same page where Connection2 has already inserted a row and is holding a lock. This causes a deadlock.

The following is the output for trace flag 1204 when the deadlock occurred:
97/04/20 11:51:57.88 spid13   *** DEADLOCK DETECTED with spid 14 ***
   spid 13 requesting EX_PAGE (waittype 0x8005), blocked by:
     EX_PAGE: spid 14, dbid 6, page 0x188, table example2, indid 0x1
     pcurcmd INSERT(0xc3), input buffer: INSERT INTO example2 VALUES (100,
     'AAAA', 'CCC')
   spid 14 waiting for EX_PAGE (waittype 0x8005), blocked by:
     EX_PAGE: spid 13, dbid 6, page 0x180, table example1, indid 0x1
     pcurcmd INSERT(0xc3), input buffer: INSERT INTO example1 VALUES (200,
   'AAAB', 'CCC')
   VICTIM: spid 13, pstat 0x0000 , cputime 30
				

Each line of the deadlock trace can tell users more about a deadlock. Connection1 is spid 13 and Connection2 is spid 14 (you can determine the spid associated with a connection by using the sp_who system stored procedure).
   >> 97/04/20 11:51:57.88 spid13   *** DEADLOCK DETECTED with spid 14 ***
   The deadlock was detected between spid 13 and spid 14.
   >> spid 13 requesting EX_PAGE (waittype 0x8005), blocked by:
   >>   EX_PAGE: spid 14, dbid 6, page 0x188, table example2, indid 0x1
   >>   pcurcmd INSERT(0xc3), input buffer: INSERT INTO example2 VALUES
   (100, 'AAAA', 'CCC')
				

Spid 13 was requesting EX_PAGE lock and was blocked by spid 14, which already has EX_PAGE lock for page 0x188 on table example2 in dbid 6. The lock is held on the page belonging to clustered index.
      Indid Value         Description
-------------------------------------
         0                Data page if there is no clustered index, or the
                          leaf page of a clustered index if there is one
         1                Non-leaf page of the clustered index page
       255                Text/image page
    Any other value       Non-clustered secondary index
				

The current command executed by spid 13 is an INSERT and the trace gives part of the input buffer.
   >> spid 14 waiting for EX_PAGE (waittype 0x8005), blocked by:
   >>   EX_PAGE: spid 13, dbid 6, page 0x180, table example1, indid 0x1
   >>   pcurcmd INSERT(0xc3), input buffer: INSERT INTO example1 VALUES
   (200, 'AAAB', 'CCC')
				

Spid 14 is waiting for EX_PAGE lock and is being blocked by spid 13, which already holds EX_PAGE lock on the same page.
   >> VICTIM: spid 13, pstat 0x0000 , cputime 30
   SQL Server has chosen spid 13 as the deadlock victim.
				

The following is an explanation of what the various locks mean in the trace:

SH_INT and EX_INT
Intent locks that are taken on a higher-level item (for example, a table) before lower-level locks (for example, a page) can be taken, because the lock manager is unaware of the relationship between different types of items (in this case, pages and tables). If an EX_INT lock was not taken on the table before taking EX_PAG locks on the pages, another user could take an EX_TAB lock on the same table and the lock manager would not know that a conflict existed. Currently, SQL Server has intent locks only on tables. There are two kinds of intent locks: shared (SH_INT) and exclusive (EX_INT) locks.

EX_PAGE
This is an exclusive page lock that is taken when a page is updated due to a DELETE, UPDATE, or INSERT statement with insert row-level locking (IRL) disabled.

UP_PAGE
This is an update page lock that is taken in place of a shared-page lock when a page is scanned and the optimizer knows that the page will be updated (or the UPDLOCK hint is used).

PR_EXT, NX_EXT, UPD_EXT, and EX_EXT
These locks are taken when allocating or deallocating disk space. UPD_EXT is taken when allocating or deallocating a page from an existing extent and the others are used when allocating or deallocating entire extents.

IX_PAGE and LN_PAGE
These are IRL locks. IX_PAGE is an intent-to-do-row-locking lock on a page. LN_PAGE is taken when a page on which IRL is being done needs to be split.

RLOCK and XRLOCK
These short-term locks are taken when traversing an index b-tree. There are two types of this kind of lock: shared (RLOCK) and exclusive (XRLOCK). Shared locks are taken during scan, while exclusive locks are taken on index pages during an update.

EX_TAB
This is an exclusive table lock that occurs when the SQL Server optimizer determines that a table scan is the most efficient way to solve an update query (for example, when there are no indexes on a table). EX_TAB locks also appear when you lock the table with TABLOCKX hint or when SQL Server escalates the page locks on a table to a table lock.

SH_TAB
This is a shared table lock that is used when the optimizer assumes that most of the table will be scanned (or page locking escalates) or the TABLOCK hint is used.

The previous deadlock example can be avoided if the two connections update tables in the following sequence:
   Connection1 > BEGIN TRANSACTION
   Connection2 > BEGIN TRANSACTION
   Connection1 > INSERT INTO example1 VALUES (100, 'AAAA', 'CCC')
   Connection2 > INSERT INTO example1 VALUES (200, 'AAAB', 'CCC')
   Connection2 > INSERT INTO example2 VALUES (200, 'AAAB', 'CCC')
   Connection1 > INSERT INTO example2 VALUES (100, 'AAAA', 'CCC')
				

Example 2: Insertions to Different Parts of the Same Table

This deadlock can also occur when two connections insert into different parts of the same table in opposite order when rows share pages. Για παράδειγμα:
   Connection1 > BEGIN TRANSACTION
   Connection2 > BEGIN TRANSACTION
   Connection1 > INSERT INTO example1 VALUES (100, 'AAAA', 'CCC')
   Connection2 > INSERT INTO example1 VALUES (400, 'AAAB', 'CCC')
   Connection1 > INSERT INTO example1 VALUES (400, 'AAAA', 'CCC')
				

In this example table, there is a clustered index on the first column of the example1 table. Rows with the same values for the first column will tend to fall on the same page. In the example, the second row inserted by Connection1 will probably fall on the same page as the first row inserted by Connection2, because they both have a clustered index value of 400. This causes Connection2 to block Connection1.
   Connection2 > INSERT INTO example1 VALUES (100, 'AAAB', 'CCC')
				

Now Connection2 may also be blocked by Connection1, leading to a deadlock. The following is the deadlock trace:
   97/04/20 12:56:01.40 spid16   *** DEADLOCK DETECTED with spid 15 ***
   spid 16 requesting EX_PAGE (waittype 0x8005), blocked by:
     EX_PAGE: spid 15, dbid 6, page 0x2c5, table example1, indid 0
     pcurcmd INSERT(0xc3), input buffer: INSERT INTO example1 VALUES (100,
   'AAAB', 'CCC')
   spid 15 waiting for EX_PAGE (waittype 0x8005), blocked by:
     EX_PAGE: spid 16, dbid 6, page 0x8bd, table example1, indid 0
     pcurcmd INSERT(0xc3), input buffer: INSERT INTO example1 VALUES (400,
   'AAAA', 'CCC')
   VICTIM: spid 16, pstat 0x0000 , cputime 130
				

The spid 16 request for EX_PAGE lock for page 0x2c5 is blocked by spid 15, which already holds EX_PAGE lock for page 0x2c5 after it did the first insert. And spid 15 also got blocked by spid 16 on waiting for a EX_PAGE lock for page 0x8db leading to deadlock.

This deadlock can be avoided by using the following command to enable IRL for table example1:
   sp_tableoption 'example1', 'insert row lock', true
				

Example 3: Insertions Using IRL

IRL allows two or more users to share a page when they do only insert operations, which often results in better throughput. However, enabling IRL will not always reduce deadlocks. In some cases, IRL may introduce deadlocks.
   Connection1 > BEGIN TRANSACTION
   Connection2 > BEGIN TRANSACTION
   Connection1 > INSERT INTO example1 VALUES (100, 'AAAA', 'CCC')
   Connection2 > INSERT INTO example1 VALUES (105, 'AAAB', 'CCC')
				

With IRL enabled, both connections will hold an IX_PAGE lock on the page containing the two new rows. If IRL was disabled, Connection1 would have acquired an EX_PAGE lock, and Connection2 would have been blocked immediately.
   Connection2 > UPDATE example1 SET column3 = 'CCCB' where column1 = 105
   and column2 = 'AAAB'
				

At this point, Connection2 needs an exclusive page lock to do an UPDATE statement, which is incompatible with Connection1's IX_PAGE lock. Therefore, Connection2 will wait.
   Connection1 > UPDATE example1 SET column3 = 'CCCA' where column1 = 100
   and column2 = 'AAAA'
				

Now Connection1 may be blocked by Connection2, leading to a deadlock. The following is the deadlock trace:
   97/04/20 15:13:50.07 spid17   *** DEADLOCK DETECTED with spid 18 ***
   spid 17 requesting UP_PAGE (waittype 0x8007), blocked by:
     IX_PAGE: spid 18, dbid 6, page 0x2c5, table example1, indid 0
     pcurcmd UPDATE(0xc5), input buffer: UPDATE example1 SET column3 =
   'CCCA' where column1 = 100 and column2 = 'AAAA'
   spid 18 waiting for UP_PAGE (waittype 0x8007), blocked by:
     IX_PAGE: spid 17, dbid 6, page 0x2c5, table example1, indid 0
     pcurcmd UPDATE(0xc5), input buffer: UPDATE example1 SET column3 =
   'CCCB' where column1 = 105 and column2 = 'AAAB'
   VICTIM: spid 17, pstat 0x0000 , cputime 20
				

Spid 17 (connection one) is waiting for an UP_PAGE lock, which is the first step to getting an exclusive page lock. It is being blocked by spid 18, which holds IX_PAGE lock on page 0x2c5. Spid 18 is waiting for UP_PAGE lock on the same page, and is being blocked by IX_PAGE lock held by spid 17. This leads to a deadlock because IX_PAGE lock is sharable, whereas UP_LOCK is not. During the first inserts, both the spids got IX_PAGE lock on the same page, and later they tried to upgrade the lock to UP_PAGE lock, which is not possible because UP_PAGE lock is exclusive.

The one way to avoid the deadlock is to insert the updated value directly into the table instead of inserting and then updating the row in the same transaction. If this is not possible, using the following command to disable IRL will help to avoid deadlock:
   sp_tableoption 'example1', 'insert row lock', false
				

Example 4: Insertions to Rows on the Same Page

A deadlock may also result when the rows the two spids are working on are different but belong to the same page.
   Connection1 > BEGIN TRANSACTION
   Connection2 > BEGIN TRANSACTION
   Connection1 > INSERT INTO example1 VALUES (100, 'AAAA', 'CCC')
   Connection2 > INSERT INTO example1 VALUES (400, 'AAAB', 'CCC')
   Connection1 > UPDATE example1 SET column3 = 'CCCA' where column1 = 405
   and column2 = 'AAAA'
				

At this point, Connection1 may be blocked by Connection2. This situation may occur because Connection1 wants to update a row in a page where Connection2 has already inserted a row.
   Connection2 > UPDATE example1 SET column3 = 'CCCB' where column1 = 105
   and column2 = 'AAAB'
				

Σε αυτό το σημείο, Connection2 μπορεί επίσης να εμποδίζεται από Connection1, η οποία θα οδηγήσει σε αδιέξοδο. Αυτή η κατάσταση μπορεί να προκύψει όταν Connection2 θέλει να ενημερώνετε μια γραμμή σε μια σελίδα όπου Connection1 έχει εισαγάγει μια γραμμή. Ακολουθεί η παρακολούθηση αδιέξοδη κατάσταση:
   97/04/20 15:48:21.18 spid20   *** DEADLOCK DETECTED with spid 19 ***
   spid 20 requesting UP_PAGE (waittype 0x8007), blocked by:
     EX_PAGE: spid 19, dbid 6, page 0x2c4, table example1, indid 0
     pcurcmd UPDATE(0xc5), input buffer: UPDATE example1 SET column3 =
   'CCCB' where column1 = 105 and column2 = 'AAAB'
   spid 19 waiting for UP_PAGE (waittype 0x8007), blocked by:
     EX_PAGE: spid 20, dbid 6, page 0xc48, table example1, indid 0
     pcurcmd UPDATE(0xc5), input buffer: UPDATE example1 SET column3 =
   'CCCA' where column1 = 405 and column2 = 'AAAA'
   VICTIM: spid 20, pstat 0x0000 , cputime 60
				

Αδιέξοδο αυτό μπορεί να αποφεύγονται κατά τη διάδοσή εκτός των γραμμών σε διαφορετικές σελίδες. Μία μέθοδος για να το κάνετε αυτό είναι να δημιουργήσετε ξανά το ευρετήριο συμπλέγματος σε αυτόν τον πίνακα με έναν παράγοντα συμπλήρωσης μεγάλο. Η ακόλουθη είναι μια δήλωση που δημιουργεί ένα ευρετήριο συμπλέγματος με έναν παράγοντα συμπλήρωσης 50 τοις εκατό:
   create unique clustered index ex1ind1 on example1 (column1, column2)
   with fill factor = 50, PAD_INDEX
				

Αυτή η εντολή δημιουργεί το ευρετήριο συμπλέγματος, αφήνοντας κενό, συμπεριλαμβανομένων των επιπέδων που δεν είναι leaf συγκεντρωτικό ευρετήριο (λόγω της επιλογής PAD_INDEX) τα μισά από τις σελίδες. Ο πίνακας καταλαμβάνει διπλά το πραγματικό μέγεθος και τον αριθμό των γραμμών ανά σελίδα οι μισοί που είχαν.

Η συμπλήρωση παράγοντα δεν διατηρείται σε έναν πίνακα, ο πίνακας είναι re-organized με τον παράγοντα συμπλήρωσης καθορίζεται κατά το χρόνο δημιουργίας ευρετηρίου. Με τον καιρό, των γραμμών ανά σελίδα θα αλλάξει από τον παράγοντα συμπλήρωσης καθορίζεται κατά τη δημιουργία ευρετηρίου. Όταν συμβεί αυτό, μπορεί να είναι καλή ιδέα να δημιουργήσετε ξανά το ευρετήριο συμπλέγματος με το συντελεστή γεμίσματος που θέλετε.

Είναι μια άλλη λύση για να αποφύγετε την προηγούμενη κατάσταση αδιεξόδου στο παράθυρο γραψίματος του πίνακα με ψεύτικος στήλες (για παράδειγμα, dummy1 char(255)). Αυτό αυξάνει το μέγεθος της γραμμής και οδηγεί σε λιγότερες γραμμές ανά σελίδα (μόνο μία γραμμή ανά σελίδα). Because this type of padding is maintained over time, you do not need to re-create the clustered index to maintain the padding (though you may want to re-create the clustered index for other reasons). The disadvantage of this technique is that storage space is wasted on dummy fields.

Example 5: Padding Rows

Padding rows leads to fewer rows per page (hence fewer deadlocks), but it will not completely eliminate deadlocks.

In this example table, example1 is padded to occupy one row per page. The following are the statements used to create the table for this example:
   create table example1 (column1 int, column2 char(20), column3 char(50),
   dummy_column4 char (255), dummy_column5 char (255), dummy_column6 char
   (255))
   go
   create unique index ex1ind5 on example1 (column3, column2, column1,
   dummy_column4, dummy_column5, dummy_column6) with fill factor = 85
   go
   Connection1 > BEGIN TRANSACTION
   Connection2 > BEGIN TRANSACTION
   Connection1 > INSERT INTO example1 VALUES (100, 'AAAA', 'CCC', ' ', ' ',
   ' ', ' ')
   Connection2 > INSERT INTO example1 VALUES (400, 'AAAB', 'CCC', ' ', ' ',
   ' ', ' ')
   Connection1 > UPDATE example1 SET column3 = 'CCCA' where column1 = 401
   and column2 = 'AAAA'
				

At this point, Connection1 is blocked by Connection2 while updating the row. Because SQL Server must maintain page-chain pointers, it locks the previous page, the next page, and the page that is being updated. Because Connection2 holds a lock on the previous page, Connection1 must wait until Connection2 commits the transaction.
   Connection2 > UPDATE example1 SET column3 = 'CCCB' where column1 = 101
   and column2 = 'AAAB'
				

At this point, Connection2 is blocked by Connection1 because it must lock the previous page, which is currently locked by Connection1. The result is a deadlock. The following is the deadlock trace:
   spid 20 requesting UP_PAGE (waittype 0x8007), blocked by:
     EX_PAGE: spid 19, dbid 6, page 0x12b5, table example1, indid 0
     pcurcmd UPDATE(0xc5), input buffer: UPDATE example1 SET column3 =
   'CCCB' where column1 = 101 and column2 = 'AAAB'
   spid 19 waiting for UP_PAGE (waittype 0x8007), blocked by:
     EX_PAGE: spid 20, dbid 6, page 0x1531, table example1, indid 0
     pcurcmd UPDATE(0xc5), input buffer: UPDATE example1 SET column3 =
   'CCCA' where column1 = 401 and column2 = 'AAAA'
   VICTIM: spid 20, pstat 0x0000 , cputime 300
				

This deadlock can be avoided by inserting dummy rows between the rows that are being inserted, updated, or deleted. For example, if Connection1 works (inserts, updates, or deletes) with row pk = 1 and Connection2 works with row pk = 5, inserting a row between these two rows (such as a row containing pk = 3) will avoid deadlocks. This method also increases the size of the table, but may be the best solution for those queue tables critical to the application.

Example 6: Nonclustered Indexes

In some cases, non-clustered secondary indexes may introduce deadlocks. In this example, the maintenance of the secondary index introduces deadlock.

The following is the statement used to create the secondary index used in this example:
   create index ex1ind2 on example1 (column3) with fill factor = 90,
   PAD_INDEX
   Connection1 > BEGIN TRANSACTION
   Connection2 > BEGIN TRANSACTION
   Connection1 > INSERT INTO example1 VALUES (100, 'AAAA', 'CCBA', ' ', '
   ', ' ', ' ')
   Connection2 > INSERT INTO example1 VALUES (300, 'AAAB', 'CCCZ', ' ', '
   ', ' ', ' ')
   Connection2 > UPDATE example1 SET column3 = 'CCBA' where column1 = 105
				

At this point, Connection2 may be blocked by Connection1 because Connection1 may be holding a lock on the secondary non-clustered index page where Connection2 needs to update.
   Connection1 > UPDATE example1 SET column3 = 'CCCZ' where column1 = 305
				

At this point, Connection1 may be blocked by Connection2, resulting in a deadlock. This situation can happen when Connection1 is waiting for a lock to update the non-clustered secondary index where Connection2 has already inserted and holds a lock on that page. The following is the deadlock trace for this deadlock example:
   97/04/20 19:05:38.75 spid11   *** DEADLOCK DETECTED with spid 12 ***
   spid 11 requesting EX_PAGE (waittype 0x8005), blocked by:
     EX_PAGE: spid 12, dbid 6, page 0x112f, table example1, indid 0x2
     pcurcmd UPDATE(0xc5), input buffer: UPDATE example1 SET column3 =
   'CCCZ' where column1 = 305
   spid 12 waiting for EX_PAGE (waittype 0x8005), blocked by:
     EX_PAGE: spid 11, dbid 6, page 0x1108, table example1, indid 0x2
     pcurcmd UPDATE(0xc5), input buffer: UPDATE example1 SET column3 =
   'CCBA' where column1 = 105
   VICTIM: spid 11, pstat 0x0000 , cputime 50
				

This deadlock can be avoided by dropping the secondary index. It is not possible to pad the index to contain one row per page, so this situation can be avoided only by eliminating the non-clustered secondary index or by modifying the application.

Deadlocks may occur with more than two connections, in which case the deadlock trace lists the spids involved in the deadlock and also the conflicting locks. Deadlocks may occur with RLOCK and XRLOCK locks, which are acquired during index traversing. Deadlocks may also occur because of extent locks (PR_EXT, NX_EXT, UPD_EXT & EX_EXT).

For additional information about analyzing deadlocks, you can enable the following trace flags:

T1200
Prints all of the lock request/release information when it occurs, whether a deadlock is involved or not. This is expensive in terms of performance, but it can be useful for analysis.

T1206
Prints all of the locks held by participating spids in the deadlock.

T1208
Prints the host name and program name supplied by the client. This can help identify a client involved in a deadlock, assuming the client specifies a unique value for each connection.

Ιδιότητες

Αναγν. άρθρου: 169960 - Τελευταία αναθεώρηση: Σάββατο, 18 Δεκεμβρίου 2010 - Αναθεώρηση: 2.0
Οι πληροφορίες σε αυτό το άρθρο ισχύουν για:
  • Microsoft SQL Server 6.5 Standard Edition
Λέξεις-κλειδιά: 
kbhowto kbusage kbmt KB169960 KbMtel
Μηχανικά μεταφρασμένο
ΣΗΜΑΝΤΙΚΟ: Αυτό το άρθρο είναι προϊόν λογισμικού μηχανικής μετάφρασης της Microsoft και όχι ανθρώπινης μετάφρασης. Η Microsoft σάς προσφέρει άρθρα που είναι προϊόντα ανθρώπινης αλλά και μηχανικής μετάφρασης έτσι ώστε να έχετε πρόσβαση σε όλα τα άρθρα της Γνωσιακής Βάσης μας στη δική σας γλώσσα. Ωστόσο, ένα άρθρο που έχει προκύψει από μηχανική μετάφραση δεν είναι πάντα άριστης ποιότητας. Ενδέχεται να περιέχει λεξιλογικά, συντακτικά ή γραμματικά λάθη, όπως ακριβώς τα λάθη που θα έκανε ένας μη φυσικός ομιλητής επιχειρώντας να μιλήσει τη γλώσσα σας. Η Microsoft δεν φέρει καμία ευθύνη για τυχόν ανακρίβειες, σφάλματα ή ζημίες που προκύψουν λόγω τυχόν παρερμηνειών στη μετάφραση του περιεχομένου ή χρήσης του από τους πελάτες της. Επίσης, η Microsoft πραγματοποιεί συχνά ενημερώσεις στο λογισμικό μηχανικής μετάφρασης.
Η αγγλική έκδοση αυτού του άρθρου είναι η ακόλουθη:169960
Αποποίηση ευθυνών για περιεχόμενο της Γνωσιακής Βάσης (KB) που έχει αποσυρθεί
This article was written about products for which Microsoft no longer offers support. Therefore, this article is offered "as is" and will no longer be updated.

Αποστολή σχολίων

 

Contact us for more help

Contact us for more help
Connect with Answer Desk for expert help.
Get more support from smallbusiness.support.microsoft.com