[patch_done] Updating Same Record

[sandeep_c24]

I haven't noticed this before but I think this behavior is not correct. What is happening is I have a simple app connected to Firebird and Transaction Isolation is set to ReadCommitted. I run 2 instances of the same app and then edit same record. The first app updates the record but if I edit the same field, using 2 instance of the app, my changes are lost and I don't get any conflict error. I think an exception should be raised at this point.

Comments please.

Regards

Sandeep

[Yogi Yang]

I am not a pro, but as far as Fb is concerned I think it maintains versioning of data records (by default) so you should always see the latest updates but can see any other previous updates from 'rdb$database'.

I don't know the exact way of seeing previous updates nor do I know as to how many updates are maintained in each record history.

[sandeep_c24]

It's got nothing to do with FB versioning, but the way the updates are handled by Zeos are not correct(IMO).

Has anyone verified this?

Regards

Sandeep

[Yogi Yang]

I don't know how Zeos will handle this because FB will never raise concurrency error even if two users will try to update the same record at the same time.

[sandeep_c24]

Zeos query returns update count which can be used to determine if the update was successful or not.

I think this should be used to tell user what has happened otherwise the user does not know if the upadtes were successful or not.

Regards

Sandeep

[sandeep_c24]

Mark what are your thoughts on this issue?

Sandeep

[mdaems]

The problem probably is the missing 'for update' in the select statement. So the selects do not lock the records and independent insert/update/delete statements are fired in each session when applying the updates. As long as the update from the second session doesn't fire it's updates between the update and the commit of the first session you won't get an error. In that case the update fired by the second session should be the final result. And the update fired by the first session should be lost.
Is this the experienced behaviour? Try 'select... for update'. However, in that situation you won't be able to select-for-update the same records in 2 sessions simulataniously, I think.

Mark

[sandeep_c24]

The update done by second session should overwrite the updates done by the first session if WhereMode is wmWhereKeyOnly, but when it is wmWhereAll the update does not succeed as the record cannot be searched based on the where clause made. This I think should raise an exception as the update has failed.

What this gives Zeos is the ability to work properly without locking the record.

Sandeep

[mdaems]

Hi,

Thinking about it it seems a good idea. My comments:

• Check if we can add a switch using ZQuery.Properties
• It shouldn't be default behavior in 6.6.x as it could break applications depending on it
• It should become default behavior in a next version (and testing branch
• What about updates of multiple rows? These are also known to happen in case duplicate rows exist. Here the problem is that the update is already done when we see the problem, so reacting is even impossible for autocommitted (eg. mysql ISAM tables) queries.
• Have a look at TZGenericCachedResolver.PostUpdates. I think you can just check the return value of the Statement.ExecuteUpdatePrepared functon call and raise an error if it is 0 (or <> 1 ??)

Do you try and post?

Mark

[sandeep_c24]

I think it might be alright to add a property to ZQuery.Properties, but that means it has to done for each query and cannot be done for a database connection.
What if this switch is added to TZConenction.Properties?

I am not quite sure about updates to multiple rows. Do you mean when used with CachedUpdates? Could you please explain a bit more.

>Do you try and post?
I am using AutoCommit and TZQuery so Post is fired for each record.

I'll look at TZGenericCachedResolver.PostUpdates and let you know if I have any difficulties.

Sandeep

[mdaems]

Getting to the properties of the connection may be even more difficult than getting to the properties of the query.
The more : as wmWhereKeyOnly is a query property, it seems more logical to regulate it's behavior also at the query level.

I am not quite sure about updates to multiple rows. Do you mean when used with CachedUpdates? Could you please explain a bit more.

I meant : when a primary key isn't available, it might be possible that the update query affects multiple rows instead of one or none. This is a known side effect of 'not wmWhereKeyOnly' only mode. In this case changing one record also changes the duplicates. This could also be catched in the same check you propose.

>Do you try and post?
I am using AutoCommit and TZQuery so Post is fired for each record.

I meant : do you try to fix this and post your solution to this forum?

Mark

[sandeep_c24]

Assuming one update command is executed for each record updated (in TZTable/TZQuery) the updatecount must always return 1. If the updatecount is not 1, either for wmWhereAll or wmWhereKeyOnly, then this is an error condition.

If multiple records are updated then the query must have been executed directly e.g. Update TableA set FieldA = 'NewVal'. If this is the case then I think we don't need to raise any error.

Does the above assumption sound correct?

Sandeep

[mdaems]

If multiple records are updated then the query must have been executed directly e.g. Update TableA set FieldA = 'NewVal'. If this is the case then I think we don't need to raise any error.

No, this situation also occurs when there are datasets being updated that have duplicate rows. In that case every row fires a separate update/delete, but the where part of that update returns multiple rows, resulting in updatecounts > 1. This is wrong. By using a rollback in a normal transaction this can be undone. But for non-transactional situations (autocommit, myisam tables with mysql, ...) the bad is already been done when you detect it.
I'm not sure if we should raise an exception or not, as programmed recovery is impossible... But maybe it's better to do so as that makes the programmers aware of the problem with their code. (problem being that the selected columns don't contain all necessary fields to perform unique updates)
Conclusion ( I think):
Every updatecount <> 0 is an error
In version 6.6 this can raise an error IF requested explicitly by setting a query property
In version 6.7 this should raise an error UNLESS this is disabled explicitly by setting a query property

Do you think this is possible?

Mark

[sandeep_c24]

Hi Mark

If the table has a primary key then only we raise an exception. If the table has no primary key then there is no way to identify a record uniquely and we allow multiple records to be updated. And we could raise an exception, as you suggested, based on some setting in the query?

Sandeep

[mdaems]

I wouldn't make it that complex. This behaviour will be difficult to explain. I think my last conclusion is easier. No primary key is almost always bad design. The case where it is good design the option can be used.