Board index » delphi » Transaction

Fidias

Delphi Developer
Mon, 04 Aug 2003 01:24:06 GMT

Transaction

I've a transaction with the parameters

Transaction.Params.Add('write');
Transaction.Params.Add('read_committed');
Transaction.Params.Add('no_rec_version');
Transaction.Params.Add('nowait');

Table B with event DeleteError, PostError and EditError managing the sql
code -615 (record lock)

I've two clients accessing to table B in a form with navigator, at the same
time.
If user Z edit a record and the client Y edit this record, any message error
is shown,
WHY?
Any idea?

 
Ded

Delphi Developer
Mon, 04 Aug 2003 02:02:58 GMT

Re:Transaction


Quote
Fidias wrote:
> I've a transaction with the parameters

> Transaction.Params.Add('write');
> Transaction.Params.Add('read_committed');
> Transaction.Params.Add('no_rec_version');
> Transaction.Params.Add('nowait');

> Table B with event DeleteError, PostError and EditError managing the sql
> code -615 (record lock)

> I've two clients accessing to table B in a form with navigator, at the same
> time.
> If user Z edit a record and the client Y edit this record, any message error
> is shown,
> WHY?
> Any idea?

This is not IBX problem, it is common transaction principles. If user Z have not

commited his changed, update in other read_committed transaction by Y simply
cannot "see" this record and update silently goes into void. General way is
to use snapshot transactions when making changes and read_commited
for select large cursor for viewing purposes.

Fidia

Delphi Developer
Mon, 04 Aug 2003 02:18:56 GMT

Re:Transaction


Quote
> This is not IBX problem, it is common transaction principles. If user Z
have not

> commited his changed, update in other read_committed transaction by Y
simply
> cannot "see" this record and update silently goes into void.

Yes, that it's my problem
But

Quote
>General way is
> to use snapshot transactions when making changes and read_commited
> for select large cursor for viewing purposes.

I dont understand you, you say that use snapshot = concurrency + nowait,
but this isolation level means that I only see the records commited before
start the transaction????
Team

Delphi Developer
Mon, 04 Aug 2003 02:36:22 GMT

Re:Transaction


Quote
Fidias wrote:

> I've a transaction with the parameters

> Transaction.Params.Add('write');
> Transaction.Params.Add('read_committed');
> Transaction.Params.Add('no_rec_version');
> Transaction.Params.Add('nowait');

> Table B with event DeleteError, PostError and EditError managing the sql
> code -615 (record lock)

> I've two clients accessing to table B in a form with navigator, at the same
> time.
> If user Z edit a record and the client Y edit this record, any message error
> is shown,
> WHY?

The two can edit, but only the first one to post will succeed, the second one
will get a deadlock.  Is your question why client Y didn't get a deadlock error
when he started editing?  The answer is that going into edit mode requires no
checks on the Server side.  One way around this is how ever they go into edit
mode do something like Dataset.Edit; Dataset.Post; Dataset.Edit;.  This will
"lock" that record or get an immediate deadlock since you have posted a nothing
change.  As long as everyone working with that table follows that rule you will
always get a deadlock message.

Quote
> Any idea?

--
Jeff Overcash (TeamB)
      (Please do not email me directly unless  asked. Thank You)
The correct way to punctuate a sentence that starts: "Of course it is
none of my business but ~" is to place a period after the word "but".
Don't use excessive force in supplying such a moron with a period.
Cutting his throat is only a momentary pleasure and is bound to get
you talked about.   (RAH)
Ded

Delphi Developer
Mon, 04 Aug 2003 18:25:32 GMT

Re:Transaction


Quote
Fidias wrote:
> > This is not IBX problem, it is common transaction principles. If user Z
> have not

> > commited his changed, update in other read_committed transaction by Y
> simply
> > cannot "see" this record and update silently goes into void.

> Yes, that it's my problem
> But

> >General way is
> > to use snapshot transactions when making changes and read_commited
> > for select large cursor for viewing purposes.

> I dont understand you, you say that use snapshot = concurrency + nowait,
> but this isolation level means that I only see the records commited before
> start the transaction????

Real commercial application is rather sophisticated combination of reads
to present data for user in long read_commited transactions and updates
in short snapshot ones. Simplification of snapshot idea is: update in snapshot

transaction see data in state it was when transaction started and knows what
data was updated since that moment, but, independently of was it commited
or not, don`t knows what are the changes, so it generates exception when
you try to update such data. Update non-commited data in read_commited
transaction don't see non-commited data at all. Reliable practice is:
1. Present data retrieved in read_commited in the grid form.
    User select row and tried to change it.
2. Start short snapshot, try update, check exceptions and commit/rollback.

Real application, depends on nature of data and type of it presentation in
database structure usially is more sophisticated.

Other Threads

1. Tricky transaction problem: Catching EDatabaseError from one of several Post within a transaction

2. SQL Server and Delphi: START TRANSACTION vs maximum of transactions

3. The rollback transaction request has no corresponding BEGIN TRANSACTION

4. The rollback transaction request has no corresponding BEGIN TRANSACTION

5. invalid transaction handle (expecting explicit transaction start)

6. Transactions and the default transaction

7. Error 335544332 Invalid transaction handle (expecting explicit transaction start)

8. Unable to start transaction: Transaction is active

9. Next transaction older than oldest active transaction

10. invalid transaction handle (expecting explicit transaction start)