topic RDB problem in Operating System - OpenVMS

RDB problem

Wed, 06 Feb 2008 05:57:57 GMT

Hi,

I am facing a small problem. Its much related to database but since database is of VMS, i thot to post it over here. RDB behaviour is bit different from Oracle DB 9i and 10g.

I have openend session of SQL on two telnet terminal attahced to VMS server.
Using SQL command I attached to the same database in both the terminals. Now, in one terminal I give a command to read a particular record from the database and I got the record displayed on the screen. and then in the second terminal I gave the command to delete the same record from the database but after this it doesn't come to SQL prompt unless and untill I commit from the first terminal.

I just want to know is there a way by which I can avoid it.
Means when I read a document I don't want any other process to be blocked to delete the same document.

Hope I am clear with my problem.

Regards,
ajaydec

Re: RDB problem

Karl Rohwedder — Wed, 06 Feb 2008 06:16:29 GMT

Perhaps you started a read/write transaction on session A, retry with a START TRANSACTION READ ONLY first.

It would help, if you tell us the exact commands used and message from Rdb/VMS.

regards kalle

Re: RDB problem

Karl Rohwedder — Wed, 06 Feb 2008 06:19:48 GMT

Note:

If you are interested in Rdb, a visit to
http://www.jcc.com/listserver.htm
may be helpful. You can subscribe to their rdb-mailing list.

regards Kalle

Re: RDB problem

Wed, 06 Feb 2008 06:48:47 GMT

Hi,

Exact commands are:

On session A:

SQL> attach 'filename audit_db';
SQL> set transaction read only wait reserving avail_table for shared read;
SQL> select * from avail_table where docount_s = 'AYI1U1UT89';

On session B:

SQL> attach 'filename audit_db';
SQL> set transaction read write wait reserving avail_table for shared write;
SQL> del from avail_table where docount_s = 'AYI1U1UT89';

Regards,
ajaydec

Re: RDB problem

Willem Grooters — Wed, 06 Feb 2008 07:01:03 GMT

I'm not a RdB expert - but I'd say that session A should reserve on concurrent WRITE - allowing other processes to access the same record for altering it contents.

On the other hand: I would expect this behaviour to happen :). In respect to additional decisions, it's even a requirement. Though the record itself would not be changed, some data could be tested and lead to certain decisions in session A. Removing the record on that moment in another session would interfere and void the conclusion without even notifying session A.

Re: RDB problem

John Gillings — Wed, 06 Feb 2008 07:12:12 GMT

ajaydec,

That sounds correct to me. Just like RMS, a read transaction puts an implicit lock on the object read. You need to read something else of explicitly UNLOCK the object to do anything else (use COMMIT to do this in RDB). I couldn't see an equivalent of "READ WITH NO LOCK".

I suspect if you remove the "wait reserving" clause, your session B will fail instead of hang.

For this case, adding COMMIT after your SELECT will fix your problem, but if you're looking for a mechanism for session B to be able to always delete whatever it wants, without blocking, regardless of what other processes are doing, I think you need to change "FOR SHARED WRITE" to "FOR EXCLUSIVE".

Note that this type of behaviour is required to guarantee data base integrity.

Re: RDB problem

Jean-François Piéronne — Wed, 06 Feb 2008 08:10:23 GMT

ajaydec,

this should work except if you have disabled snapshot mechanism

You can on another session use the rmu command:
rmu/show lock/mode=block to display what is the ressource locked.
you can also use
rmu/dump/header=param yourdb and verify what is the status of the snapshot mechanism default is
Snapshot mode is NON-DEFERRED

I suspect it's not what you have.

JFP

Re: RDB problem

Wed, 06 Feb 2008 09:26:25 GMT

Hi Jean,

The output is show below:

$ rmu/dump/header=param audit_db
*------------------------------------------------------------------------------
* Oracle Rdb V7.1-500 6-FEB-2008 14:45:30.42
*
* Dump of Database header
* Database: DKB100:[000000.AUDIT_DB]AUDIT_DB.RDB;1
*
*------------------------------------------------------------------------------

