| Oracle FAQ | Your Portal to the Oracle Knowledge Grid | |
 |
 | |||
| Oracle FAQ | Your Portal to the Oracle Knowledge Grid | |
|
| |||
Home -> Community -> Usenet -> c.d.o.misc -> Re: lock Oracle db objects, but only a lock for others?
"Eric Fortin" <emfortin_at_attbi.com> wrote in message news:<3%Joa.551141$F1.76351_at_sccrnsc04>...
> "Bob Kilmer" <rprgrmr_at_yahoo.com> wrote in message
> news:FbHoa.222550$o8.3779878_at_twister.tampabay.rr.com...
> > "Daniel Morgan" <damorgan_at_exesolutions.com> wrote in message
> > news:3EA32BBB.1A412583_at_exesolutions.com...
> > > Eric Fortin wrote:
> > >
> > > > "Daniel Morgan" <damorgan_at_exesolutions.com> wrote in message
> > > > news:3EA31A5F.C26DAB1A_at_exesolutions.com...
> > > > > > > On Error Goto
> > > > > >
> > > > > > What is the correct way to Error Handle in VB6?
> > > > >
> > > > > To handle the errors in the back-end, or middle-tier ifone exists:
> Not
> in
> the
> > > > > front-end. VB should only be used to display messages returned from
> the
> > > > serer.
> > > >
> > > > >
> > > > > > >
> > > > > > > If things like this have been written expect that the entire
> code
> base
> needs to
> > > > > > > be rewritten.
> > > > > > >
> > > > > >
> > > > > > Can you please give an example of a correct construction for VB6
> and
> Oracle?
> > > > > >
> > > > > > (or is it the order, begin trans followed by error handling)
> > > > > > (or is it that vb6 should not be used with Oracle)
> > > > > >
> > > > > > I guess I'm not clear on how it should be done.
> > > > > >
> > > > > > Thanks.
> > > > >
> > > > > No problem. I teach an entire certificate program, three quarters,
> at
> the
> > > > > University of Washington on the subject. So I'll just include all of
> that
> > > > > information here in this usenet post
> > > > >
> (http://www.outreach.washington.edu/extinfo/certprog/oad/oad_crs.asp)
> just
> > > > > kidding. Seriously this is not
> > > > > something that can be done generically. Just to give you an example
> of
> what I
> > > > > would consider minimal information required to construct Oracle
> error
> handling
> > > > > ... I would say that you must understand all of the following:
> > > > >
> > > > > TooManyDays EXCEPTION;
> > > > > EXCEPTION WHEN TooManyDays THEN
> > > > > EXCEPTION WHEN NO_DATA_FOUND THEN
> > > > > EXCEPTION WHEN OTHERS THEN
> > > > > PRAGMA EXCEPTION_INIT
> > > > > RAISE;
> > > > > RAISE_APPLICATION_ERROR(-20001, 'Date Can Not Be In The Future');
> > > > > AUTONOMOUS TRANSACTIONS
> > > > >
> > > > > And likely I'll think of something I missed as soon as press the
> SEND
> button.
> > > > >
> > > > > Daniel Morgan
> > > > >
> > > >
> > > > On error goto is in the VB6 code
> > > >
> > > > if that exception is thrown in the db and not handled in the VB app,
> the
> vb
> > > > app will get a terminating ora-440 error. (Then, especially if no
> begin
> > > > trans, all the sql up to that point will be auto committed and the sql
> after
> > > > that 440 error will never be executed)
> > > >
> > > > Here is the Oracle Doco ( bottom of the page): on how to do it with
> Ora
> OLE
> > > > DB.
> > > >
> > > >
> >
> (http://download-west.oracle.com/docs/cd/B10501_01/win.920/a95498/using.htm#
> > > > 1006186)----code sample:Dim Oracon As ADODB.Connection
> > > > Dim recset As New ADODB.Recordset
> > > > Dim cmd As New ADODB.Command
> > > > Dim param1 As New ADODB.Parameter
> > > > Dim param2 As New ADODB.Parameter
> > > > Dim objErr As ADODB.Error
> > > > Dim Message, Title, Default, EmpNoValue
> > > >
> > > > Message = "Enter an employee number (5000 - 9000)"
> > > > Title = "Choose an Employee"
> > > > Default = "7654"
> > > >
> > > > On Error GoTo err_test
> > > >
> > > > EmpNoValue = InputBox(Message, Title, Default)
> > > > If EmpNoValue = "" Then Exit Sub
> > > > If EmpNoValue < 5000 Or EmpNoValue > 9000 Then EmpNoValue = 7654
> > > >
> > > > Set Oracon = CreateObject("ADODB.Connection")
> > > > Oracon.ConnectionString = "Provider=OraOLEDB.Oracle;" & _
> > > > "Data Source=exampledb;" & _
> > > > "User ID=scott;" & _
> > > > "Password=tiger;"
> > > >
> > > > Oracon.Open
> > > > Set cmd.ActiveConnection = Oracon
> > > > Set param1 = cmd.CreateParameter("param1", adSmallInt, adParamInput, ,
> > > > EmpNoValue)
> > > > cmd.Parameters.Append param1
> > > > Set param2 = cmd.CreateParameter("param2", adSmallInt, adParamOutput)
> > > > cmd.Parameters.Append param2
> > > >
> > > > ' Enable PLSQLRSet property
> > > > Cmd.Properties ("PLSQLRSet") = TRUE
> > > >
> > > > cmd.CommandText = "{CALL Employees.GetDept(?, ?)}"
> > > > Set recset = cmd.Execute
> > > >
> > > > ' Disable PLSQLRSet property
> > > > Cmd.Properties ("PLSQLRSet") = FALSE
> > > >
> > > > MsgBox "Number: " & EmpNoValue & " Dept: " &
> recset.Fields("deptno").Value
> > > >
> > > > Exit Sub
> > > >
> > > > err_test:
> > > > MsgBox Error$
> > > > For Each objErr In Oracon.Errors
> > > > MsgBox objErr.Description
> > > > Next
> > > > Oracon.Errors.Clear
> > > > Resume Next
> > > >
> > > > ----End code sample.
> > > >
> > > > Is this incorrect?
> > >
> > > Properly handled errors in the database never produce errors in a
> front-end.
> > > That you are handling errors as you are indicates the database is not
> handling
> > > them: Nothing more.
> > >
> > > Daniel Morgan
> > >
> > >
> >
> > I only mentioned error handling at all (On Error Goto!) to emphasize that
> it
> > is so difficult in this app to be sure that a lock is on or off, it is
> > necessary to be ready for an error. Whether it is handled in VB or Oracle
> or
> > by careful routing of the spaghetti or at all is beside my point.
> >
> > The code I walked into looks something like the following. Even if you
> have
> > never heard of Oracle, you can see that this is a bad idea.
> >
> > (If I might borrow the VB code context of an earlier post)
> >
> > '(__calling code__)
> > Set Oracon = CreateObject("ADODB.Connection")
> > Oracon.ConnectionString = "blah, blah, not important"
> > Oracon.Open
> > Set cmd.ActiveConnection = Oracon
> > cmd.ActiveConnection.BeginTrans
> > cmd.CommandText = _
> > "{Begin Select colName From tblName For Update; End;}"
> > blnLockIsOn = True
> > 'Do stuff
> > 'call other code
> >
> >
> > '(__called code__)
> > 'rollback to end "select for update" lock
> > 'so this routine can access the data
> > If blnLockIsOn then
> > cmd.ActiveConnection.RollbackTrans
> > blnLockIsOn = False
> > End If
> > '
> > 'Do stuff,
> > 'including selects, updates, inserts, whatever
> > '
> > cmd.ActiveConnection.BeginTrans
> > cmd.CommandText = _
> > "{Begin Select colName From tblName For Update; End;}"
> > blnLockIsOn = True
> > 'return to caller
> >
> >
> > '(__back to calling code__)
> > 'do stuff
> > 'call other code
> > 'setting and removing locks all the while until
> > 'eventually, somewhere, hopefully.
> > If blnLockIsOn then
> > cmd.ActiveConnection.RollbackTrans
> > blnLockIsOn = False
> > End If
> >
> > So, given that this is bad, I want to know how best to lock what needs to
> be
> > locked, if it needs to be locked at all (if not, I don't), in such a way
> as
> > to assure reasonable security (or concurrency, or whatever - you Oracle
> > types can help me out here) for the data while simplifying the code and
> code
> > maintenance until such time as this company Does The Right Thing and
> > rewrites the entire code base, carefully coached by duely qualified Oracle
> > gurus. That is all.
> >
> > I feel like, on the whole, I am getting the run around here. I can
> > appreciate that I may be ignorant of the scope of my ignorance, but I have
> > to start somewhere, and that has been by reading as much of the plentiful
> > documentation available to me as I can and posting what I had hoped would
> be
> > taken as a well worded question. Since I cannot hire a consultant and
> > because the company is not likely to hire an Oracle consultant soon, at
> > least not for this problem, one that they have been willing to live with,
> I
> > will continue reading. If anyone here has any more helpful pointers, such
> as
> > the list noted by Daniel, and the "Oracle Concurrency Model" noted by
> Simeon
> > Rice, I'll make good use of it.
> >
> > Thanks,
> > Bob Kilmer
> >
> >
>
>
> I didn't comprehend your situation the first time, so let me make sure I
> understand.
>
> 1. You have a set of client workstations
> 2. One client accesses data for update (the for update is so no one else
> can access it)
> 3. other client workstations [may] try to access that data, and your code
> (at least maintenance wise) uses a flag that says someone else is updating
> the record so exit the procedure and rollback any transactions (and probably
> gives a try later message). (The flags are actually in the VB code?)
> 4. You want to know what to do about these flags/locks to clean the code?
>
> The simple answer is that oracle has exceptional row level locking (unlike
> sql server).
>
> Thus,
>
> you could eliminate the flagging altogether.
>
>
> ie:
> You could create a recordset
>
> select [cols] from emp [ + where clause] for update
>
>
> All the rows (and only those rows) retreived in the recordset will be locked
> to that user that first accesses them
>
> When the second (and third, and forth, etc) users run that same sql, they
> will not get access to those rows until the previous recordset releases
> them. Oracle handles all the locking for you. You have to do nothing. The
> subsequent client ws (workstation) just waits for the previous client ws to
> complete.(Oracle's row level locking is so fine grained, that user1 can get
> one set of rows, lock them, and user 2 can get a different set of rows and
> lock them without affecting either user --as long as the recordsets
> (cursors, in oraclespeak) do not access any of the rows in another active
> cursor).
>
> However, there is one potential problem:
>
> The second, third, (...) client ws all 'hang' (Not Responding, in the task
> manager), waiting for the previous recordset to clear when one of the
> retreived rows is already locked by a previous recordset. The only way to
> get the machine to 'respond' is end the task or wait for the previous user
> to commit, (update in some cases) or rollback. In the real world, this can
> be a problem: ie: the first user gets the records, two other users
> subsequently execute the same query, the workstation hangs on users 2 and 3
> (because the first user has them), and during this process, user 2 decides
> to get a cup of coffee and restroom, and then gets side tracked. User 1
> releases the recordset, and because the locks are created and released FIFO,
> user 3 (who really needs the records) has to wait and wait until user 2
> gets back or someone kills the session.
>
> If it were me, I would not mess with the flags you are talking about
> (impossible to read and maintain and debug). Let oracle handle the locking,
> and force the person in charge to enforce the no walking away rules.
>
> If it is really a problem, and you get 'Not Responding' complaints, I'm
> afraid you do need some redesign. (and not of the flag sort)--(and, btw, in
> this case the begin-commit trans is essential, as you will have users that
> will do the ctrl-alt-delete and end the task after a millisecond passes).
>
>
> Also, Oracle is smart enough not to allow the same application to lock
> itself out (deadly embrace). (however, if you execute the same for update
> query in a vb6 app and sqlplus on the same workstation at the same time,
> oracle will lock the records appropriately!)
>
> Hopefully this helped a little.
>
> E
Eric,
> 1. You have a set of client workstations
Yes. One (software) client per workstation, usually a few workstations, to at most 30. It's a two tier app. The two tiers are the VB client and an Oracle backend.
> 2. One client accesses data for update (the for update is so no one else
> can access it)
Yes. The 'for update' locks a row in a table I'll call the primary table. It is the table that contains the primary identification information for the entity whose properties are being stored, tracked, updated. The row locked is the row in teh primary table that stores the info representing the entity whose info is being edited.
> 3. other client workstations [may] try to access that data, and your code
> (at least maintenance wise) uses a flag that says someone else is updating
> the record so exit the procedure and rollback any transactions (and probably.
> gives a try later message). (The flags are actually in the VB code?)
Not exactly.
Allow me to describe an intake process in some detail. In an intake process, the user opens an intake VB form and enters a unique identifying number already assigned by an external business system. The code updates a client_id/entity_id table with the entity id to inform other clients (who will check this table before an intake) that it will be handling this intake entity. The user enters additional required information in the still open intake form. They click "Finish," which does not close the intake form, but does trigger a process whereby this form info is inserted (literally) into the primary table and other tables in the DB. The id is then removed from the client_id/entity_id table. The code then begins a transaction on a perpetually open connection and issues a 'select for update' to lock the entity row just inserted. After clicking "Finish", the user has the option to enter additional information by opening other forms from buttons on the intake form. Because of the way things are - not necessarily because experts would agree this is a good idea - the code supporting these forms may need to access data locked by the 'select for update'. So, the code in any new form opened will issue a rollback to negate the lock while it modifys data it needs to modify. Then, the form code will begin a transaction and issue another 'select for update' and return control to the calling routine. Once the user actually ends the intake process, a final rollback releases the lock on the entity.
Competition for the same data is of little concern because, among other things, almost all of data modified is directly associated with a physical entity or a unique physical process. Two clients would ordinarily try to access the same data only accidentally, say, by virtue or a data entry error - entering the wrong entity id number, for instance.
> 4. You want to know what to do about these flags/locks to clean the code?
Pretty much, yes.
I want to be as free as possible from having to lock and unlock database objects explicitly. If I must lock db objects explicitly, I want to lock them at the beginning of an editing session (an intake process, say), still giving the editing session on the client holding the lock complete access to data that is otherwise locked to others, and then unlock them at the end. If I can rely on implicit locking, so much the better. I can see a use for 'select for update' if an update were actually pending, but having to set and unset this lock over and over again across several code modules for the same overall process is a bit much to easily keep track of.
I had an idea that it would be sufficient for clients to record something in a table in the database to act as a flag to other clients that indicates that a specific client is modifying data related to a particular entity. I fully believe this would work, because I do not believe that the locks currently being placed truly lock all the data that is related to the entity being edited - the existence of the lock merely acts as a warning to the other clients that a particular entity is being edited, and, as a side effect, gets in the way of the legitimate business of the client holding the lock. As is, every client checks explicitly for a lock on an entity before proceeding. If this is true, then it seems to me that a value in some table that represents the fact of an entity being edited on a client would be simpler but just as effective. A client need only set this value when it intends to edit an entity (if it is not already off limits), do its thing, then unset it at the end. Other clients could always check this 'indication of editing' as they do now (flag or lock, who cares?).
Please don't misunderstand me. I am just a problem solver looking for a cost-effective fix. I suspect that the previous paragraph subverts the purpose of a relational database, but it would be perfectly trivial to implement and it would obtain a few orders of magnitude improvement in data "locking" and code simplicity over what we have now. Perhaps there is a reasonable method using a legitimate database oriented solution - no doubt, there is. I'd like to discover it.
I have been asked to insert even more intake options into this house of cards. I am motivated to simplify this process because it will make my task easier and less error prone, not to mention that it will be good for the code in the long run and might provide a marginally better model for future development. I cannot hope to re-engineer the complete application because improving data access practices and simplifying the coding of the same is simply not perceived as a primary concern at this time.
Thanks for listening. And thanks to anyone who reads this far. I do not expect you to do my work. Your voluntary, positive response will be appreciated.
Bob Kilmer Received on Mon Apr 21 2003 - 10:17:50 CDT
 |
 |