What would you like to do?

Define Granularity in database management system?

already exists.

Would you like to merge this question into it?

already exists as an alternate of this question.

Would you like to make it the primary and merge this question into it?

exists and is an alternate of .

If one application holds a lock on a database object, another application might not be able to access that object. For this reason, row-level locks, which minimize the amount of data that is locked and therefore inaccessible, are better for maximum concurrency than block-level, data partition-level or table-level locks. However, locks require storage and processing time, so a single table lock minimizes lock overhead. The LOCKSIZE clause of the ALTER TABLE statement specifies the scope (granularity) of locks at the row, data partition, block, or table level. By default, row locks are used. Only S (Shared) and X (Exclusive) locks are requested by these defined table locks. The ALTER TABLE statement LOCKSIZE ROW clause does not prevent normal lock escalation from occurring. A permanent table lock defined by the ALTER TABLE statement might be preferable to a single-transaction table lock using LOCK TABLE statement in the following cases:
  • The table is read-only, and will always need only S locks. Other users can also obtain S locks on the table.
  • The table is usually accessed by read-only applications, but is sometimes accessed by a single user for brief maintenance, and that user requires an X lock. While the maintenance program runs, the read-only applications are locked out, but in other circumstances, read-only applications can access the table concurrently with a minimum of locking overhead.
For an MDC table, you can specify BLOCKINSERT for the LOCKSIZE clause in order to use block-level locking during INSERT operations only. When this 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 for next-key locking if RR scans are encountered in the indexes as they are being updated. BLOCKINSERT locking might be beneficial in the following cases:
  • There are multiple transactions doing mass insertions into separate cells.
  • Concurrent insertions to 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.
The ALTER TABLE statement specifies locks globally, affecting all applications and users that access that table. Individual applications might use the LOCK TABLE statement to specify table locks at an application level instead.
parant post http://publib.boulder.ibm.com/infocenter/db2luw/v9r5/index.jsp?topic=/com.ibm.db2.luw.admin.perf.doc/doc/c0005271.html
my blog http://jacksparrowcode.blogspot.com/
45 people found this useful
Thanks for the feedback!

What is the database management system?

Answer   Database is a collection of related data while database management system is a software which is use to access the database.   A database is an accumulation

ADVANTAGES of database management system?

One advantage of having a database management system is the fact  that it can store a lot of information about customers. Another  advantage is the fact that businesses can

Components of a database management system?

  Components of DBMS   A database management system (DBMS) consists of several components. Each component plays very important role in the database management system en

Define and explain Database management systems?

A collection of programs that allow a user to extensively modify,  store, and extract information from a database is called a database  management system. Also knows as "DBM

How is a database different from a database management system?

A database is simply the data that has been stored in tables. Adatabase management system includes all the elements that arerequired to work with the database. This would incl

What is a database or database management system DBMS?

Basically a simple computer file or card index containing related data is a database. In such a system the items data will be laid out as a table with related data items formi

What are the objectives of database management systems?

Shareability: An ability to share data resources is a fundamental objective of database management. In its fullest interpretation, this means different people and different pr