Re: Opinions on approach, please...

On Mon, 26 May 2008 15:00:37 GMT, "William M. Klein"
<wmklein@xxxxxxxxxxxxxxxxx> wrote:

<all previous parts of the thread are snipped>

Pete,
I the reason that I mentioned "locks" is that I am familiar with applications
that INTENTIONALLY used "locks" (of one sort or another) to serialize specific
steps. They didn't use them to "protect" from data corruption. Rather they
would use a "control" record to be "locked" when one
user/part-of-the-application/whatever was doing something to insure that other
parts/users didn't "start" a new process.

Micro Focus and the '02 Standard have "wait" syntax (within READ processing -
and as a separate statement). I couldn't find this in my Fujitsu dox, so this
may not be a problem there. (They do have file status codes for accessing
locked records).

As I say, this is not a question of 'accidentally" having two processing
attempting to update the same record; rather it is the use of specific records
(or files) to serialize processing.
This is possible in some, if not all RDBMS. In case of DB2 for example
one would declare a select WITH RR (or RS) USE AND KEEP UPDATE (or
exclusive) LOCKS.

While it seems simple, it can have a negative effect if the table
space is applying page lock lvl (most common) and not row level lock.
It all comes down to database design taking in consideration what the
application(s) that access same database do, and is never easy to get
it right.


***

I also wanted to explain (a bit) about the "multiple positioning" point that I
tried to make earlier. I don't know if this is used with non-IBM mainframe
COBOLs - or even if it is still used (much) on IBM mainframes.

The techniques was where you had two (or more) File-Control entries - pointing
to the same physical (VSAM) file - one via its "primary" key and one via an
alternate key (or possibly both via separate alternate keys). Using START and
READ NEXT processing, you could then process "simultaneously" through the same
physical file in two different sequences. I could be mistaken, but I don't
think that SQL can (easily) do this via two "simultaneous" cursors for the same
database. However, if this isn't something that you have run into, then it may
well be a non-issue with the types of applications that you would want to
convert.
It is perfectly possible to do it. bad performance most times unless
the cursor specifies a small enough number of records.
e.g. if table_a has following indexes.
ix_1
company
department
client
invoice_number
invoice_line

ix_2
company
department
product_code
invoice_number

It is perfectly possible to declare 2 cursors. one for each index (or
even for same index) and have both open at same time, and scrool in
each one as needed.

Regarding COBOL and updates.
Several cursors can be open at the same time on same table.
Problem arises when a update occurs to that same table.
With standard COBOL indexed files, if any process added a records PAST
the sequence the current process is reading, then the current process
would pick it up.
With SQL this depends on the isolation lvl used when defining the
SELECT.
Most common is CS - cursor stability. This one will NOT read rows have
not yet been commited by other processes. But it will also prevent
other processes from updating the current row of the open cursor.
Only type of isolation lvl that gives exactly the same behaviour as
COBOL is UR - uncomitted read. But this should really not be used by
update programs, as you may get dirty data on the database. Should
only be used by inquiry programs where content of the data is not that
important.

This may or not be important on Pete case. Without knowing exactly how
their current application processes its files, and what type of file
structure they have it is impossible to know if they will find this
type of problem. or even others.


Frederico Fonseca
ema il: frederico_fonseca at syssoft-int.com
.

Relevant Pages

  • Re: Multiple problems with a running job
    ... SQL Server MVP ... > database that I know is unchanging), making it impossible for me to run ... I'm declaring the cursors as LOCAL STATIC. ... > to prevent locks. ...
    (microsoft.public.sqlserver.programming)
  • Re: Opinions on approach, please...
    ... that INTENTIONALLY used "locks" to serialize ... It all comes down to database design taking in consideration what the ... It is perfectly possible to declare 2 cursors. ... With standard COBOL indexed files, if any process added a records PAST ...
    (comp.lang.cobol)
  • Re: Cursor in a trigger
    ... All the locks that your transaction has taken ... out are held for the durtion of the trigger, and cursors are slow, so the ... > Is it a good thing to put a Cursor in a trigger? ...
    (microsoft.public.sqlserver.programming)
  • Multiple problems with a running job
    ... I've got a stored procedure that essentially consists of about a half dozen ... database that I know is unchanging), making it impossible for me to run ... I'm declaring the cursors as LOCAL STATIC. ... to prevent locks. ...
    (microsoft.public.sqlserver.programming)
  • Re: Single database transaction across a multiple HTTP requests?
    ... Either this transaction is poorly designed, or you entire systems of stored ... You will need to change one or the other until you obtain a design that will ... > database engine. ... It is normal practice to issue the locks manually just before performing the ...
    (comp.lang.php)