3

One of the applications I am supporting, is running into a weird issue. In a particular page, there is an insert query.

INSERT INTO TBL_Parts_In_Arrival_Details ( PIAD_Parts_In_Arrival_Detail_ID, 

PIAD_Parts_In_Request_ID, PIAD_Warehouse_ID, PIAD_Location_ID, PIAD_Qty_Received, 

PIAD_WAP_Price, PIAD_WAP_Currency, PIAD_Arrival_Date, PIAD_SupplierSent_Invoice_No, 
PIAD_Invoice_No, Use_Count, PIAD_CreatedDate, PIAD_CreatedBy, PIAD_ModifiedDate, PIAD_ModifiedBy ) 


VALUES ( @PIAD_Parts_In_Arrival_Detail_ID, @PITM_Parts_In_Request_ID, @PIAD_Warehouse_ID, 

@PIAD_Location_ID, @PIAD_Qty_Received, @PIAD_WAP_Price, @PIAD_WAP_Currency, @PIAD_Arrival_Date, 

@PIAD_SupplierSent_Invoice_No, @PIAD_Invoice_No, 0, GETDATE(), 'prism.dev', GETDATE(), 'prism.dev' )

This query was working fine in SQL server 2000. But, since the server upgrade to 2014, it locks up the database. This query is run as a transaction. If I run this query manually using query analyzer, it works wonderfully.

Now, if there is any problem with the query, normally it should rollback the transaction. But, in this particular case, it doesn't. So, I end up with some 5-6 instances of same process in SQL server activity monitor, all of them in suspended state. At that point, server stops responding to queries, and only thing I can do is kill all processes.

Since, I am pretty much a noob in this, and out of depth here. I want to understand what might be causing this, so I can try to fix this. Basically, just looking to understand possible reasons.

I am not sure, what details, I should provide to make this more question more readable. If you feel there is something I missed, do let me know, and I will add it promptly.

Here is the result of SELECT status, wait_type, blocking_session_id FROM sys.dm_exec_requests;:

+------------+------------------------------------+---------------------+
|   status   |             wait_type              | blocking_session_id |
+------------+------------------------------------+---------------------+
| sleeping   | NULL                               |                   0 |
| sleeping   | NULL                               |                   0 |
| sleeping   | NULL                               |                   0 |
| sleeping   | NULL                               |                   0 |
| sleeping   | NULL                               |                   0 |
| sleeping   | NULL                               |                   0 |
| sleeping   | NULL                               |                   0 |
| sleeping   | NULL                               |                   0 |
| sleeping   | NULL                               |                   0 |
| background | BROKER_EVENTHANDLER                |                   0 |
| background | BROKER_TRANSMITTER                 |                   0 |
| background | SLEEP_TASK                         |                   0 |
| background | HADR_FILESTREAM_IOMGR_IOCOMPLETION |                   0 |
| sleeping   | NULL                               |                   0 |
| sleeping   | NULL                               |                   0 |
| sleeping   | NULL                               |                   0 |
| sleeping   | NULL                               |                   0 |
| sleeping   | NULL                               |                   0 |
| sleeping   | NULL                               |                   0 |
| sleeping   | NULL                               |                   0 |
| sleeping   | NULL                               |                   0 |
| running    | NULL                               |                   0 |
+------------+------------------------------------+---------------------+

Update:

So, I was looking more into this. Turns out, this query locks up, because right after this query, we have another SQL query in same transaction.

        string strSQL
        = " SELECT"
        + " COUNT(1)"
        + " FROM"
        + " TBL_Parts_In_Txn_Master"
        + " WHERE"
        + "     PITM_Related_Request_No = @MRPR_MR_No"
        + " AND PITM_Continuation_No    = @MRPR_Continuation_No"
        + " AND PITM_Seq_No             = @MRPR_Seq_No"
        + " AND PITM_Parts_Spec_ID      = @MRPR_Return_Faulty_Part_Spec"
        + " AND PITM_Part_Type_ID       = 19"
        + " AND PITM_Exchange_Flag      = 'Y'"
        + " AND PITM_Status_ID          = 25";

    return (Convert.ToInt32(this.GetSingleValue(strSQL, this.CreateParameterArray(objParam), CommandType.Text)) > 0) ? true : false;

If I comment out this code, everything works fine, and the insert query doesn't lock up at all. But, if this query is part of the transaction, then it gets locked in perpetuity.

Below is the screenshot of the activity monitor for this DB.

Activity

Parameters used by select query:

MRPR_MR_No -- M29284100

MRPR_Continuation_No -- 0

MRPR_Seq_No -- 0 (1 at the second run)

MRPR_Return_Faulty_Part_Spec -- ""

Edit:

Well, I spent some 2 days trying to resolve this. But, till now no luck. The only thing I can find is that, if the select query is ran more than once in the transaction, db locks up. Weird thing is, it locks up only if I select the quantity in insert query (PIAD_Qty_Received) as zero.

If the select query is ran only one time, or the quantity selected is not zero there is no lock.

  • can you capture locks while this is running and post it here – TheGameiswar Oct 29 '14 at 9:15
  • Sure. Will do. Give me some time. – jitendragarg Oct 29 '14 at 9:21
  • What is the result of SELECT status, wait_type, blocking_session_id FROM sys.dm_exec_requests; for this session while this query is hung? Running forever - especially for a simple query like this - is usually blocking or something else explainable. – Aaron Bertrand Oct 29 '14 at 17:01
  • Added the output in the main query. @TheGameiswar Sorry, totally forgot about it. Was too busy today with another issue, so didn't checked it. I am unable to capture the locks, as today, this issue is not happening. Today, the query before this one fails randomly. I will keep on trying. As soon as I can replicate it, i will post the details here. BTW, I can run DB reports right now, in case if it is needed. – jitendragarg Oct 30 '14 at 9:14
  • Have you double checked all the data types for your variables used as input values? Maybe an incomaptibility or an implicit convert occurring? – Steve Mangiameli Oct 30 '14 at 18:24
0

Ok, so I finally solved it, thanks to the help of my senior manager. Turns out, he had seen similar problem happening earlier.

Anyway, to solve this, all I had to do was create an index for the select query, using the exact column order as where clause. Apparently, the select query was taking too much time, so the second run of the same query was getting locked. Using view, the first query runs successfully in milliseconds, so everything works fine.

| improve this answer | |

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.