locking

[vin]

Hi,

I have a question regarding transactions
and locking.

Suppose that I start a transaction and make
modifications on several tables in a database.

At the same time another user delete a record
that i just used.

When I call committransaction it will fail.

The question is how do we cope with this problem.

Thanks

[Johnny101]

If you are in the middle of a transaction and another person issues a delete for a record you just updated - your transaction will still have that record locked, and the other command should wait until your transaction ends before sending the delete through.

hopefully, this situation will never happen because of how transactions work. The data you are updating will not appear to any other outside command. if the other delete is inside a transaction (which it should be), the you can't see that delete in your transaction. if you locked the record first, then youre fine - your transaction will update the record and return success. although as soon as your transaction ends the second will delete that record so the user might think something happened because the data he just changed is now totally gone. If the second transaction locks the record first, then it will be deleted and commited before your transaction tries to update it, so it will update zero rows - which you can test for and send a message accordingly.

hope this clarifies a little.

John

John Pirkey
MCSD
http://www.ShallowWaterSystems.com
http://www.stlvbug.org

[vin]

Hi John,

Thanks for you reply,

If you have time to clarify just one more thing.

What locktype should I use in order for the
transaction to keep locked all records I've
modified during the transaction process even
after calling update method.

Regards,

Valery Nikolov

http://listen.to/quark

[Johnny101]

all the records you touch will be locked until you call "CommitTrans" (or "RollbackTrans") on the connection that called "BeginTrans". Locktype isn't important, but if it makes you feel safer, then i would suggest using adLockPessimistic.

have fun,

john

John Pirkey
MCSD
http://www.ShallowWaterSystems.com
http://www.stlvbug.org