Re: row vs row.column level locking

"Jim Kennedy" <kennedy-downwithspammersfamily_at_attbi.net> wrote in message news:4_WdnSsVsuUZPT7eRVn-og_at_comcast.com...
>
> "NetComrade" <netcomradeNSPAM_at_bookexchange.net> wrote in message
> news:43a30aee.270110453_at_localhost...
>> On Fri, 16 Dec 2005 01:58:57 GMT, Mladen Gogala <gogala_at_sbcglobal.net>
>> wrote:
>>
>> >On Thu, 15 Dec 2005 19:03:20 +0000, NetComrade wrote:
>> >
>> >> I feel different processes should be able to update different columns
>> >> w/o blocking each other, as long as what's updated is not the PK.
>> >
>> >Why do you feel that?
>>
>> Why not? Let's say i have a table within a web application that has
>> fields that are updatable by end-user and and fields updatable by a
>> batch job. Currently I have to denormalize and separate the two
>> tables, so that user won't block batch and vice versa. If I have
>> multiple batch jobs running at different times I might have to do even
>> more separation (if the batch jobs are taking too long)...
>>
>> No-one clearly explained to me why not to have such a feature. The
>> main reason I can think of is that Oracle would have to do much more
>> work to track changes for 'read-consistency'
>>
>> .......
>> We run Oracle 9.2.0.6 on RH4 AMD
>> remove NSPAM to email
>
> The database can't know if columns in the same table should be allowed to
> be
> treated as if they aren't related to one another. Hence the row level
> lock
> and not a row and column (or cell) level lock. If they weren't related to
> one another then they would be in separate tables.
> Jim
>
>

technically speaking, if there's any FK-PK path between tables, columns are related -- that's where the term 'relational' comes from. you're referring to dependence/identity

interesting concept, but it would require multi-versioning at the column level instead of the row level to maintain the read-consistency model and could possibly have significant impact on queries and memory usage, and rollback segment usage, as read-consistency would need to reconstruct the correct version of each column, no each row.

since the requirement is rare and work-arounds exist (lock and transaction management, splitting tables if necessary), there doesn't seem to be a great business or technical reason to invest in re-architecting everything involved and solving the ancillary issues this would raise in the database internals -- yet.

++ mcs Received on Sat Dec 17 2005 - 05:44:17 CST