| Subject | Re: [ib-support] Updating one field on a transaction locks whole row? |
|---|---|
| Author | Helen Borrie |
| Post date | 2001-06-21T08:24:49Z |
At 08:43 PM 20-06-01 +0200, you wrote:
One has to question why your needs are such that you built a SP to update one column throughout the entire table in one hit. One wouldn't normally do something like this except as a part of a one-time restructuring exercise, e.g. to initialize something that, from there forward, will be maintained by applications. In that case, you would certainly want other users locked out of that table and any that depended on it.
Normally, there would be a WHERE clause to limit an update of this nature... only those rows selected by the WHERE clause would be unavailable to other transactions (unless you imposed a very pessimistic isolation level on your transaction, such as SNAPSHOT TABLE STABILITY).
Helen
All for Open and Open for All
InterBase Developer Initiative ยท http://www.interbase2000.org
_______________________________________________________
>I'm executing a "long duration" Stored Procedure which includes anNo - and there is no reason why there ever would be. A column is part of a collection of data attributes that go to make up a "set of data". If you change one attribute, you change the whole set and Transaction control isolates that set for you so that you have a defined level of control over it.
>UPDATE TABLE SET FIELD1=value .
>I must wait until StoredProcedure finish, to automatically commit transaction to server, and meanwhile I can't post any other changes to table TABLE, even in other fields than FIELD1.
>Is this the current behavior? Or is there a way to lock ONLY that field I need to update inside the Stored Procedure?
One has to question why your needs are such that you built a SP to update one column throughout the entire table in one hit. One wouldn't normally do something like this except as a part of a one-time restructuring exercise, e.g. to initialize something that, from there forward, will be maintained by applications. In that case, you would certainly want other users locked out of that table and any that depended on it.
Normally, there would be a WHERE clause to limit an update of this nature... only those rows selected by the WHERE clause would be unavailable to other transactions (unless you imposed a very pessimistic isolation level on your transaction, such as SNAPSHOT TABLE STABILITY).
Helen
All for Open and Open for All
InterBase Developer Initiative ยท http://www.interbase2000.org
_______________________________________________________