MSSQL Update Freezes

[bazzer747 25]

Hi

I have an MSSQL update I run which looks like this:

 dm.fdc.ExecSQL(   'UPDATE tPGCMatches SET FirstTeeTime = :p1T, Interval = :pIn, SecondTeeStart = :p2, SecondTeeTime = :p2T WHERE ID = :pMI' ,[ c1stTeeStartTime, cInterval, c2ndTeeHole, c2ndTeeStartTime, gviMatchID ]);

 

I have a similar command in a different project which uses the query ExecSQL to do the update (rather than the connection) but to a different named (but structurally the same) table. I've tried using the query ExecSQL as well, I usually now prefer to use the connection for these types of SQL statements.

 

I do many updates elsewhere in my project which work fine, but this one freezes when attempting to execute. I've run the FireDac Monitor but on the line execution nothing new appears to indicate if the command is being processed. I've checked the parameters are all OK, and run the update in Management Studio (entering values instead of parameters) and it runs OK there, so the table seems OK. 

 

It's as though it's getting stuck formulating the sql command to send to MSSQL but I don't know a way to trace what is happening to make it freeze, and the format of the command looks OK.

 

Any suggestions/possible answers would be appreciated.

[Lars Fosdal 1791]

Do you get an error if you allow it to time out when it hangs?

 

When SQL just hangs, the cause can be a deadlock.  That means that the table you are trying to update, is busy somehow. The default timeout is typically 30 seconds before a thread is declared the winner and the other threads accessing the table gets a lock error and have to do a retry.

 

This is the reason we in our team do our MSSQL table updates through stored procedures.  That allows the procedure to do a try/catch and retry if it fails due to a deadlock.

 

Preventing deadlocks:

It is important to avoid "lingering" cursors to reduce the risk of deadlocks. 

F.x. If you really need a cursor, do a select into a memory temp table, and create the cursor on that temp table.

If a complex select or view can safely use "With NoLock" on a table, use it.

 

[bazzer747 25]

Hi Lars,

Thanks for answering.

I'm pretty sure the table isn't busy. I'm the only one accessing the table (as it's not the Live Server I'm using, also this is a test system with only myself accessing any of the tables). The 'freeze' lasts forever, not for a short period. It does appear that the table is locked, but you say it would either give up or win when the lock opens.  I always have to use Task Manager to kill the app eventually.

I've just run it again and it's now been over 5 minutes and it's still frozen. I'm not sure I can look anywhere to see why it is hanging. There is no error message.

 

I'm going to create a new table (same structure) and see if it happens on this one. I've tried several different Updates using different SET field value changes, to see if it was a field that is the problem, but they all fail the same way.

[Lars Fosdal 1791]

SQL Server has pretty good diagnostics and logging, so if a query causes it to hang, it should be detectable.

 

I would suggest trying a minimal example with the explicit query through FireDAC, and compare that with the parameterized query.

Check parameter by parameter that the value type is correct.  That the format is correct (number, date, etc).

[bazzer747 25]

Lars,

 

Many thanks for the idea. I've done some of this already but will persist.

 

On the SQL diagnostics, my feeling is that the Update command isn't actually getting to the SQL Server. I would have thought I would see the command going to SQL Server in the FireDac Monitor which, as I mentioned above, does nothing when I run the specific FireDac ExecSQL line.

 

I'm definitely going to create a new small project with just this process in it, with (as you suggest) minimal fields fully checked.

[Lars Fosdal 1791]

It just dawned on me that you wrote the Monitor didn't produce any logging for that query. That strongly suggests that it fails in the preprocessing. 

What type are the parameters, and can there be invalid data passed to the query? Garbage strings, NaNs, invalid pointers, etc? 

[TigerLilly 16]

I´d have alook at the trace that is created on the server side, to have a clear diagnostics where to look further. If the statement does not reach the server, the client is the way to go.

https://docs.microsoft.com/en-us/sql/tools/sql-server-profiler/create-a-trace-sql-server-profiler?view=sql-server-ver15

BTW: Same issue on another machine? Same issue when executed locally? Same issue when switching protocols?

[bazzer747 25]

Lars,

Yes, I agree, it's not getting past pre-processing. I've double checked the values in the parameters and they all have valid values.

 

I ran the update using just one of the parameters at a time, thinking it was one of them that was the problem, but whichever parameter was used gave the same problem.

To TigerLilly, thanks for your input. I get the same behaviour from another PC, and the same when the update is going to my Live SQL Server (as well as my Test SQL Server). So all this (to me) points to the command never leaving the application. I'll look at that trace info for SQL, thanks, even if the Monitor isn't sending anything there - it will at lkeast prove nothing is getting there.

 

Just in case it is I'm recreating the table to do some more testing, and from a new (small) application! I'll beat this problem to death … eventually!

 

[bazzer747 25]

An update …. although I can't explain fully why, but I now have the code updating!

 

Although thereis only one SQL update in the process I put a TRY EXCEPT construct around the code and with a Start Transaction, so the code block now looks like this:

 

TRY
dm.fdc.StartTransaction;
    dm.fdc.ExecSQL(
        'UPDATE tPGCMatches SET FirstTeeTime = :p1T, Interval = :pIn, SecondTeeStart = :p2, SecondTeeTime = :p2T WHERE ID = :pMI' ,[ c1stTeeStartTime, cInterval, c2ndTeeHole, c2ndTeeStartTime, gviMatchID ]);
      end;
    dm.fdc.Commit;
EXCEPT
on E : Exception do  ShowMessage(E.ClassName+' error raised, with message : '+E.Message);
END;

 

Lo and behold the update went through. Note I haven't changed the SQL update command in any way, or any of the variable values /parameters. So, like many things in Delphi, it is a mystery.

 

I normally use the Start  Transaction construct where I am doing several SQL Updates &/or Inserts to different tables, so wouldn't normally need one around a single SQL command. The problem I have (now) with the above code id I normally have a Rollback if the transaction fails, but I'm unsure where I can put this, or even if it is needed as an error presumably won't do the Commit and will go to the EXCEPT. My lack of knowledge shows here.

[Attila Kovacs 629]

you have to end the transaction either with commit or with rollback, so rollback comes into the except block

[Attila Kovacs 629]

http://docwiki.embarcadero.com/RADStudio/Rio/en/Managing_Transactions_(FireDAC)

 

btw, what is the connection's transactioncount in the original "defect" code right before the update?

Edited June 16, 2020 by Attila Kovacs

[bazzer747 25]

How do I find out this 'transactioncount'?

[Guest]

Some two, tree times a year i get an "Logical transaction already started" or some such from my DAC (not FireDAC).

IMHO if you do not start and end transaction properly you should get an exception from the DAC, so i would file a QC for FireDAC.