Changing the same record in DB

[LemonParty]

Hello.

Let's say we have 2 connections to DB. The first user opens table X and changes record Y in it, at the same time another user opens this same table and changes record Y. Then the first user commit transaction and then the second user commit transaction too. There will be an error or maybe exception? What will happen?

[Zvoni]

Hello.

Let's say we have 2 connections to DB. The first user opens table X and changes record Y in it, at the same time another user opens this same table and changes record Y. Then the first user commit transaction and then the second user commit transaction too. There will be an error or maybe exception? What will happen?

This is about Isolation Levels.
Read here: https://en.wikipedia.org/wiki/Isolation_(database_systems)

As to what's happening?
Depends on the Isolation-Level and the DB in use

[egsuh]

Zvoni's answer would be precise, but probably the lastly committed change will remain stored, in general cases.

[Thaddy]

The theory behind it is ACID.
https://en.wikipedia.org/wiki/ACID

Not a long read and a solid explanation.

Most distributed databases have out-of-the-box support for that.

Some databases need something like a trigger, though, keeping track of multiple read access and generating a warning on commit that the record is being edited elsewhere.

I once wrote something like that for Sqlite ( a server-side user defined function that keeps a reference count in a separate table) which lacks that, because Sqlite is by design not a multi-user database.
It may be that mORMot already has something similar available, though?

In its simplest way, egsuh is right: simply all commits are accepted, but that is not how distributed databases usually work, since the commits can be conflicting, so care needs to be taken, otherwise the database may be permanently in a conflicting state for that record and that is not good database management. You need write locks and a trigger, usually this is built-in in some form.

[Zvoni]

As to SQLite:
"Default" is, if you have a "pending" Transaction (Not commited or rolled back), trying to commit (or is it "starting" the Transaction? Don't remember) your second Transaction results in an Exception "Database is locked"

[cdbc]

Hi
With SQLite3:
What I usually do is: keep datasets in memory (like a ClientDataset), e.g.: TBufDataset, maybe then I write a middle-tier that funnels / sequences the changing of data to database one at the time, so _no_ locks...
Additionally one could use a lock-file with the 'tablename-recID' currently being in transaction, so that the user gets a warning, if he tries to change that specific record, this ofc. if we're talking 2 different processes, otherwise one could do it in memory...
Just my 2 cent's worth
Regards Benny

[jcmontherock]

DB contained in one file are not done for multiple, several and simultaneous access to the same data.

[cdbc]

Hi
I agree, it's usually not worth 'the mountain of fiddling' needed, when you've got several good multi-user-databases available...
Regards Benny

[LemonParty]

Thank you for answers. This two articles https://en.wikipedia.org/wiki/Isolation_(database_systems), https://en.wikipedia.org/wiki/ACID have an explanation about such situations.