Re: row vs row.column level locking

From: Serge Rielau <srielau_at_ca.ibm.com>
Date: Sat, 17 Dec 2005 07:57:14 -0500
Message-ID: <40ig9iF1a6nh4U1@individual.net>

Mladen Gogala wrote:
> On Fri, 16 Dec 2005 19:22:15 +0000, NetComrade wrote:
>
>

>>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)...

>
>
> Or, you must do partitioning or you must organize your jobs better
> so that two fields in the same table are not updated in two different
> batches without a good reason. I would be extremely uncomfortable
> with the idea that two processes can write to the the same row. That
> would be a guaranteed recipe for corruption.
And the reason is?

For semantic correctness any given data that the business transaction uses ffor decision making must not change whiel the transaction is in flight.
If a transaction does not require knowledge of the content of a column in a row then that column need not be locked and someone else can change it without affecting corectness. Thatis true even in serializable isolation.

The only reason to not have column level locking is that it's inefficient to keep track of such a fine level. Row level locking has proven quite sufficient and column level locking can be achieved easily through vertical partitioning of the data (as youmay do when you separate employee and manager specific information into different tables)

The risks everyone takes by not running serializable/RR are real because most don't truly understand the implications of other isolation levels.

Column level locking is safe, just too expensive.

Don't confuse isolation (semantics) with locking (implementation).

Cheers
Serge

-- 
Serge Rielau
DB2 Solutions Development
DB2 UDB for Linux, Unix, Windows
IBM Toronto Lab

Received on Sat Dec 17 2005 - 06:57:14 CST