Solved

Getting PXData.PXLockViolationException when deleting records from table on every other attempt

  • 25 March 2022
  • 9 replies
  • 307 views

Userlevel 6
Badge +3

Overview: I have a custom table to display info to a grid.  When the user clicks a button on the form, I call two methods.  One to delete the records matching the filter and then re-add the data back into the table using the same filter values.  The data in the table cannot be “selected” using a normal select statement as I have to parse fields from the AuditHistory table (ModifiedFields contains null delimited field/value pairs).  I am “de-normalizing” the data so there is one record in the custom table for each modified field in the ModifiedFields column.  This is what the customer has requested.

The form works as expected except that the DeleteData method only works EVERY OTHER TIME!  

Here is the sequence of events:

  1. open form.  At this point there is no data in the table.
  2. Click Get New Data
  3. The DeleteData fires and nothing is deleted (obviously...there is no data in the table matching the filter yet)
  4. Data is added to the table as desired using the filter field values.
  5. The user clicks the Get New Data button and when the DeleteData is fired, there is a locking violation thrown.  No data gets deleted and since an error is thrown, the AddData method doesn’t get called (which is good because it would just try to add duplicate data which would also throw errors as keys would be violated)
  6. The user clicks the Get New Data button and the data IS deleted and re-added.
  7. The user clicks the Get New Data button and the error in item 5 occurs.

In summary, every other time you click the Get New Data button it works fine.

 

I will paste the entire graph here in case anyone sees anything I can do to fix this.

using PX.Data;
using PX.Data.BQL;
using PX.Data.BQL.Fluent;
using PX.SM;
using QTCAuditReporting.DAC;
using System;
using System.Collections;

namespace QTCAuditReporting
{
public class QTCAuditReport : PXGraph<QTCAuditReport>
{

private Guid? _currentUser;

public QTCAuditReport()
{
_currentUser = CommonServiceLocator.ServiceLocator.Current.GetInstance<ICurrentUserInformationProvider>().GetUserId();
}

[PXHidden]
public class QTCAuditHistoryReportFilter : IBqlTable
{
#region ScreenID
[PXString(8, IsFixed = true, InputMask = "")]
[PXDefault]
[PXUIField(DisplayName = "Screen ID", Required = true)]
[PXSelector(typeof(Search5<AUAuditTable.screenID,
InnerJoin<SiteMap, On<SiteMap.screenID, Equal<AUAuditTable.screenID>>>,
Where<AUAuditTable.isActive, Equal<True>>,
Aggregate<GroupBy<AUAuditTable.screenID>>>),
typeof(AUAuditTable.screenID),
typeof(SiteMap.title),
DescriptionField = typeof(SiteMap.title))]
public virtual string ScreenID { get; set; }
public abstract class screenID : PX.Data.BQL.BqlString.Field<screenID> { }
#endregion

#region ChangeDate
[PXDate]
[PXUIField(DisplayName = "Change Date", Required = true)]
[PXDefault]
public virtual DateTime? ChangeDate { get; set; }
public abstract class changeDate : PX.Data.BQL.BqlDateTime.Field<changeDate> { }
#endregion
}

public PXFilter<QTCAuditHistoryReportFilter> Filter;

[PXFilterable]
public SelectFrom<QTCAuditHistoryReport>.Where<QTCAuditHistoryReport.screenID.IsEqual<QTCAuditHistoryReportFilter.screenID.FromCurrent>.
And<QTCAuditHistoryReport.changeDate.IsEqual<QTCAuditHistoryReportFilter.changeDate.FromCurrent>.
And<QTCAuditHistoryReport.reportingUserID.IsEqual<AccessInfo.userID.FromCurrent>>>>.View.ReadOnly DetailsView;

public PXSelect<QTCAuditHistoryReport> ItemsToAdd;

#region "Actions"
public PXAction<QTCAuditHistoryReportFilter> GetReportData;
[PXButton(CommitChanges = true)]
[PXUIField(DisplayName = "Get Report Data", Enabled = false)]
protected void getReportData()
{

if(Filter.Current.ScreenID is null || Filter.Current.ChangeDate is null) return;

if (Filter.Current.ScreenID.Equals(string.Empty) || Filter.Current.ChangeDate.Equals(string.Empty)) return;

DeleteData();

GetNewDataset();
}
#endregion

protected void DeleteData()
{

using (var ts = new PXTransactionScope())
{
foreach (QTCAuditHistoryReport item in DetailsView.Select())
{
var result = DetailsView.Delete(item);
}
var result2 = DetailsView.Cache.Persist(PXDBOperation.Delete);
ts.Complete(this);
}
}

protected void GetNewDataset()
{

DateTime startDate = (DateTime)Filter.Current.ChangeDate;
DateTime endDate = (DateTime)Filter.Current.ChangeDate;

startDate = startDate.Date;
endDate = endDate.Date;

startDate = startDate.AddDays(-1);
endDate = endDate.AddDays(1);

ItemsToAdd.Cache.Clear();

foreach (AuditHistory item in SelectFrom<AuditHistory>.
Where<AuditHistory.screenID.IsEqual<@P.AsString>.
And<AuditHistory.changeDate.IsBetween<@P.AsDateTime, @P.AsDateTime>>>
.View.Select(this, Filter.Current.ScreenID, startDate, endDate))
{
var dateTemp = (DateTime)item.ChangeDate;
var workingDate = dateTemp.Date;

QTCAuditHistoryReport newRow = new QTCAuditHistoryReport
{
BatchID = item.BatchID,
ChangeDate = workingDate,
ChangeID = item.ChangeID,
CombinedKey = item.CombinedKey,
Email = "",
FirstName = "",
LastName = "",
ModifiedFieldName = "",
ModifiedFieldValue = "",
ScreenID = item.ScreenID,
TableName = item.TableName,
Operation = item.Operation,
Userid = item.UserID,
ReportingUserID = _currentUser
};

ItemsToAdd.Insert(newRow);
}

ItemsToAdd.Cache.Persist(PXDBOperation.Insert);
ItemsToAdd.Cache.Clear();
DetailsView.View.RequestRefresh();
}
}

I haven’t even gotten to the parsing coding yet.  I’m just getting the framework for the process working.

Sorry for posting so many help requests lately.  I’m a noob and I am under the gun to get this out to the client.  

icon

Best answer by Dmitrii Naumov 25 March 2022, 20:29

View original

9 replies

Userlevel 7
Badge +5

Out of curiosity, what happens if you close the screen and re-open it before clicking the Get New Data button?

Userlevel 7
Badge +5

Is there a reason you use Cache.Persist instead of Save.Press() ? 

Cache.Persist will not trigger all the required events, e.g. RowPersisted. 

 

You should probably also set ‘RecordComesFirst’ in the PXDBTimestamp attribute for QTCAuditHistoryReport (if you even need a timestamp field for such a strange scenario)

Userlevel 6
Badge +3

 

@ddunn 

Awesome idea.  I tried your suggestion and I get no errors when I go to a different form, then return back and refresh the data.

