Category: Technology

Releasing an ASP.NET application requires careful planning to ensure a smooth deployment without disruptions. Below is a checklist that I use to cover essential pre- and post-release checks, focusing on database changes, application performance, and stability.

Pre-Release Checklist

Follow the step-by-step deployment guide to make the release. For a major release, it is recommended to schedule downtime and include a maintenance page for the user.

Once the release is done, this is the post-release checklist.

Post-Release Checklist

This checklist ensures a structured and thorough approach to deploying your ASP.NET application and minimizing risks to ensure a smooth production environment. Let me know if I have missed anything!

When managing a SQL Server database, you may need to find occurrences of a specific text string within database objects, such as table names, stored procedures, functions, or views. This can be useful for debugging, auditing, or refactoring existing code.

The following stored procedure, sp_search_code, allows you to search for a specific text string across different database objects, including table names, stored procedures, functions, and views

CREATE PROC [dbo].[sp_search_code] (
	@SearchStr VARCHAR(100)
	,@RowsReturned INT = NULL OUT
	)
AS
BEGIN
	SET NOCOUNT ON

	SELECT DISTINCT USER_NAME(o.uid) + '.' 
    + OBJECT_NAME(c.id) AS 'Object name'
		,CASE 
			WHEN OBJECTPROPERTY(c.id, 'IsReplProc') = 1
				THEN 'Replication stored procedure'
			WHEN OBJECTPROPERTY(c.id, 'IsExtendedProc') = 1
				THEN 'Extended stored procedure'
			WHEN OBJECTPROPERTY(c.id, 'IsProcedure') = 1
				THEN 'Stored Procedure'
			WHEN OBJECTPROPERTY(c.id, 'IsTrigger') = 1
				THEN 'Trigger'
			WHEN OBJECTPROPERTY(c.id, 'IsTableFunction') = 1
				THEN 'Table-valued function'
			WHEN OBJECTPROPERTY(c.id, 'IsScalarFunction') = 1
				THEN 'Scalar-valued function'
			WHEN OBJECTPROPERTY(c.id, 'IsInlineFunction') = 1
				THEN 'Inline function'
			END AS 'Object type'
		,'EXEC sp_helptext ''' + USER_NAME(o.uid) + '.' 
          + OBJECT_NAME(c.id) + '''' 
         AS 'Run this command to see the object text'
	FROM syscomments c
	INNER JOIN sysobjects o ON c.id = o.id
	WHERE c.TEXT LIKE '%' + @SearchStr + '%'
		AND encrypted = 0
		AND (
			OBJECTPROPERTY(c.id, 'IsReplProc') = 1
			OR OBJECTPROPERTY(c.id, 'IsExtendedProc') = 1
			OR OBJECTPROPERTY(c.id, 'IsProcedure') = 1
			OR OBJECTPROPERTY(c.id, 'IsTrigger') = 1
			OR OBJECTPROPERTY(c.id, 'IsTableFunction') = 1
			OR OBJECTPROPERTY(c.id, 'IsScalarFunction') = 1
			OR OBJECTPROPERTY(c.id, 'IsInlineFunction') = 1
			)
	ORDER BY 'Object type'
		,'Object name'

	SET @RowsReturned = @@ROWCOUNT
END

How to Use the Stored Procedure

To execute this stored procedure and search for a specific text string, you can use the following SQL command:

EXEC sp_search_code 'Customer';

This command will return a list of tables, views, stored procedures, and functions containing the word Customer in their names or definitions.

This stored procedure is a powerful tool for searching database objects based on a specific text string. Whether you need to find references to a particular table or locate a function containing certain logic, this procedure simplifies the process. You can further enhance it by adding search capabilities for triggers, indexes, or column names as needed.

We have been struggling with many deadlocks in an old project that started getting busier during working hours. This project was initially built quickly to solve a small problem in stock operation. Over the last three years, a lot of code and processes have been added to the platform. As a result, we have a big platform with a very unstable base of code structure. This is one of the common issues where we see projects were developed without any longer-term plan.

So, what is deadlocks?

SQL Server deadlocks occur when two processes/queries attempt to access the same data, but each query has already locked the data that the other query needs. In simple terms, it is a lock that takes you to the dead end. While query optimization can improve the speed and efficiency of queries, it’s crucial to understand the root cause of deadlocks and how to prevent them. A deadlock happens when two or more tasks permanently block each other, with each task holding a lock on a resource that the other tasks are trying to acquire. 

For example, Process-1 needs to modify data in the Product table, which also requires reading data from the ProductDetails table. So, Process-1 puts a lock in the Product table and requests data from the ProductDetails table. At the same time, Process-2 needs to modify data in the ProductDetails table, which also requires reading data from the Product table. So, Process-2 puts a lock in the ProductDetails table and requests data from the Product table.

At this point neither process can finish because they are both waiting on locked resources. i.e. they are deadlocked. One of them must be killed to allow either of them to finish. SQL decides which is the “least expensive process to kill” and it becomes the Deadlock victim.

Let’s not confuse deadlock with Blocking.

Although it’s based on the same principles, blocking is much simpler than deadlock. Blocking happens when two or more processes need access to the same resource. In Microsoft SQL Server, only one process can use a resource at any given time. If the first process is already using (or locking) the resource, the second process is blocked from accessing it. SQL Server will then make the second process wait until the first process has completed its task.

What was happening in our case

We have many SQL jobs that frequently get product feeds from our supplier and update the product, product details table and a few other tables. During the process, the SQL job locks the Product table to make necessary updates and request details of the product from the ProductDetails table. This process takes a long time due to the large volume of data the SQL job processes. At the same time, if anyone in our team uses our internal web applications to update any product details that require updating the ProductDetails table and getting data from the product table, the web application becomes the victim of the deadlock. Also, the team always complains that the product update process takes a long to time complete even they do not get the deadlock.

So what we have done to resolve the issue

We have identified that our platform is a victim of both blocking and deadlock. So, we have taken the following steps to improve the issue.

Create a separate temporary Product table to hold the products from all SQL jobs. This will free the main Product table for the web applications.

Put multiple similar SQL jobs under one job but in steps. Set to run the following step once the current step is completed and succeeds. The last step is to update the main Product table from the temp Product table.

In our case it is important to allow our web application to get the priority. So we set SET DEADLOCK_PRIORITY LOW for the query behind the SQL job. By default SQL set SET DEADLOCK_PRIORITY NORMAL for all queries so the web application query will have the priority and SQL Job will be the victim of the deadlock.

 Use ” WITH (NOLOCK)” for the select queries, which is equivalent to READ UNCOMMITTED

Ensure enough Index and tune the transactional code to make sure the query runs faster.

Deadlock code example

To experience the deadlock, you can use the code below.

Creating two tables and adding some sample data

CREATE TABLE ##Product (
    ProductId INT IDENTITY,
    ProductName VARCHAR(16),
    ProductCode VARCHAR(16)
)
GO

INSERT INTO ##Product (ProductName, ProductCode)
VALUES ('iPhone', '123'), ('iPad', '124')
GO

CREATE TABLE ##Supplier(
    SupplierId INT IDENTITY,
    SupplierName VARCHAR(64),
    SupplierEmail VARCHAR(64)
)
GO

INSERT INTO ##Supplier (SupplierName, SupplierEmail)
VALUES ('Apple', '222-555-6666'), ('Samsung', '888-568-1234')
GO

Now, Open two separate query windows in SSMS. In one window, enter the code for session 1, and in the other, enter the code for session 2. Execute the steps in each session alternately, switching between the two query windows as needed. You’ll notice that each transaction holds a lock on a resource that the other transaction is also trying to access.

-- Step 1 --  -- Run in Session 1 --
BEGIN TRAN;                 

UPDATE ##Product SET ProductName = 'Apple Watch'
WHERE ProductId = 1

-- Step 2 -- --  Run in Session 2 --
BEGIN TRAN;

UPDATE ##Supplier SET SupplierEmail = N't@t.com'
WHERE SupplierId = 1

-- Step 3 -- --  Run in Session 1 --
UPDATE ##Supplier SET SupplierEmail = N's@s.com'
WHERE SupplierId = 1

-- Step 4 -- --  Run in Session 2 --
UPDATE ##Product SET ProductCode = '234'
WHERE ProductId = 1

And You will get deadlock.

Msg 1205, Level 13, State 45, Line 7
Transaction (Process ID 70) was deadlocked on lock resources with another process and has been chosen as the deadlock victim. Rerun the transaction.