Since I have no unique index on a table I need to find an other way to asure that no duplicate records are created.

For my customer it's ok that only one user at a time creates a new record. Therefore I used a AdsLockRecord(1) to always lock the first record in the table. So only one is possbile to perform the following insert. Now I was told that no one would be able to do an insert because it was no longer possible to lock record no. 1. I adviced to shut down and restart ADS server, so all locks should be gone.

  1. How long does a AdsLockRecord last? Until AdsUnlockRecord, until closing the table or the complete connection? What happens when killing the application via task manager or switching off PC without shutdown?
  2. Is there a way to kill all locks on a table via client so no shutdown and restart of server is neccessary in that case?
  3. Is there a better way than locking record no. 1? AFAIK a table.insert doesn't block a concurrent insert. Or can I do this by AdsLockRecord(RecCount() +1)? Does this lock behave different from AdsLockRecord(1)?

TIA \/olker

asked 31 Oct '13, 02:06

Volker%20Sengler's gravatar image

Volker Sengler
266171727
accept rate: 0%


a lock lasts until you perform an unlock on that record or the connection gets closed. When using the TDataset components, moving away from that record will also unlock it. When in a transaction, a lock is kept until the transaction is finished (commited or rolled back). You can't kill a lock, but you can kill the connection that holds the lock.
In your case I would write a trigger and throw an error if a duplicate record is entered.

create trigger trig_ins on mytable instead of insert
begin
  if exists (select * from mytable where /your unique condition/ ) then
    raise my_exception(-1,'duplicate record');
  end;
  insert into mytable select * from __new;
end;

link

answered 31 Oct '13, 02:29

Joachim%20Duerr's gravatar image

Joachim Duerr
8.7k1031133
accept rate: 18%

Thanks for your answer!

The promblem is that a user may have entered much data before saving. So, the exception must not lead to abandon the data. I have to if my code can handle this.

Just to be sure I got it right: when using TDataSet component a "close" doesn't unlock the record? And moving from a record only unlocks the actual record, right? So I have to asure that the connection (not the connection component) is closed? Is it sufficient to cancel the connection by server tools?

link

answered 31 Oct '13, 08:03

Volker%20Sengler's gravatar image

Volker Sengler
266171727
accept rate: 0%

1

a close on a table/query unlocks all records locked by that component. If you close the connection component that is bound to the table/query, then the table/query gets closed and disconnected aswell.

(31 Oct '13, 08:42) Joachim Duerr
Your answer to the original question.
If responding to a request for additional information, please edit the question or use the comment functionality.
toggle preview

Follow this question

By Email:

Once you sign in you will be able to subscribe for any updates here

By RSS:

Answers

Answers and Comments

Markdown Basics

  • *italic* or _italic_
  • **bold** or __bold__
  • link:[text](http://url.com/ "Title")
  • image?![alt text](/path/img.jpg "Title")
  • numbered list: 1. Foo 2. Bar
  • to add a line break simply add two spaces to where you would like the new line to be.
  • basic HTML tags are also supported

Tags:

×271
×154

Asked: 31 Oct '13, 02:06

Seen: 12,384 times

Last updated: 31 Oct '13, 08:42

Advantage Developer Zone Contact Us Privacy Policy Copyright Info


Powered by Advantage Database Server and OSQA
Disclaimer: Opinions expressed here are those of the poster and do not necessarily reflect the views of the company.