Do transactions solve the saving duplicates problem?

Classic Servoy
1

Hi,

We have a validation method running before saving a record that checks whether a field value does not already exist in the database (say, a job number).
There is a very short time before the check and the save happen but someone else could also run the check for the same value.
In this case, duplicates will be saved to database.
Will databaseManager.startTransaction() and .commitTransaction() solve this problem if we put validation between the start and commit?

Cheers,
Maria

2

If you declare the databrovider of the the field to check UNIQUE and catch an exception
if it is duplicated

3

Maria,

No, this approach will not prevent duplicates.
Transactions are for making changes to the database atomic, 2 reading processes will still go through.

Better to prevent duplicates than check afterwards.
How do you generate the job nr?
Servoy offers multiple options for id generation, all of these guarantee unique values (within the same db).
Uuid generator guarantees unique id accross distributed databases as well.

Rob

4

rgansevles:
Maria,

No, this approach will not prevent duplicates.
Transactions are for making changes to the database atomic, 2 reading processes will still go through.

Better to prevent duplicates than check afterwards.
How do you generate the job nr?
Servoy offers multiple options for id generation, all of these guarantee unique values (within the same db).
Uuid generator guarantees unique id accross distributed databases as well.

Rob

We have a method that generates this kind of codes.
For jobs, it’s a simple counter (thought the user can set the next available number manually in setup).
For other codes, there can be a formula, those are a mix of numbers and characters.

What is this Uuid generator that you mentioned, Rob?

Cheers,
Maria

5

maria:
What is this Uuid generator that you mentioned, Rob?

Maria,

You can define a uuid generator on column in the table editor, a new record will automatically get a new uuid.
The columns should either be a text column (>= 36) or a media column (>=16).

You can also call application.getUUID() and assign the uuid in scripting.

Rob

6

rgansevles:
Uuid generator guarantees unique id accross distributed databases as well.

Rob

hi Rob,

can you elaborate on this? what if other systems generate a backup of insert sql statement and when we ran the rawsql plugin it is still possible to have a duplicate?

7

Rogel,

The rogel uuid is designed to create unique values across unconnected systems, so every time that you create a new uuid, it will be different from all other uuids created so far.
See http://en.wikipedia.org/wiki/Universall … identifier

Rob