RDB problem

[#1]

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

[Karl Rohwedder]

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

[Karl Rohwedder]

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

[#4]

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

[Willem Grooters]

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.

[John Gillings]

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.

[Jean-François Piéronne · Accepted solution]

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

[#8]

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

[Jean-François Piéronne]

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

[#10]

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.