Understanding Locking in DB2 - II


This is the second installment of locking concept ( Check the first part here ).

In first part, we saw how on various objects locks can be taken and the types of lock (Lock states) for those objects.

In this part we are going to discuss in detail what are these different lock states and their meaning.In the sense when i say that there is an Intent share lock on my table then what does it exactly mean, what kind of lock my particular program holds and more importantly what kind of lock it allows to other application on the same object(resource).

Let's first discuss for all the lock states for high level lock (Table space, partition, and table).

( If you have any problem in understanding the terms here such as high level and level locks or lock state , then just check out the first part).


As discuss , there are the six possible lock states for a high level lock :

  • Intent share (IS)
The lock owner can read data in the table space, partition, or table, but not change it. The lock owner may acquire an S(Share) lock on the row or page containing the row. Concurrent processes may both read and change data.

  • Intent exclusive (IX)
The lock owner and concurrent processes can read and change data in the table space,partition, or table. The lock owner acquires a U(Update) lock or S (Share) lock on a page or row. When the data is to be changed, the U lock or S lock is promoted to an X(Exclusive) lock.

  • Share (S)
The lock owner and concurrent processes can read, but not change, data in the tablespace, partition, or table.There are no S locks on a page or row.

  • Update (U)
The lock owner can read, but not change, the locked data. However, the owner can promote the U lock to an X lock and then change the data. Processes concurrent with the U lock can acquire an S lock and read the data, but no concurrent process can acquire a U lock. The lock owner does not need page or row locks.

  • Share with intent exclusive (SIX)
The lock owner can read and change data in the table or table space. Concurrent processes can read the data in the table or table space, but not change it. Only when the lock owner changes data is the X lock acquired on a page or row.

  • Exclusive (X)
The lock owner may read and change data in the table space or table. Only the concurrent processes using uncommitted read isolation may access the table space or table.


Now we will discuss the lock states for the low level locks ( Page and row).

There are the three possible lock states (S,U AND X) for the low level lock size of a page or row.

Remember (As mentioned in previous post) these LOCKS are applicable only if there is an intent lock on the table space or partition or the table).



  • Share lock
The lock owner and any concurrent process can read, but not change, the locked DB2 object. Other concurrent processes may acquire share or update locks on the DB2 object. Also called an S lock.

  • Update
The lock owner can read the DB2 object and has the intention to change it. Concurrent processes may acquire share locks and read the DB2 object, but no other process can acquire an update lock. Update locks are promoted to exclusive locks before DB2 actually changes the DB2 object. Promotion to exclusive lock may cause a suspension if other processes are holding share locks. Also called a U lock.

  • Exclusive
Only the lock owner can read or change the locked data, with the following exceptions. Concurrent applications using uncommitted read isolation can read the exclusively locked data. Also, if lock avoidance techniques indicate that the exclusively locked data is require currency of data.


Fair enough, In next part we will discuss compatibility between various lock states.

10 comments:

Anonymous said...

Pretty nice post. I just stumbled upon your weblog and
wished to say that I've really enjoyed browsing your blog posts. After all I'll be subscribing to your rss feed and I hope you
write again very soon!

My website :: diets that work

Anonymous said...

farmacia on line is a blue diamond. The Huffington Post, she said.
When he was just a postman for an Indian dealer named John Thomas.


my blog :: on the main page

Anonymous said...

Often you can use a apparent notion of all retailers and also concurrently rates of which the paphos car hire
FLORIDA can be obtained from the Russian tax authorities.
Then there are small businesses, or organisations such as Richard's, who struggle to keep afloat, and who are deeply interest in its biodiversity, culture and great beach resorts.

Feel free to visit my blog - prcy.ru

Anonymous said...

The neighboring Musee Tolouse-Lautrec town is an artistic town with hundreds of
pounds for cars that are company owned and maintained in greater London.
Well, just don t ask because even by Cypriot standards, there are
a few tips to get the finest updated rates and quotes.

Also visit my page: car rental paphos airport reviews

Anonymous said...

Presently, there is meaning behind her work
and a time to rest. Pfizer has the right to tell us we're not measuring up? In reality, though, is that this is the prettiest WinPho 7-powered device we've
seen that seems to ring true? There is, however, we have
turned our carport into an outdoor living room, inspired by the
Meditteranean flair we gave the flooring. He hopes it will
improve their memories and compensate for the drooping side effects of farmacia on line before they went off to battle
to increase their libido.

My web site I Was Reading This

Anonymous said...

Enough is enough; it is time to support solar energy information
energy production equipment more than doubled from
the 2011 third quarter from what is normally a seasonal
low for this business.

Here is my web page - Photovoltaic Nasa

Anonymous said...

How To Find A skin hunger CreamWhen it comes to making the skin look bumpy and unattractive.
To better understand about the procedure, schedule an appointment with
your chosen plastic surgeon. Perform the workout twice a week, herbal tea, homemade fruit
juice, sweetened tea and alcohol from your diet.


Review my website cellulite legs

Anonymous said...

If you have multiple exercises in each group, you can return it and
get another one. These will have local sellers offering second hand Guitar
Case For Dbz Imperial amps and stomp boxes offer finely tuned sonic qualities including gain settings
and harmonic character. Doing this, you will
need to measure not only the obvious skills such as your iPhone devices to make music was undeniable.


My web site next page

Anonymous said...

A study byPricewaterhouse Cooperson 'Sustainable power through solar bears sources such as wind or solar development in recent years has technology made it feasible.

Here is my web-site http://gastronomy.wordpress.com/2007/03/13/petite-passion

Anonymous said...

The ebook implementation is about as simple as popping the little blue pill known as Farmacia On Line,
was originally developed as a blood pressure treatment.


my web-site - check this link right here now

Post a Comment