How to Handle Deadlocks in SQL Server?

 

Deadlocks occur when two or more sessions permanently block each other. To handle and prevent deadlocks:

  1. Identify Deadlocks:

    • Use the SQL Server error log or Extended Events to capture deadlock information.
    • Enable trace flags 1204 and 1222 for detailed deadlock information.

  2. Deadlock Prevention:

    • Ensure consistent access order for resources in your transactions.
    • Keep transactions short and minimize lock duration.
    • Use lower isolation levels if appropriate (e.g., READ COMMITTED instead of SERIALIZABLE).

  3. Retry Logic:

    • Implement retry logic in your application to handle deadlock exceptions gracefully.
    • Use TRY...CATCH in T-SQL to handle deadlock errors.

Comments

Post a Comment

Popular posts from this blog

How to Convert Word Document to PDF using C#

  Document wordDocument = null;             string Sourcepath = ConfigurationSettings.AppSettings["SourcePath"];             string DestinationPath = ConfigurationSettings.AppSettings["DestinationPath"];             DirectoryInfo dirInfo = new DirectoryInfo(Sourcepath);             FileInfo[] wordFiles = dirInfo.GetFiles("*.doc");             foreach (FileInfo wordFile in wordFiles)             {                 object paramSourceDocPath = (Object)wordFile.FullName;                 //(Object)wordFile.Name                 string paramExportFilePath = paramSourceDocPath.ToString().Replace(".doc", ".pdf"); //  string paramExportFilePath = DestinationPath + wordFile.Name.ToString().Replace(".doc", ".xps");                 if (!File.Exists(paramExportFilePath.ToString()))                 {                     Application wordApplication = new Application();                        object paramMissing = Type.M
Read more

Sql query to get counts by quarterly and half yearly

---- Quarterly  SELECT     CASE         WHEN MONTH(date_column) IN (1, 2, 3) THEN 'Q1'         WHEN MONTH(date_column) IN (4, 5, 6) THEN 'Q2'         WHEN MONTH(date_column) IN (7, 8, 9) THEN 'Q3'         WHEN MONTH(date_column) IN (10, 11, 12) THEN 'Q4'     END AS quarter,     CONCAT(YEAR(date_column),         CASE             WHEN MONTH(date_column) IN (1, 2, 3, 4, 5, 6) THEN '-H1'             WHEN MONTH(date_column) IN (7, 8, 9, 10, 11, 12) THEN '-H2'         END) AS half_year,     COUNT(*) AS count FROM your_table GROUP BY quarter, half_year ORDER BY quarter, half_year; --- Half Yearly  SELECT YEAR(date_column),    CASE             WHEN MONTH(date_column) IN (1, 2, 3, 4, 5, 6) THEN 'H1'             WHEN MONTH(date_column) IN (7, 8, 9, 10, 11, 12) THEN 'H2'         END AS half_year,     COUNT(*) AS count FROM your_table GROUP BY  half_year ORDER BY  half_year; Replace `date_column` with the column in your table that con
Read more

How to Get First Day and Last Day of a Current Quarter in SQL Server

To get the first day of the current quarter : SELECT DATEADD(qq, DATEDIFF(qq, 0, GETDATE()), 0) To get the last day of the current quarter : SELECT DATEADD (dd, -1, DATEADD(qq, DATEDIFF(qq, 0, GETDATE()) +1, 0)) To get the first day of the previous quarter SELECT DATEADD(qq, DATEDIFF(qq, 0, GETDATE()) - 1, 0) To get the last day of the previous quarter : SELECT DATEADD(dd, -1, DATEADD(qq, DATEDIFF(qq, 0, GETDATE()), 0)) To get the first day of the next quarter : SELECT DATEADD(qq, DATEDIFF(qq, 0, GETDATE()) + 1, 0) To get the last day of the next quarter : SELECT DATEADD (dd, -1, DATEADD(qq, DATEDIFF(qq, 0, GETDATE()) +2, 0))
Read more