When there are multiple sales orders, I am going to combine the packing list, and then print the ssrs on the screen according to each different sales order number, but I found that when the number of the combined sales orders is too much, the database will deadlock
The following is the main method code in the controller:
DeliveryCreditNoteController controller = new DeliveryCreditNoteController();
SRSPrintDestinationSettings settings;
CustPackingSlipJour custPackingSlipJour = _args.record();
CustPackingSlipTrans custPackingSlipTrans;
str printTime = strReplace(DateTimeUtil::toFormattedStr(DateTimeUtil::applyTimeZoneOffset(DateTimeUtil::utcNow(), DateTimeUtil::getUserPreferredTimeZone()),
321,
DateDay::Digits2,
DateSeparator::None,
DateDay::Digits2,
DateSeparator::None,
DateYear::Digits4,
TimeSeparator::Space,
TimeSeparator::Colon)," ","");
Set salesIdSet = new Set(Types::String);
SetEnumerator enumerator;
SalesId parmSalesId;
CustPackingSlipTrans custPackingSlipTransTmp;
while select OrigSalesId
from custPackingSlipTransTmp
group by OrigSalesId
where custPackingSlipTransTmp.SalesId == custPackingSlipJour.SalesId
&& custPackingSlipTransTmp.PackingSlipId == custPackingSlipJour.PackingSlipId
&& custPackingSlipTransTmp.DeliveryDate == custPackingSlipJour.DeliveryDate
{
salesIdSet.add(custPackingSlipTransTmp.OrigSalesId);
}
enumerator = salesIdSet.getEnumerator();
while (enumerator.moveNext())
{
parmSalesId = enumerator.current();
controller.parmTableRecId(custPackingSlipJour.RecId);
controller.parmSalesId(parmSalesId); controller.parmReportName(ssrsReportStr(DeliveryCreditNoteReport, Report));
controller.parmShowDialog(false);
settings = controller.parmReportContract().parmPrintSettings();
settings.printMediumType(SRSPrintMediumType::Screen);
controller.startOperation();
}
What are the dedlocked queries and locked resources?
Before looking at your code, let me format it and simplify it a bit:
utcDateTime timeInUserTz = DateTimeUtil::applyTimeZoneOffset(DateTimeUtil::utcNow(), DateTimeUtil::getUserPreferredTimeZone());
// TODO: Review the format
str printTime = System.String::Format('{0:yyyyMMdd HH:mm}', timeInUserTz);
CustPackingSlipJour custPackingSlipJour = _args.record();
CustPackingSlipTrans custPackingSlipTransTmp;
Set salesIdSet = new Set(Types::String);
while select OrigSalesId from custPackingSlipTransTmp
group by OrigSalesId
where custPackingSlipTransTmp.SalesId == custPackingSlipJour.SalesId
&& custPackingSlipTransTmp.PackingSlipId == custPackingSlipJour.PackingSlipId
&& custPackingSlipTransTmp.DeliveryDate == custPackingSlipJour.DeliveryDate
{
salesIdSet.add(custPackingSlipTransTmp.OrigSalesId);
}
SetEnumerator enumerator = salesIdSet.getEnumerator();
while (enumerator.moveNext())
{
SalesId parmSalesId = enumerator.current();
DeliveryCreditNoteController controller = new DeliveryCreditNoteController();
controller.parmTableRecId(custPackingSlipJour.RecId);
controller.parmSalesId(parmSalesId);
controller.parmReportName(ssrsReportStr(DeliveryCreditNoteReport, Report));
controller.parmShowDialog(false);
SRSPrintDestinationSettings settings = controller.parmReportContract().parmPrintSettings();
settings.printMediumType(SRSPrintMediumType::Screen);
controller.startOperation();
}
What is the execution mode of the controller? If it’s the default of the framework, i.e. ReliableAsynchronous, it means that the reports may be executed in parallel. Is it your intention?
Thanks for the simplification of the code,I didn’t set the controller execution method because I don’t know how to set it
The statement in the lock details viewed in the database is as follows:
SELECT SUM(T1.QTY),AVG(T1.QTYMULTIPLES),T1.INVENTTRANSID,T1.LINENUM,T1.INVENTLOCATIONID,T1.WAREHOUSENAME,T1.WAREHOUSEPHONE,T1.WAREHOUSETELEFAX,T1.WAREHOUSEADDRESS,T1.WAREHOUSECONTACTPERSONNAME,T1.ITEMID,T1.ITEMNAMEALIAS,T1.ITEMNAME,T1.INVENTBATCHID,T1.WMSLOCATIONID,T1.SPECFICATION,T1.INVENTBATCHEXPDATE,T1.STORAGECONDITION,T1. SALESTYPE
FROM tempdb."DBO". t24180IISJ21310092_497EDCA0F9C8421B8C4131E3C0F76853 T1
WHERE ((PARTITION=5637144576) AND (DATAAREAID=N'aj'))
GROUP BY T1.INVENTTRANSID,T1.LINENUM,T1.INVENTLOCATIONID,T1.WAREHOUSENAME,T1.WAREHOUSEPHONE,T1.WAREHOUSETELEFAX,T1.WAREHOUSEADDRESS,T1.WAREHOUSECONTACTPERSONNAME,T1.ITEMID,T1.ITEMNAMEALIAS,T1.ITEMNAME,T1.INVENTBATCHID,T1.WMSLOCATIONID,T1.SPECFICATION,T1.INVENTBATCHEXPDATE,T1.STORAGECONDITION,T1. SALESTYPE
ORDER BY T1. LINENUM
A deadlock means that several statements block each other. Knowing just one of them doesn’t tell you enough about the deadlock. Also, pay attention to which resource are locked and by which lock type.
Or may be you actually mean a lock and waiting instead of a deadlock.
Could you also answer my question whether you want to run the reports in parallel or not?
Sorry, I don’t understand what it means to run reports in parallel
After running, my database has the process shown in the figure below:
Let’s say you have two IDs in salesIdSet
. With your current code, two reports may be executed at the same time, one for each SalesId. If they need to lock the same respources, then can block each other and potentially even cause a deadlock (which means that the database server will terminate of the transactions).
Maybe you did it by mistake and you actually want to run one by one. That’s the question for you.
What can I do to fix this problem? Running reports in parallel?
First of all, you need to decide what you want to achieve. Then if you want help from others, share this information with.
We can’t tell you how to fix it if you don’t tell is what you want.
I want to print multiple ssrs on the screen, the problem now is that the database deadlocks when printing more than a certain number of ssrs
Let me ask you one more time. Do you want to run them in parallel or not?
Then do it. It’s likely that your problem will disappear if you run the reports one by one. Try adding this call before calling startOperation():
controller.parmExecutionMode(SysOperationExecutionMode::Synchronous);
Thank you very much for your patient answer and the solution you gave, which successfully solved my problem
Sorry, I have one more question and need your help, after the ssrs are printed on the screen, I save as PDF through the export in the upper left corner, I find that every file name is repeated, I want to use the current printed ssrs The parmSalesId+printTime to form the file name of the pdf, I use the following code: controller.parmDialogCaption(parmSalesId+'_'+printTime);
but it didn’t work
The question about the file name doesn’t belong to this thread about the deadlock, but you can easily create a new thread (with the right name and tags).
Also, don’t forget to check previous discussions. For example, look at Change file name of SSRS when export to PDF using X++, although I’m not sure that it still applies to the current application version.
ok i will follow your advice
When I compile the current model in VS and run the program, I find that the problem of deadlock reappears. It’s really strange. I don’t know if there are other solutions?
@MartinDrab Could you please pay attention to this issue again.