Concurrent delete operations in X++

Michael Fruergaard ...

1,616

Like ( 3 )

Report

X++ is designed for concurrent work loads - enabling multiple users performing their operations at the same time. Sometimes such operations are in conflict, and the system has to detect, recover and proceed. Data inconsistencies should not be allowed.

Insert

For insert operations consistency is ensure by uniqueness constraints in SQL. If a user tries to insert a record that already exists the transaction will be aborted, and the user will be presented an error message.

Update

Update operations are a trickier. When updating a record, the system will ensure that no one else updated or deleted the record meanwhile. This is implemented using the RecVersion field. When a record is updated, the same record must still exist with the same RecVersion in SQL as the one selected. If not, the transaction is aborted.

Delete

Delete operations are the most tricky. As described above the system will prevent against a simultaneous delete and update operation. However; by default it does not prevent against 2 simultaneous delete operations. After all; if two users both decides to delete the same record simultaneously - why fail one of them?

Here is an overview:

User 1	User 2	Consistency
Insert	Insert	Guaranteed
Update	Update	Guaranteed
Update	Delete	Guaranteed
Delete	Update	Guaranteed
Delete	Delete	Not guaranteed (unless enabled)

If there is no X++ logic in the delete methods on the table (or any of the downstream tables where delete actions are fired) then this is perfectly ok.

But that is not always the case. Consider this example:

public MyLines extends Common
{
str headerId;

public void delete()
{
ttsbegin;

super();

MyHeader header = MyHeader::find(this.headerId, true);
header.numberOfLines--;
header.update();

ttscommit;
}
}

If 2 users attempt to delete the same line at the same time, then both of them will end up decrementing the header, resulting in a header with negative number of lines. The first thread will hold a delete-lock on the lines table blocking the second thread on the super() statement. Once thread 1 commits, the second thread will attempt the same delete (which does nothing), then select the header that is already decremented and decrement it again.

This is the default behavior.

The solution

If you don't like it, do like me and override the new method PU35 method: shouldThrowExceptionOnZeroDelete(). Always. No second thoughts. On new tables, on existing tables, on regular tables, on tmp tables (why not?).

If this method returns true the data base layer will throw an UpdateConflict exception when attempting to delete a record that is no longer there.

Small caution

When blindly making the shouldThrowExceptionOnZeroDelete() return true in our application a few product bugs surfaced. If the same thread attempts to delete the same record twice it will start failing now. A typical product bug where this happens is if a delete action is expressed both declaratively in meta data, and imperatively as code. Still; these bugs are much better (as data remain consistent) than the data inconsistencies that are the alternative.

Why not change the default behavior

The answer is really simple: To stay backwards compatibility. For this one we have an opt-in model, there is no other way while honoring our promise of backwards compatibility.

THIS POST IS PROVIDED AS-IS; AND CONFERS NO RIGHTS

Comments

*This post is locked for comments

Michael Fruergaard ... 1,616
Posted at

It reuses the Exception::UpdateConflict exception - which is also what you get when a delete and fails as someone else has updated the record.

Like (0)

Report
Community Member Microsoft Employee
Posted at

o.. thanks for the explanation. so what exception will be with this flag shouldThrowExceptionOnZeroDelete()? will it cancel the current transaction? or will be similar to UpdateConflict, DuplicateKeyException)?

Like (0)

Report
Michael Fruergaard ... 1,616
Posted at

Denis, both for update and inserts we have exceptions types that can be caught within the transaction, so you can handle the exception without a roll back. The two exceptions are Exception::DuplicateKeyException and Exception::UpdateConflict. Learn more here: docs.microsoft.com/.../xpp-exceptions Now behavior is aligned across the 3 DB operation types. (If you opt-in for deletes). If you want an uncatchable exception for your table to ensure a rollback always happens, that is easy to implement already. Just wrap that the super() statement in a try catch block, and if you get one of the 2 exceptions, then rethrow an unrecoverable exception.

Like (0)

Report
Community Member Microsoft Employee
Posted at

update and insert are different - for update() - the exception always rollbacks the current transaction for insert() - it depends - see my job, for example, the current transaction is not cancelled. The current transaction cancellation can be avoided with catch(Exception::DuplicateKeyException) So basically my proposal to add the similar flag - if it is set - exception on insert() should always rollback the current transaction

Like (0)

Report
Michael Fruergaard ... 1,616
Posted at

I'm not sure I follow you. Insert already throws an exception that will cancel the current transaction (unless you catch it). So for insert and update you are already in full control unless I'm mistaken.

Like (0)

Report
Community Member Microsoft Employee
Posted at

Yes, my example was about that the exception on insert does not cancel the current transaction(so if you have some code on insert it may be executed and committed even if insert failed - exactly the same situation as you describe with delete). As you decided to introduce a flag for delete - and that is a great decision, maybe to introduce the same flag for insert - that will produce an exception that cancels the current transaction?

Like (0)

Report
Michael Fruergaard ... 1,616
Posted at

Hi Denis, I can promise you that it is not possible to insert 2 records with the same primary key in the data. You will get an exception, which you can catch and handle if you know what you are doing, but you will never end up with 2 identical records in SQL. The same is true for deletes if you opt-in for this new behavior. You can catch the exception and handle it, for example in your batch job. This is why it is an opt-in, enabling it as the default would be breaking.

Like (0)

Report
Community Member Microsoft Employee
Posted at

But what is the reason to generate an exception if you can avoid this? For example, I run some batch job that deletes records and then a user deleted one record manually. There is no point to generate an error here. But I like the current design - if you want you can add this flag. By the way - can you add the same property for "insert" (shouldThrowExceptionOnDuplicateException)? I think there is an error in your table - the first line "Insert Insert = Guaranteed" but it is not always true Consider the following job - duplicate insert doesn't cancel a whole transaction, so you will get exactly the same problem. at the end of this job "after insert" will be printed static void Job35(Args _args) { CustGroup custGroup; ; ttsBegin; custGroup.CustGroup = "test1"; custGroup.insert(); try { custGroup.CustGroup = "test1"; //some business logic here custGroup.insert(); } catch(Exception::DuplicateKeyException) { } info("after insert"); ttsCommit; }

Like (0)

Report
Michael Fruergaard ... 1,616
Posted at

Thanks for the comment Denis. For me it is simple: Why would you not want an exception when a delete operation fails to delete a record? You are right there are many ways to write idempotent logic to be robust in these scenario - sometimes that is doable, sometimes it incurs a performance overhead, sometimes an extender added logic that is outside your control. I sleep better not having to worry about this type of race condition.

Like (0)

Report
Community Member Microsoft Employee
Posted at

I don't get your advice(always specify shouldThrowExceptionOnZeroDelete()). The code on delete may not be called when the record is deleted - this is by design as you always have doDelete() method. So instead of doing " header.numberOfLines--; " you should run lines recalculate method and this will resolve this problem.

Like (0)

Report