Dismiss
InnovationQ will be updated on Sunday, Oct. 22, from 10am ET - noon. You may experience brief service interruptions during that time.
Browse Prior Art Database

System and method of having Cluster based locking mechanism in relational databases

IP.com Disclosure Number: IPCOM000222893D
Publication Date: 2012-Oct-29
Document File: 3 page(s) / 78K

Publishing Venue

The IP.com Prior Art Database

Abstract

System and method of having Cluster based locking mechanism in relational databases

This text was extracted from a PDF file.
This is the abbreviated version, containing approximately 52% of the total text.

Page 01 of 3

System and method of having Cluster based locking mechanism in relational databases

MDC are unique objects in RDBMS that provide advantage over other indexes .

For example in following MDC table

CREATE TABLE MDCTABLE (Year INT,
Nation CHAR(25),
Color VARCHAR(10),... )

ORGANIZE BY( Year, Nation, Color )
there will be three dimensions: YEAR, NATION, and COLOR

If we start performing DML operation on above table and updating only the data for year 2011 (Total number of rows in table are 100 million and only 1 million has year as 2011 ) , after certain number of DML operation Lock escalation happens and eventually we application which is doing DML operation locks the full table.

For a multidimensional clustering (MDC) table, you can specify BLOCKINSERT with the LOCKSIZE clause in order to use block-level locking during insert operations only. When BLOCKINSERT is specified, row-level locking is performed for all other operations, but only minimally for insert operations. That is, block-level locking is used during the insertion of rows, but row-level locking is used to lock the next key if repeatable read (RR) scans are encountered in the record ID (RID) indexes as they are being updated. BLOCKINSERT locking might be beneficial when:

* There are multiple transactions doing mass insertions into separate cells

* Concurrent insertions into the same cell by multiple transactions is not occurring, or it is occurring with enough data inserted per cell by each of the transactions that the user is not concerned that each transaction will insert into separate blocks

http://publib.boulder.ibm.com/infocenter/db2luw/v9r7/topic/com.ibm.db2.luw.admin.perf.doc/doc/c000527


1.html


The method we ar...