We have an import process that reads from a text file (two variations - one CSV, the other XML) into a temp table. Then the temp table is read and we write to TSTimesheetTable, TSTimesheetLine, TSTimesheetLineWeek, and there's call to TSTimesheetTrans::updateFromTimesheetLineWeek. These are inside a tts block, and all the data seems fine.
We then run some validations - emplId valid, employee assigned to project, hours per day, and so on. Valid timesheets are then posted.
Posting is done by updating TSTimesheetTable, setting ApprovalStatus to TsAppStatus::Ready, then calling TSCreateProjJournal::create(TSTimesheetTable).
Occasionally - we're talking months between occurrences - the post fails with an error that there's a deadlock. It has also happened for timesheets that are not imported through this process, which go through workflow, with the same error. The error message is, "Cannot delete a record in Ledger balances - Dimensions (LedgerBalancesTransDelta). Ledger account: , . Deadlock, where one or more users have simultaneously locked the whole table or part of it."
We're running AX 2009, Rollup Update 4. Production environment has three AOS instances. We have not been able to recreate the deadlock situation in our test environment, which is only a single AOS. I did try locking the entire table with a SQL script in SSMS, but that just made the AX job wait; after 7 minutes or so, I continued my script in SSMS, releasing the table lock, and the AX job immediately resumed and completed quickly.
One thing that seems suspicious in the error message is the "Ledger account , ." Almost seems like a strfmt with substitution variables that are blank.
I've followed the call stack in debug, all the way down to LedgerBalancesPostingList class methods and LedgerBalancesTransDelta methods. LedgerBalancesTransDelta (lbtd) is, for the most part, not referred to directly. Posting goes through a RecordSortedList that is setup for lbtd, and there's an insert_database() form the RSL. Then there are insert_recordset calls using the lbtdSum view in a select, into two tables. The only direct reference to lbtd is a delete_from, which clears out lbtd by the UserTTSId. In all methods where I could see it, the ttsId was the same.
Another odd thing I found was, after the RSL.insert_database(), I could not see any rows in SQL Server (SELECT *, no WHERE condition), nor could I see any rows by opening lbtd from the AOT. It's almost behaving like a temporary table, but as far as I can tell, it's not temporary in the properties, and I don't see it being made temporary in code anywhere.
Has anyone else seen anything like this? Any ideas on how I can simulate a lock on lbtd, to be able to recreate the problem in test?
