Finance | Project Operations, Human Resources, ...

2 users updating the same record

(0) Share

Report

Posted on by junior AX

1,552

for integrations how can you make sure that 2 out of 5 users won't update the same record

This was an interview question.

I have the same question (0)

All responses (9)

Answers (0)

Bharani Preetham Pe... 3,634 Moderator on at

Like (0)

Report

Hi Junior AX,

For this reason only we have ttsbegin and ttscommit for every transaction which starts and ends a transaction and cannot be duplicated.

Thanks

Bharani

Was this reply helpful? Yes No
junior AX 1,552 on at

Like (0)

Report

Hi baharani,

But ttsbegin and ttscommit won't solve the issue. Because ttsbegin will lock the record and if another user does it at the same time we'll get deadlock? Or no?

Was this reply helpful? Yes No
Bharani Preetham Pe... 3,634 Moderator on at

Like (0)

Report

Hi Junior AX,

Yeah, we should get a update conflict or a deadlock in SQL. We have recversion and if one is working on a record and some other person tried to edit same record for sure he will get update conflict.

Also please check the below link.

cloudblogs.microsoft.com/.../

Thanks

Bharani

Was this reply helpful? Yes No
junior AX 1,552 on at

Like (0)

Report

Hi bahran,

I answered the question by mentioning OCC and PCC and I said in case there was a deadlock we can do a retry.

But why did you say ttsbegin and ttsbegin will avoid duplicates?

And what did the question mean exactly?

Was this reply helpful? Yes No
Bharani Preetham Pe... 3,634 Moderator on at

Like (0)

Report

Hi Junior AX,

Transaction cannot be duplicated. Here what I mean is a transaction on for one rec I'd can be done by a single user. If another user tried to do the same transaction at the same time system considers it as a duplicate transaction based on its rec version. So it gives you update conflict. So only 1 user will be able to update that record and for other users it will give error. This is the reason why I mentioned about ttsbegin and ttscommit as the names suggest that transaction begin and transaction commit.

If in case there is a deadlock, then you need to wait as update is in process by one user and then you need to update that record.

Hope your question is answered.

Thanks

Bharani

Was this reply helpful? Yes No
junior AX 1,552 on at

Like (0)

Report

Hi bahran,

When you say I need to wait then you mean I should use retry right?

And another question..so if I don't use transactions, I can update the same record at the same time with no errors?

Was this reply helpful? Yes No
Suggested answer

Arunraj Rajasekar 1,743 on at

Like (0)

Report

The update statement will throw an error if it is executed without a transaction begin and commit.

Assume both records are part of a large batch query that is waiting to be updated; when the update executes, deadlock occurs because both are already in transaction. To avoid this, all large and complex queries are executed within a try catch. In that case, retry is an option.

Was this reply helpful? Yes No
André Arnaud de Cal... 301,075 Super User 2025 Season 2 on at

Like (0)

Report

Hi Junior AX,

If this was an interview question and you mentioned that you provided an answer, what exactly do you want to get from the community?

My first reply would be another question. What is this integration about and why are users updating records related to the integration? What is the exact scenario?

Was this reply helpful? Yes No
Martin Dráb 237,965 Most Valuable Professional on at

Like (1)

Report

When a resource is locked in transaction A and transaction B waits for the lock to be released, it's a waiting, not a deadlock. A deadlock would accur if transaction B also waited for a resource locked in transaction A, therefore they would be waiting for each other and if the DB server didn't intervene, they would be blocked forever. It's a different scenario.

There is no concept of "duplicated transaction", as far as I know. What Bharani Preetham Peraka describes is a behaviour of a concurrent record update when using optimistic concurrency control. An update and a transaction are two different things - a single transaction can manipulate thousand records in many tables, and you can't say that two transactions are duplicates just because they happen to update a single common record. By the way, RecVersion is indeed a property of record, not transaction.

The primary reason for using transactions is making operations atomic. It means that either the whole operation (e.g. an update of a hundred related records) is performed, or it's not done at all. You won't end up with inconsistent data because the operation failed in the middle.

Was this reply helpful? Yes No