Locking Modes

From: Karl & Betty Schendel 
Subject: Re: locking

At 1:51 AM +0000 10/28/99, morella3253@my-deja.com wrote:
>Hi,
>
>I am having trouble understanding the purpose of IX and IS
>locks on tables. Can anyone please give me an example of
>why they are necessary and what would happen if they were
>not used.

IS and IX locks are needed so that sessions taking page locks are aware
of sessions that have table locks.  Consider this scenario:

session 1 X-locks page 100
session 2 X-locks entire table

without any other locking both sessions would proceed.  That would be
wrong, session 2 needs to wait for session 1.
Ingres uses the IX lock thus:

session 1 X-locks page 100 and IX-locks entire table
session 2 attempts to X-lock entire table, sees the IX table lock and waits.

and similarly for the S and IS locks.

Karl R. Schendel, Jr.
K/B Computer Associates   schendel@kbcomputer.com
Ingres and Unix Expertise

Past President, North American Ingres Users Association
past_president@naiua.org



From: Chip Nickolett 
Subject: Re: locking

Karl's explanation below is technically correct but may not fully
answer the question asked.  The IX & IS locks were primarily
implemented for performance reasons.  When someone tries
to acquire a table level lock Ingres needs some mechanism
of validating that this is possible.  If they did not exist then
Ingres would end up scanning all locks on that table (not the
most efficient operation in some scenarios) looking for
incompatible locks.  The IS / IX lock implementation is much
more efficient and easier to manage.

Chip Nickolett           ChipN@Comp-Soln.com
Comprehensive Consulting Solutions, Inc.   (www.Comp-Soln.com)
Phone:  414-544-9954     Fax:  414-544-1236

President - North American Ingres Users Association (NAIUA)



From: Roy Hann 
Subject: Re: Concurrency Question.
To: Jon.Blake@ons.gov.uk

>I've noticed within the errorlog that the locks have different modes - this
>one being mode 5, what is the significance of the different modes?

Darn, I thought I knew this, but now I am assailed by doubts and I can't lay
hands on where I've got it written down.  I _pretty_ sure the modes are
numbered as follows:


0 NL (null--a placeholder)
1 IS (intended-shared--some page has a shared lock)
2 IX (intended-exclusive--some page has an exclusive lock)
3 S (shared--page/table is read-only)
4 SIX (shared, intended-exclusive, for cursor access)
5 X (exclusive--page/table is offline for update)

Don't make any wagers involving the family farm using the above.

Roy Hann
Rational Commerce Ltd.
"Ingres development, tuning, and operations experts"
Ingres Q & A
Back to William's Home Page
Email William