Re: row vs row.column level locking

"Serge Rielau" <srielau_at_ca.ibm.com> wrote in message news:40igg0F1a6nh4U2_at_individual.net...
> Mark C. Stock wrote:

...
>> ++ mcs
> Right on!
>
> --

why, thank you.

reviewing the entire thread (after my first post, of course) it seems that the one issue not getting enough attention is transaction duration

i only need column level locking if two transactions on the same row cannot be serialized -- and when is that a problem? as implied (but not discussed) when batch transactions that _actually need to run as a single transaction_ (as opposed to batch processes, which could legitamately include multiple transactions) collide with other transactions. typically such large-scale batch transactions are not scheduled during prime OLTP hours, so for the OLTP transaction to have to wait for the properly-scheduled batch or visa-versa typically means the OLTP or batch job is poorly scheduled

but this type of scheduling may just be due to traditional resource and technology limitations, not necessarily business rules -- plus globalization of applications (as in 24*7 'prime time' access) starts to blow traditional scheduling schedules to pieces. so at some point column level locking may become more of a necessity; until then the vertical partitioning of tables will probably have to do.

but even if and when column level locking becomes available, it cannot be broadly applied without considering column constraints -- as implied in the startdate enddate example. just because i can restrict locking to the enddate column, that's not going to help, because i also need to guarantee the startdate (or any other constraining column) is not changed by another transaction. perhaps a mechanism like DBMS_LOCK could perform that function or perhaps a column-locking algorithm would need to be more of a column-set locking algorithm.

++ mcs Received on Sat Dec 17 2005 - 07:58:10 CST