Icon View Thread

The following is the text of the current message along with any replies.
Messages 1 to 5 of 5 total
Thread Lock table
Fri, May 29 2015 12:14 PMPermanent Link

Mirco Malagoli

Hi, I need to find the MAX value of a column in a table and then insert a new record in the same table with the value incremented by 1.
In a multi user environment i need the assurance that the value is not used two times.
I have tried with transaction but do not block the read of other session.
In a MySQL sample I can use the LOCK TABLE but I have not found an DBISAM alternative

Thanks for the suggestion
Fri, May 29 2015 12:24 PMPermanent Link

Raul

Team Elevate Team Elevate

On 5/29/2015 12:14 PM, Mirco Malagoli wrote:
> Hi, I need to find the MAX value of a column in a table and then insert a new record in the same table with the value incremented by 1.
> In a multi user environment i need the assurance that the value is not used two times.
> I have tried with transaction but do not block the read of other session.
> In a MySQL sample I can use the LOCK TABLE but I have not found an DBISAM alternative

There is the "LockTable" method of the dbisam table that should do just
what you need.

(http://www.elevatesoft.com/manual?action=viewmethod&id=dbisam4&product=rsdelphiwin32&version=XE7&comp=TDBISAMTable&method=LockTable)

Alternative is to just use a unique index on the column to ensure unique
value. Your code simply has to deal with the insert failing (key
violation) and if your ID is simply an increment then you can likely
just increment and commit again (instead of re-querying for max value).

Raul
Sun, May 31 2015 1:56 AMPermanent Link

Mirco Malagoli

Thanks,
I have not tried but the documentation says that the table is not locked in reading and two clients can then read the same value

Raul wrote:

On 5/29/2015 12:14 PM, Mirco Malagoli wrote:
> Hi, I need to find the MAX value of a column in a table and then insert a new record in the same table with the value incremented by 1.
> In a multi user environment i need the assurance that the value is not used two times.
> I have tried with transaction but do not block the read of other session.
> In a MySQL sample I can use the LOCK TABLE but I have not found an DBISAM alternative

There is the "LockTable" method of the dbisam table that should do just
what you need.

(http://www.elevatesoft.com/manual?action=viewmethod&id=dbisam4&product=rsdelphiwin32&version=XE7&comp=TDBISAMTable&method=LockTable)

Alternative is to just use a unique index on the column to ensure unique
value. Your code simply has to deal with the insert failing (key
violation) and if your ID is simply an increment then you can likely
just increment and commit again (instead of re-querying for max value).

Raul
Sun, May 31 2015 2:11 AMPermanent Link

Roy Lambert

NLH Associates

Team Elevate Team Elevate

Mirco


To the best of my knowledge there are two ways of doing waht you want. The first is the one you're following which is to ensure uniqueness at the start, the second is to react to a non unique situation and rectify it.

Unless you are concerned about the order in which the records are created rather than their uniqueness why not try the second approach? Create a unique index on the column and react when its violated.

99% of the time the uniqueness criteria will be fine and for the odd 1% you simply retry.

Roy Lambert
Mon, Jun 1 2015 2:16 AMPermanent Link

Mirco Malagoli

Thanks Roy
the only reason is i don't like the way to try and see what happens but i have understand there is no many other ways to do this


Roy Lambert wrote:

Mirco


To the best of my knowledge there are two ways of doing waht you want. The first is the one you're following which is to ensure uniqueness at the start, the second is to react to a non unique situation and rectify it.

Unless you are concerned about the order in which the records are created rather than their uniqueness why not try the second approach? Create a unique index on the column and react when its violated.

99% of the time the uniqueness criteria will be fine and for the odd 1% you simply retry.

Roy Lambert
Image