How to create a locking scenario with SQL Server DB and application

First switch off the Auto commit on SQL ServerMgmt Studio

Disabling Autocommit mode in SSMS

By default as we know SSMS (SQL Server Management Studio) is in a Autocommit mode, which means whenever a transaction is executed then that is committed by default. If we want to disable Auto commit mode in SSMSL, then follow below steps:

1. Connect to SQL Server using SSMS
2. From the Menu bar, select Tools –> Options
3. Select Query Execution –> SQL Server –> ANSI
4. Make sure that you check the check box SET IMPLICIT_TRANSACTIONS
5. Click on OK

Now, open a new Query window and start executing the scripts.

Identify a record in the a table. Make sure that the data in this table is being used (update/delete/read (locked)) by the app

SELECT  * FROM dbo.true_r  where column1 = '123456' and id = 30059468;

update dbo.true_r set sect = 'SFC' where id = 30059468  and column1 = '123456';

if you now open up a new query window and execute the above select statement it would be hung.

Don't commit this transaction.

Now from the application perform the operation that tries to access the above record. You could see that the application is hung.

If you want to see the threaddump for linux

1.  ps -ef | grep JAVA

2. KILL -3

In major enterprises for security reasons only JREs are installed in production machines. Since jstack and other tools are only part of JDK, you wouldn’t be able to use jstack. In such circumstances, ‘kill -3’ option can be used.

kill -3 

Where:

pid: is the Process Id of the application, whose thread dump should be captured

Example:

Kill -3 37320

When ‘kill -3’ option is used thread dump is sent to standard error stream. If you are running your application in tomcat, thread dump will be sent in to /logs/catalina.out file.

Note: To my knowledge this option is supported in most flavors of *nix operating systems (Unix, Linux, HP-UX operating systems). Not sure about other Operating systems.

A good resource on threaddumps

https://dzone.com/articles/how-to-take-thread-dumps-7-options

Once you do the commit on the DB Query window, everything would be back to normal.