Database Parameters:
Root filename is "DKB100:[000000.AUDIT_DB]AUDIT_DB.RDB;1"
Created at 5-FEB-2008 14:36:01.58
Oracle Rdb structure level is 71.0
Maximum user count is 50
Maximum node count is 16
Database open mode is AUTOMATIC
Database close mode is AUTOMATIC
Database will be mapped in process space
All transaction modes are allowed
Prestarted transactions are enabled
Snapshot mode is NON-DEFERRED
Statistics are enabled
Operator notification is disabled
Logical area count is 512
Storage Areas...
- Active storage area count is 12
- Reserved storage area count is 0

" Snapshot mode is NON-DEFERRED ".

Do I need to change it.

Regards,
ajaydec

Re: RDB problem

Jean-François Piéronne — Wed, 06 Feb 2008 10:09:34 GMT

ajaydec,
can you reproduce the problem and post the result of
rmu/show lock/mode=block db

you may need a new session or used the session which run the read-only transaction using:
sql> $rmu/show lock/mode=block db

the first "$" character mean spawn the command

You can also use rmu/sh stat db
and go to the screen "process Information"->"Stall Messages" and use the "L" command to view the lock information, if any.

JFP

Re: RDB problem

Wed, 06 Feb 2008 12:38:55 GMT

Hi Jean,

Please find the required information.

$ rmu/show lock/mode=block audit_db
================================================================================
SHOW LOCKS/MODE=BLOCKING Information
================================================================================

--------------------------------------------------------------------------------
Resource: record 84:67:1

ProcessID Process Name Lock ID System ID Requested Granted
--------- --------------- --------- --------- --------- -------
Waiting: 202054F6 _TNA282:....... 5E00A732 00010001 EX
Blocker: 202054F5 _TNA281:....... 550021A6 00010001 PR

Could you help me to find, how to interpret record by number.
Resource: record 84:67:1

Regards,
ajaydec.

Re: RDB problem

Jean-François Piéronne — Wed, 06 Feb 2008 13:02:06 GMT

84:67:1 is the dbkey of the locked record
84 is the logical area id
67 the page number
1 the line number in the page

can you issue under your sql sessions the command
"show transaction"

to be sure that the first is a read only and the second a shared read write

Re: RDB problem

Willem Grooters — Wed, 06 Feb 2008 13:58:02 GMT

Waiting: 202054F6 _TNA282:....... 5E00A732 00010001 EX
Blocker: 202054F5 _TNA281:....... 550021A6 00010001 PR

My assumption is:

PID 202054F6 = 'session B' (delete) and PID 202054F5 = 'session A' (Select); Exclusive access to delete this record (EX) seems rather obvious to me: you'd better be sure the record is'"owned" by the deleter.
Perhaps it would help to remove "reserving table avail_table for shared read" in session A's SQL set transaction command. If that would set a NL (Null) lock, you're ploblem is solved (but as stated before, it depends on what the data is used for later on)

Re: RDB problem

Jean-François Piéronne — Wed, 06 Feb 2008 14:18:04 GMT

The reserving clause in the read only only set a lock on the logical area which will only block a read write reserving the same table in exclusive write mode.

Also if you try to select a table other that the one reserved you will have a error.

A read only don't lock any record of the tables used in the select statement except if you have disabled the snapshot mechanism which is use by Rdb to maintain the correct isolation level (serializable). If you disabled the snap you will have a read committed isolation level for you read only transaction using transient lock on selected record.

Re: RDB problem

Dan Herron — Thu, 07 Feb 2008 13:48:35 GMT

Ajaydec,

Make sure the table you are accessing has a snapshot file associated with it.

Dan Herron

Re: RDB problem

Jean-François Piéronne — Fri, 08 Feb 2008 08:15:17 GMT

A missing snap will generate the following error:
%RDB-F-SYS_REQUEST, error from system services request
-RDMS-F-FILACCERR, error opening storage area file xxxxxx

and a corrupt one generate a bugcheck.

JFP