Very Computer

Hai there,

I have this problem (It has to work at financial companies, anyway):

Multi user system:

Two users work with the same customer's data. Both users want to ADD a record
(let's say data of a contact person for the current customer) at the same time.

I want to prevent this. The customer shouldn't get two records with the same
contact person.

Isn't this one of the basics of transactions?
I can't get it done with the BeginTrans, CommitTrans and Rollback methods.

Anyone got an idea?

Thanx,
Octavie

Add a "locked" flag to the table and when someone requests a row or rows,
update the locked flag to true.  Never allow a user to select a row that is
locked.  When they update or release the data set the flag to false.

Another method is timestampting it.  Add a timestamp field and retrieve it
when you retrieve the data.  Before you save the data check that the
timestamp of the data is the same as what it was when you retrieved it.  If
it is, great.  If not then warn the user that someone has updated the data
since they retrieved it and give them the option of abandoning the save or
overwriting the changes.

Hai there,

I have this problem (It has to work at financial companies, anyway):

Multi user system:

Two users work with the same customer's data. Both users want to ADD a
record
(let's say data of a contact person for the current customer) at the same
time.

I want to prevent this. The customer shouldn't get two records with the same
contact person.

Isn't this one of the basics of transactions?
I can't get it done with the BeginTrans, CommitTrans and Rollback methods.

Anyone got an idea?

Thanx,
Octavie

Updating is not the problem, I've got that one covered (timestamp field).

ADDING records is my problem. Two users adding the same record at the same time!

For example,

Two bank employees are updating your account balance. They both start a
transaction. They both add a (transaction)record with the amount of $1000,- to
your account. How does the system know, which addition is allowed and which one
isn't?? However, I think you don't mind getting $2000,-!!  ;-)

Octavie

My question is how come that two bank employees do the same operation for
the same customer in the first place?
It does not seem possible.
I do not think that it is possible to control such a thing. How should the
application know whether there is a duplicate
operation over the same amount of money, or it is indeed an attempt to
deposit $1000 twice (a total of $2000) by free choice?
Can you elaborate on the matter?

Hello Octavie.

Maybe I missed something here, but couldn't you use a primary key to
accomplish this task?

--
HTH

Craig Hunt, MCSE, MCDBA
AISA Logic, Inc.
http://aisalogic.com

--Think when you breathe.

Hai there,

I have this problem (It has to work at financial companies, anyway):

Multi user system:

Two users work with the same customer's data. Both users want to ADD a
record
(let's say data of a contact person for the current customer) at the same
time.

I want to prevent this. The customer shouldn't get two records with the same
contact person.

Isn't this one of the basics of transactions?
I can't get it done with the BeginTrans, CommitTrans and Rollback methods.

Anyone got an idea?

Thanx,
Octavie

Craig, the primary key is an automatically generated GUID...

To you all, thank you for all the efforts and time. Whether the examples given
are reality or not, in theory the problem could exist in any system.

I think I've found a solution of some kind:

The Connection object has a property called IsolationLevel. You can set this
property in conjunction with the BeginTrans method. If you set this property to
adXactIsolated any transaction on a table will be isolated (even reads).

I have to test it thoroughly, but at first sight it works fine.

Thanks,
Octavie