Db concurrency: field vs UPDLOCK
I'm developing an Enterprise resource planning in Omnis using SQL Server.
Multiple users will read-update-delete ecords, so i have to deal with concurrency.
I see two possible solution. And i already know how to deal with both. I'm just asking which is better for you.
AUTOMATIC SOLUTION Setting the transaction mode = Automatic, so the BEGIN-COMMIT-ROLLBACK transaction are automatically done by the software, but no concurrency control at all. I could use a field on every table that contains the name of the user blocking the record, if is empty the user can update the record and i store the user name in the filed, if the field is NOT empty i will display the user blocking the field.
MANUAL SOLUTION Setting the transaction mode = Manual, so i have to deal with BEGIN-COMMIT-ROLLBACK, but i can use things like UPDLOCK with ROWLOCK.
Do you see the using of a field instead of UPDLOCK with ROWLOCK as a bad-poor practice? There are other pro and cons?
It is not field vs UPDLOCK. The question should be what business logic is and which way you are going to make it.
Which concurrent problems do you have there exactly and how they should be solved?
What your code should be doing if there is two or more concurrent:
- inserts of the same data
- updates on the same row
- delete and update of the same row
- mb more here
Then your answer is in the following questions: Do AUTOMATIC SOLUTION gonna do what you need here or no? Or you have to implement manual control?