Menu
▾
▴
firebird-devel
|
From: Ivan P. <iva...@se...> - 2007-01-30 18:59:04
|
In the application's log file I found FB error
Statement failed, SQLCODE = -901
cannot update erased record
I think that it can occur in the following situation:
Updating (or deleting) a record consists of two stages
- old record version is read into buffer
- new record version is created/stored
If it is done in READ COMMITED transaction,
and some other transaction deletes that record and
manages to commit it between those two stages,
then "cannot update erased record" is raised.
It is easy to get this error with WAIT transaction, i.e.
- transaction B deletes record
- transaction A (wait, read committed) tries to update it, but has to wait
- transaction B commits
- transaction A wakes up and tries to finish its work,
but it finds out that the record was deleted in the meanwhile,
and so it raises the error.
----
Now, why am I writing all this ?
* Somehow, it seems to me that this error is more artifact of the way
the command is internally processed than real error.
* It can be raised in the situation one would not expect it - normally
the DELETE/UPDATE command either succeeds because
it deletes/updates required record(s), or it succeeds because there is nothing
to delete/update (e.g. delete from tab where 1=2;)
But in described situation the DELETE can complain that record
was already deleted - looks strange.
* Could this error by avoided at all ? If the record was deleted,
it means there is nothing to delete/update, so from users point of view
the error has no sense.
The only problem I see is with Before triggers that were already fired,
can't they be just silently undone ?
Ivan
|
|
From: Leyne, S. <Se...@br...> - 2007-01-30 19:03:10
|
Ivan, > In the application's log file I found FB error >=20 > Statement failed, SQLCODE =3D -901 > cannot update erased record Are you sure that you have the correct Firebird.msg file installed on the server? <S> |
|
From: Ivan P. <iva...@se...> - 2007-01-30 19:12:33
|
Sean, >> In the application's log file I found FB error >> >> Statement failed, SQLCODE = -901 >> cannot update erased record > > Are you sure that you have the correct Firebird.msg file installed on > the server? Hmmm, have you got different message when you tried it ? Ivan |
|
From: Leyne, S. <Se...@br...> - 2007-01-30 19:20:55
|
Ivan, > Sean, > >> In the application's log file I found FB error > >> > >> Statement failed, SQLCODE =3D -901 > >> cannot update erased record > > > > Are you sure that you have the correct Firebird.msg file installed on > > the server? >=20 > Hmmm, have you got different message when you tried it ? Nope, didn't even try it. Now that I have re-read your original posting, I have some comments. - The message is completely valid, and expected when using READ COMMITTED. - The message should not be "eaten"/ignored -- the message may not matter to you, but it could matter to someone. If you don't want to see the error, then modify your triggers to handle the SQL errors codes. Sean |
|
From: Leyne, S. <Se...@br...> - 2007-01-30 20:31:15
|
Ivan, > >> >> Statement failed, SQLCODE =3D -901 > >> >> cannot update erased record > ... > > - The message is completely valid, and expected when using READ > > COMMITTED. >=20 > Can you describe, in your words, what message this error > tries to tell to the user ? >=20 >=20 > I will try first: > Situation 1) If I do DELETE FROM TAB WHERE ID=3D1; > and the record is there, it is successfully deleted. >=20 > Situation 2) If I do DELETE FROM TAB WHERE ID=3D1; > but somebody else did the same (and committed) > just fraction of second before me, my command > still returns o.k. (with rows affected=3D0) >=20 > Situation 3) If I do DELETE FROM TAB WHERE ID=3D1; > and somebody else did the same "at the same time > but a bit sooner than me", then I can get error "erased record" > with the meaning > "Oh dear, you NEARLY deleted that record, but somebody > else was faster. Sorry." >=20 > Do you have some example of situation when it is important > to distinguish case 2 and 3 ? First, the examples you provide is not the issue you originally reported, which was that User #2 tried to *update* a record which had been deleted by User #1. The answer depends on whether the developer/user expects that someone else can delete the record. Without the error, a developer/user could easily blame the database for "loosing" the changes that they applied. The fact that you are using READ COMMITTED is the reason why an action performed by another user should be reported to you. > It was my original intention to just handle that error, > but as you can read in my other post, this error does not have > its own ErrorCode, so it can't be easily/reliably distinguished > from other errors. That would certainly makes life a little difficult. Sean |
|
From: Ivan P. <iva...@se...> - 2007-01-30 20:14:00
|
Sean, >> >> Statement failed, SQLCODE = -901 >> >> cannot update erased record ... > - The message is completely valid, and expected when using READ > COMMITTED. Can you describe, in your words, what message this error tries to tell to the user ? I will try first: Situation 1) If I do DELETE FROM TAB WHERE ID=1; and the record is there, it is successfully deleted. Situation 2) If I do DELETE FROM TAB WHERE ID=1; but somebody else did the same (and committed) just fraction of second before me, my command still returns o.k. (with rows affected=0) Situation 3) If I do DELETE FROM TAB WHERE ID=1; and somebody else did the same "at the same time but a bit sooner than me", then I can get error "erased record" with the meaning "Oh dear, you NEARLY deleted that record, but somebody else was faster. Sorry." Do you have some example of situation when it is important to distinguish case 2 and 3 ? > - The message should not be "eaten"/ignored -- the message may not > matter to you, but it could matter to someone. If you don't want to see > the error, then modify your triggers to handle the SQL errors codes. It was my original intention to just handle that error, but as you can read in my other post, this error does not have its own ErrorCode, so it can't be easily/reliably distinguished from other errors. Ivan |
|
From: Dmitry Y. <fir...@ya...> - 2007-01-30 21:33:14
|
Ivan Prenosil wrote: > > I think that it can occur in the following situation: > > Updating (or deleting) a record consists of two stages > - old record version is read into buffer > - new record version is created/stored > > If it is done in READ COMMITED transaction, > and some other transaction deletes that record and > manages to commit it between those two stages, > then "cannot update erased record" is raised. Correct. > * Somehow, it seems to me that this error is more artifact of the way > the command is internally processed than real error. I'd say it's an artifact of the read-committed implementation. > * It can be raised in the situation one would not expect it - normally > the DELETE/UPDATE command either succeeds because > it deletes/updates required record(s), or it succeeds because there is nothing > to delete/update (e.g. delete from tab where 1=2;) > But in described situation the DELETE can complain that record > was already deleted - looks strange. I kinda agree. But I think that all related issues should be solved as a whole. For example, if the concurrent transaction has modified the record, we need to re-fetch it and re-evaluate booleans. In other words, read-committed transactions should (almost?) never experience an update conflict. But it's not very easy to do. > * Could this error by avoided at all ? If the record was deleted, > it means there is nothing to delete/update, so from users point of view > the error has no sense. > The only problem I see is with Before triggers that were already fired, > can't they be just silently undone ? I fail to see how the trigger actions could be undone for a single record, sorry. Dmitry |
|
From: Ivan P. <iva...@se...> - 2007-01-30 22:29:58
|
"Dmitry Yemanov" wrote:
>> * It can be raised in the situation one would not expect it - normally
>> the DELETE/UPDATE command either succeeds because
>> it deletes/updates required record(s), or it succeeds because there is nothing
>> to delete/update (e.g. delete from tab where 1=2;)
>> But in described situation the DELETE can complain that record
>> was already deleted - looks strange.
>
> I kinda agree. But I think that all related issues should be solved as a
> whole.
Yes, it would be nice, but not absolutely necessary, there already are situations
when different commands have different handling. Example from my post to fb-support:
When one transaction locks (updates) row in Master table,
then other transactions
- can Update related rows in Detail table, but
- can't Insert new Detail rows (referencing pk of locked row)
> For example, if the concurrent transaction has modified the
> record, we need to re-fetch it and re-evaluate booleans.
Right, solving situation when concurrent transaction deletes
the record is easier, since there is nothing to re-fetch
(and it is what I had in mind, see Subject.)
>> * Could this error by avoided at all ? If the record was deleted,
>> it means there is nothing to delete/update, so from users point of view
>> the error has no sense.
>> The only problem I see is with Before triggers that were already fired,
>> can't they be just silently undone ?
>
> I fail to see how the trigger actions could be undone for a single
> record, sorry.
By creating internal savepoint ? (but you know - I do not know the source code ...)
Ivan
|
|
From: Dmitry Y. <fir...@ya...> - 2007-01-31 16:40:00
|
Ivan Prenosil wrote: > > By creating internal savepoint ? Per every row? It would be a performance killer. Dmitry |
|
From: Ivan P. <iva...@se...> - 2007-02-02 20:11:15
|
>> I fail to see how the trigger actions could be undone for a single >> record, sorry. >> By creating internal savepoint ? > > Per every row? It would be a performance killer. It is basically the same as creating savepoint inside trigger (sp) using WHEN ... DO. I do not know internals much, so I made short test, and indeed the impact on performace is just too big. Then I do not see any reasonable solution, other then making this "optimization" only for operations not using triggers (no a good idea, I know). Ivan |
×
Want the latest updates on software, tech news, and AI?
Get latest updates about software, tech news, and AI from SourceForge directly in your inbox once a month.
Thanks for helping keep SourceForge clean.
X