how to avoid deadlock in sql server

efficiency of Select COUNT (*) is low. Try to change his writing style as much as possible, while EXISTS is fast. note the difference: the return values of select count (Field of null) from Table and select count (Field of NOT null) from Table are different !!!44. When the server has enough memory, the number of preparation threads = the maximum number of connections + 5, which can maximize the efficiency; otherwise, use the number of prepared thread

. If you lock table A and table B first, lock them in this order in all stored procedures. If you first lock table B in a stored procedure and then lock Table A, this may lead to a deadlock. If the lock sequence is not designed in detail in advance, it is difficult to find deadlocks. 46. Monitor the load of the corresponding hardware through SQL server performanc

Method 1: Use the SQL Server proxy (Alert + Job)The procedure is as follows:1. First, use the following command to enable the trace flag.SQL codeDbcc traceon (3605, 1204, 1222,-1)Note:3605 output the result of DBCC to the error log.1204 return the resources and types of the locks involved in the deadlock and the affected current commands.1222 return the resources

Method 1: Use the SQL Server proxy (Alert + Job)The procedure is as follows:1. First, use the following command to enable the trace flag.SQL codeDbcc traceon (3605, 1204, 1222,-1)Note:3605 output the result of DBCC to the error log.1204 return the resources and types of the locks involved in the deadlock and the affected current commands.1222 return the resources

Document directory 21 rules for SQL Server database development 21 rules for SQL Server database developmentViews: current day: 1 this week: 1 this month: 2 Total: 3 Source:21 rules for SQL Server database developmentSortin

B First, and then locks table A, this may cause a deadlock. 5. Do not open large datasets 6. Do not use server-side cursors Compared to server-side cursors, client cursors can reduce server and network overhead and also reduce lockout time. 7. Do not ignore the problem of modifying the same record at the same time Som

Tags: SQL Server optimizationAlthough the query speed is a lot of reasons, but if through a certain optimization, you can also make the query problem to a certain extent to be resolved.The reasons for the slow query are many, and the following are common: No index or index is not used (this is the most common problem of slow query, is the defect of program design) I/O throughput is small, creat

Tags: key foreign ADL CLI data function return ESS codeTurn from: 19498327 How do I capture and record deadlocks that occur in SQL Server?How SQL Server makes the error log record deadlock February 19, 2014 18:25:55 Hits: 1313 We know that you can use the Profiler tool that

Optimization of ms SQL Server Query Author: xmllover 2007-11-29 There are many reasons for slow query speed. 1. No index or no index is used (this is the most common problem of slow query and is a defect in programming)2. Low I/O throughput, resulting in a bottleneck effect.3. the query is not optimized because no computing column is created.4. Insufficient memory5. slow network speed6. The queried data vol

the space management of the system. In a SQL Server 2000 system, the smallest space management unit is a page, and a page has 8 K. All data, logs, and indexes are stored on the page. In addition, there is a limit to using a page, which is that a row of data in a table must be on the same page and not spread across pages. The Space management unit above the page is the disk area, and a disk area is 8 consec

inserted.Transact-SQL can supportMicrosoft Azure SQL Database supports a subset of the Transact-SQL languages. Before you deploy databases to Azure SQL database, you must modify the script so that it includes only supported Transact-SQL statements. Use StatementIn Azure

, the thread pool of SQL Server is enabled by using the number of threads 45, in a certain order to access your table. If you lock table A and then lock table B, you must lock them in this order in all stored procedures. If you (inadvertently) lock table B in a stored procedure, and then lock Table A, this could result in a deadlock. Deadlocks are hard to find if

the SQL window and then reopen for recovery operations, If you also hint that the error you are using can stop the SQL service and then restart it, as the above other errors can generally be changed according to the wrong content to restore Third, shrinking the database In general, the contraction of the SQL database does not greatly reduce the size of the dat

accesses the DB can be terminated to achieve the purpose of unlocking (that is, killing the process that caused the lock).In two or more tasks, if each task locks a resource that other tasks are trying to lock, it causes these tasks to become permanently blocked, resulting in a deadlock. For example:Transaction A acquires a shared lock for row 1.Transaction B gets the shared lock for row 2.Transaction A now requests an exclusive lock on line 2, but i

= spid, @ BL = blFrom # tmp_lock_who where id = @ intcounterBeginIf @ spid = 0Select '+ Cast (@ BL as varchar (10 ))+ 'Process number. The SQL syntax executed by the process is as follows'ElseSelect 'process No. spid: '+ Cast (@ spid as varchar (10) + 'be'+ 'Process No. spid: '+ Cast (@ BL as varchar (10) +' blocking. The SQL syntax executed by the current process is as follows'DBCC inputbuffer (@ BL)End--

. Do not open large datasets6. Do not use server-side cursorsCompared to server-side cursors, client cursors can reduce server and network overhead and also reduce lockout time.7. Do not ignore the problem of modifying the same record at the same timeSometimes, two users modify the same record at the same time, so that the latter modifies the previous modifier's

existence of a deadlock, if any, one of the transactions to be revoked, so that the other transaction can be completed successfully. In general, it is a matter of undoing the small amount of modified data so that the rollback is less expensive. A database with row-level locks rarely has this problem because two users are less likely to modify the same record at the same time, and have fewer locks due to the very accidental order of data modification.

extract all the result rows, the lock may stay on the table and block other users. If you use an application that transparently submits a Transact-SQL statement to the server, the application must extract all the result rows. If the application does not do this (if it cannot be configured to do so), the blocking problem may not be resolved. To avoid this problem

Tags: process image transaction SSMS request EXE modify rollback formThis post provides two ways to avoid unhealthy or long-time blocking when SQL Server transactions are locked, allowing users and programs to wait indefinitely, even causing connection pooling connections to exceed capacity.The so-called "blocking" refers to when a transaction in a database sessi

This post provides two ways to avoid unhealthy or long-time blocking when SQL Server transactions are locked, allowing users and programs to wait indefinitely, even causing connection pooling connections to exceed capacity.The so-called "blocking" refers to when a transaction in a database session is locking up resources that other session transactions want to re