Conference orarep::nomahs::rdb_60

~~     <<< Note 4998.0 by ORAREP::OSOSPS::HIRAIWA "Yoshitaka Hiraiwa/SPS" >>>
~~                      -< Q: Page level locking behavior >-

~~And please tell me the concept of the page level locking.

Have you checked the Rdb documentation?  

Briefly, the concept uses page locks to manage both the page itself as well
as data concurrency on the page (ie, the page lock takes on the roll of
both the page lock and the record lock when page locking is enabled). 

~~For example, If you do not need full VMS lock management, you can use it.

Not sure what you mean by this but I don't think it's correct.  Frequently, 
page locking (which does use the OpenVMS lock manager) uses more locks than 
record locking with ALG enabled.

The advantage of page level locking is when you would normally lock many
records on a single page.  e.g. you have EMPLOYEES and JOB_HISTORY clustered
using HASHED indices and PLACEMENT VIA INDEX.  This will typically place the
EMPLOYEES row, all the JOB_HISTORY rows, the hash bucket for the EMPLOYEES
index, the hash bucket for the JOB_HISTORY index all on the same page.  So a
typical transaction would use at least 5 row locks:

	- system record,
	- employees,
	- employees hash bucket, 
	- job history,
	- job_history hash bucket

However, in general there might be duplicates nodes for the JOB_HISTORY hash
index and multiple job history rows.  So maybe you are locking a lot more than
5 rows on this page.

In this type of application a single page lock is cheaper then the 5+ locks
used by row locking.

However, when you use page level locking we do not know which rows are
actually locked, nor do we distinguish between rows read on the page versus
rows updated on the page [i.e. we can't release read locks as would be done
for READ COMMITTED if a row has been updated on the page].  So the page lock
is held until COMMIT (and ROLLBACK) time.  Therefore, unless the transactions
are short the overhead for page level locking can be expensive and reduce
concurrency.

Page level locking works well with OLTP style short transactions.

cheers,

Ian

Thank you very much for .1 and .2.

>Briefly, the concept uses page locks to manage both the page itself as well
>as data concurrency on the page (ie, the page lock takes on the roll of
>both the page lock and the record lock when page locking is enabled). 

I know above. But I don't understand it exactly.
So I ask the question about it.

Now I understand it because you explain it for me.

Best regards,
Yoshitaka

Yoshitaka,

>Area 182 is page level locking. Area 531 is row level locking.
>602324C1 holds 182:122 and is in progress. 602302BD holds 531:2 that is already
>committed. So 602302BD does not need it now but only holds it.
>
>When 602302BD is waiting for 182:122 lock and then 602300BF requests 531:2,
>is the blocking ast of 531:2 delivered to 602302BD or not?

When row level locking is being used, page locks are transient. They are 
just used to make sure that each user sees the current version of the page. 
Each page that is read is locked in PR, and each page that is updated is 
locked in EX.

When one user requests a page lock and that request is incompatible with an 
existing lock held by another user, the other user will receive a blocking 
AST. When a user receives a BLAST on a page, he will wait till the end of 
the current KODA verb and then demote the lock (which may involve writing 
the page back toi disk if it was updated).

In you case you say the blocker is not even in a transaction. In this case 
he should respond immediately to the BLAST.

> In fact, 602302BD does not release it.

I see no reason for this process not to respond to the BLAST if he really 
is not in a transaction.

G'day,

Mark.

Thank you for your reply, Mark.
I'm sorry for the delay. But I'm spending the time for reproducing this case.

>> In fact, 602302BD does not release it.
>I see no reason for this process not to respond to the BLAST if he really 
>is not in a transaction.

So why does 602302BD hold it. In my sample case, 602302BD is corresponding to
<User B>.

I created the sample files for this case on the following.

OSOV05::DSA3:[DUMP.RDB]LDDLDB.COM, A.SQL, B.SQL, C.SQL, WORKFINE.COM

If you run @workfine or define rdm$bind_buffers as 10 for all users,
<User B> will release it.

Best regards,
Yoshitaka

Please try it with following instructions.

<System>
First, to create the sample database.
$ @lddldb

<User A>
$ mc sql$
SQL> @a
SQL> atta 'file lddldb';
SQL> set tran read write wait 15;
SQL> insert into tmp1(area_id,fld1) values('001','zzzzz');
1 row inserted
SQL>

<User B>
$ mc sql$
SQL> @b
SQL> atta 'file lddldb';
SQL> set tran read write wait 15;
SQL> declare tr1 update only cursor for
cont>  select id, fld1 from areaid order  by  id;
SQL> open tr1;
SQL> fetch tr1;
 ID     FLD1
 001    aaaaa