  1. Open form.  At this point there is no data in the table.
  2. Click Get New Data
  3. The DeleteData fires and nothing is deleted (obviously...there is no data in the table matching the filter yet)
  4. Data is added to the table as desired using the filter field values.
  5. Close the form (go to a different one) and return to it
  6. Click the Get New Data button
  7. Data gets deleted and re-added with no errors

 

Userlevel 6
Badge +3

@Dmitrii Naumov

This is an inquiry only form (other than the button to refresh the table).  There is no Save button on the form.  

Edit:

OMG.  I added the Save.Press() and no errors any more.  I don’t have the public PXSave<TABLE> Save; in the graph, but it still works.  

Userlevel 7
Badge +5

@joe21  You can still use something like PressSave() or just graph.Persist(). Using persist on cache is very low level thing and should not be really used in application code if you are not going to also manually trigger all other machinery like RowPersisted events, working with timestamps etc.

Userlevel 5
Badge +1

Not sure if this works for what you are trying to do, if you only need to purge records why not PXDatabase.Delete<QTCAuditHistoryReport>(new PXDataFieldRestrict<QTCAuditHistoryReport.changeID>)(item.ChangeID));

instead of the DetailsView.Cache.Delete(item). This will purge the records out and not mess with the cache at all.

You could then invalidate the cache to force it to refresh.

Userlevel 6
Badge +3

@Shawn Burt  I’ve never used that one.  I need to delete based on 3 field parameters.  Perhaps I can use your code and add in all 3 parameters.  

I’ll give it a try!

Userlevel 5
Badge +1

yes, I gave a sample based on guessing your key. If you don’t have a dedicate key to use then the more fields the better to narrow down the selection when performing the delete.

Let me know if I can be of any more help.

Userlevel 6
Badge +3

@Shawn Burt 

Worked like a charm.

            PXDatabase.Delete<QTCAuditHistoryReport>(new PXDataFieldRestrict<QTCAuditHistoryReport.reportingUserID>(_currentUser),
                new PXDataFieldRestrict<QTCAuditHistoryReport.screenID>(Filter.Current.ScreenID),
                new PXDataFieldRestrict<QTCAuditHistoryReport.changeDate>(Filter.Current.ChangeDate));
 

Reply


About Acumatica ERP system
Acumatica Cloud ERP provides the best business management solution for transforming your company to thrive in the new digital economy. Built on a future-proof platform with open architecture for rapid integrations, scalability, and ease of use, Acumatica delivers unparalleled value to small and midmarket organizations. Connected Business. Delivered.
© 2008 — 2024  Acumatica, Inc. All rights reserved