[Info-vax] RDB Question

[Jan-Erik Söderholm]

Den 2018-12-04 kl. 17:43, skrev Dave Froble:
> On 12/4/2018 10:16 AM, Jan-Erik Söderholm wrote:
> 
>> Note also that, even if Rdb supports record locking (has done from
>> V1.0, if I'm not wrong), it also has something called "Adjustable
>> record locking". So Rdb doesn't move locks down to the record level
>> if it doesn't have to (row contention between users).
>>
>> The default is what is shown from this database dump below.
>> So one single process doing a large query on its own will put
>> locks at blocks of 1000 database pages at a time (to start with).
> 
> Jan-Erik, I know you didn't design and implement Rdb, so I'm not directing 
> this at you.

If you actually want to ask the Rdb maintainers something, you
can use the oraclerdb mail list instead... :-)

> 
> I have to ask, why would Rdb be taking out locks for a query (if I'm 
> understanding the definition of query)?...

OK, "query" was not a good word. Read "SQL statement" instead.

There are a number of different locks. Read locks, write locks,
shared locks, protected locks. In a lot of combinations.

And there are locks not only for the actual data records but
for a number of internal data structures in Rdb.

> If one is going to read, but never write, then why lock?

Rdb might not know that. You might have to read a number of records
to find the record to update. Rdb does not know that at time of reading,
so it protects the records read from updates by others until you commit.

But yes, if your are in a "read only" transaction, the locking gets
different (more relaxed) from when you are in a "read write" transaction.

> While there just may be some reason to not read 
> locked data, that is not normally the case.  There is great efficiency in 
> skipping the locking when it is not needed.
>

Rdb never locks anything when it is not needed. Of course.

> RMS has the "read regardless" capability.  This is one example.
> 

That is "ISOLATION LEVEL READ COMMITTED". Not usually used, you do not
want to see others commited data as long as you are within the same
own transaction. The default is "ISOLATION LEVEL SERIALIZABLE" where
you always see the same data no matter others updates until you
commit and start a new transaction of your own.

This is not unique, it is part of the transactional database world.

An example from "SQL$ HELP SET_TRANSACTION EXAMPLES":

SQL> SET TRANSACTION READ WRITE RESERVING
cont>      EMPLOYEES FOR PROTECTED WRITE,
cont>      JOBS, SALARY_HISTORY FOR SHARED READ;

So you tell Rdb what operations you expect to perform against
differnt tables. Rdb will use more relaxed locking strategies against
the JOBS and SALARY_HISTORY tables than aginst the EMPLOYEES table.






>