SQL> fetch tr1;
 ID     FLD1
 002    bbbbb
SQL> fetch tr1;
 ID     FLD1
 003    ccccc
SQL> fetch tr1;
 ID     FLD1
 004    ddddd
SQL> close tr1;
SQL> commit;
SQL> set tran read write wait 15;
SQL> select * from tmp1 where area_id='001' and fld1='zzzzz';
....<Wait>....

<User C>
$ mc sql$
SQL> @c
SQL> atta 'file lddldb';
SQL> set tran read write wait 15;
SQL> declare tr1 update only cursor for
cont>   select id,fld1 from areaid order  by  id;
SQL> open tr1;
....<Wait>....

<System>
$ rmu/show stat lddldb

Node: BEPH01          DEC Rdb V6.0-15 Performance Monitor   13-FEB-1997 17:00:50
Rate: 3.00 Seconds               Stall Messages             Elapsed: 00:05:50.21
Page: 1 of 1      $4$DUA230:[V60DB.SAIGEN]LDDLDB.RDB;1       Mode: Online

Process.ID Since......   Stall.reason............................. Lock.ID.
20400278:1 17:00:40.66 - waiting for page 3:2 (PR)                 1F003518
20400275:1 17:00:46.49 - waiting for page 2:2 (PR)                 2300480D

Dave Bryden pulled this across, and both he and I have taken a look.

This example does not match your description in the base note. For a start,
locking on the area in your example is page level, but your base note said
row level locking was used.

In your example, user A inserts a row and does not commit. This will
require that the page that holds the new row be locked in EX until the
transaction commits. Since A does not commit, an attempt to read that row
by B will stall on one of the uncommited page locks. This is normal expected
behaviour. 

The third user C then tries to open a cursor and you say that this open 
will stall. Well on our machine with Rdb 6.0A ECO 6, user C does not 
stall.

So while I cannot reproduce a stall for user C, there is no evidence in 
your example of erroneous behaviour.

G'day,

Mark. 

.6>Dave Bryden pulled this across, and both he and I have taken a look.

Thank you very much for your efforts.

.6>This example does not match your description in the base note. For a start,
.6>locking on the area in your example is page level, but your base note said
.6>row level locking was used.

I'm sorry for you to understand above from my explanation.

I said it as following in the base note.
.0>Area 182 is page level locking. Area 531 is row level locking.

Anyway, the configuration matrix between customer DB and me as follows;  

	+----------------------------+-------------+-------------+
        |                            | Customer DB | Example DB  |
	+----------------------------+-------------+-------------+
        | Areaid table: Row locking  |    531      |      2      |
	+----------------------------+-------------+-------------+
        | Tmp table   : Page locking |    182      |      3      |
	+----------------------------+-------------+-------------+
        the number is area id.

.6>In your example, user A inserts a row and does not commit. This will
.6>require that the page that holds the new row be locked in EX until the
.6>transaction commits. Since A does not commit, an attempt to read that row
.6>by B will stall on one of the uncommited page locks. This is normal expected
.6>behaviour. 

Yes, I agree it.
The problem is that user C is waiting for the lock that is already commited.

.6>The third user C then tries to open a cursor and you say that this open 
.6>will stall. Well on our machine with Rdb 6.0A ECO 6, user C does not 
.6>stall.

Is it true? I'll try it with ECO 6.

.6>So while I cannot reproduce a stall for user C, there is no evidence in 
.6>your example of erroneous behaviour.

It is bad for me. ( But it is good for me that user C works fine with ECO 6.)

On our machine, these are the workarounds for this case;

1. Disable fast commit.
2. Use small number of buffers.
3. Use select statement instead of cursor.

So cursor or buffer or fast commit is concerned with this case.

Again, thank you very much!
Yoshitaka

Yoshitaka,

>The problem is that user C is waiting for the lock that is already commited.

In your example, neither user A nor user C commit. User A does the insert 
and does not commit.User B stalls waiting for A. So where is there commited 
transaction?

G'day,

Mark.

Hi Mark, thank you very much.

.7>The problem is that user C is waiting for the lock that is already commited.

.8>In your example, neither user A nor user C commit. User A does the insert 
.8>and does not commit.User B stalls waiting for A. So where is there commited 
.8>transaction?

User C needs a record that user B has. In my example the record is in 2:2.
User B commited the transaction that read the records in 2:2. 
User C does not need a records that user A is updating.

Anyway, I try my example with V6.0A ECO 6. The result is good.
So user C works fine. I can't find the description concerned about this problem
in ECO 6 release notes.

Best regards,
Yoshitaka