Oracle FAQ Your Portal to the Oracle Knowledge Grid
HOME | ASK QUESTION | ADD INFO | SEARCH | E-MAIL US
 

Home -> Community -> Usenet -> c.d.o.server -> Re: Usage of RowId...

Re: Usage of RowId...

From: Nuno Guerreiro <nuno-v-guerreiro_at_invalid.telecom.pt>
Date: 1998/11/09
Message-ID: <3646bd03.510870703@news.telecom.pt>

On Thu, 05 Nov 1998 14:26:18 -0600, Ravishankar Bhat <rshankar_at_oz-leccit.corp.fedex.com> wrote:

>In stateless and contask free environment,
>We will have this problem of locking. So it is always better have our
>application deal with this because databases do not support this.
>
>Just to share the other ways of doing things,
>
> 1. Database will have 3 more columns for in table . One is
>Transaction User name and Transaction time is the other and Transaction
>flag is the another.
> 2. Before starting any operation user will speify what operation he
>intend to do. ( Like, Add, Update, View and Delete ).
> 3. ADD and VIEWS will not have any problem as they do not need the
>lock on the rows.
> 4. If any user wants to update the database then, he need to send
>Flag as Update, Key values to the row and the user ID
> 5. Now database will take these values, takes that row and updates

How does the database take these values? It updates the row the user is accessing?

> Transaction user ID = User ID
> Transaction time = System time
> Transaction flag = "YES"
> Then the information will be displayed on User Screen for Update.
> ( Remember Row is not locked by the database yet, but has the user
>ID )

If the row was updated in order to update the above fields, then this row is already locked.

> 6. User changes the information and sends back the deatails. Database
>checks his User Id with database user ID, also checks the time and
>allows him to update the row. Now user gets the Lock on the row and
>updates immediatly.
>At end it updates the transaction flag as NO
>
>Now,
> If any other person wants update the row at after stage 5,
> Then, Database checks Transaction flag and then user id and then
>time.
> As Transaction flag is YES and User is different, Database issues an
>error message to wait.
>Now
> If User who has done till stage 5, leaves with out doing stage 6,
> Database still has the transaction flag as YES.
> Then, other other user comes for update
> Database checks for Transaction Flag first, then checks the
>Transaction time.
> If the time is more than a specified number of seconds, it will
>aloows the user2 for update.
> That means it updates the User ID, transaction Time and Transaction
>Flag again and allows
> user2 the control.
>
>
>Cheers
>Ravi
>
>Brian K. Lawton wrote:
>>
>> When the database connection is persisted, I agree with everything Thomas
>> and Nuno said. However, in a stateless environment, I fail to see how this
>> will work. When a query is issued over the inter/intranet, there is no
>> guarantee that as to what will happen next. The user may or may not issue
>> an update. Furthermore, the query and the update will most likely both
>> happen under separate database connections.
>>
>> If I'm totally misunderstanding how Oracle behaves in a stateless
>> environment, please forgive me. I'm rather new to Oracle. For the last 5
>> years, I've been primarily using SQL Server and Sybase. My Oracle
>> experience has always been in a statefull environment.
>>
>> To solve our problem, we are going to add an numeric column to each table.
>> During the initial query, we will return the value of this column to the
>> client. Later, if the client decides to update the row, we will compare the
>> client's value against what is stored in the row. If they are the same, we
>> will allow the update and then to indicate that the row has changed, we will
>> increment the row's value. If the row's value is different than what the
>> client originally stored, we will force the client to refresh and try again.
>>
>> See my additional comments below.
>>
>> This has been a great thread with a lot of great responses. Thanks to
>> everyone for their input!! I've certainly learned a lot!
>> ________________________________________________
>> Brian K. Lawton (mailto:Lawton_at_RDAConsultants.com)
>> RDA Consultants Limited (http://www.RDAConsultants.com)
>>
>> Nuno Guerreiro wrote in message <3536299c.4250682_at_news.telecom.pt>...
>> >On Wed, 15 Apr 1998 18:55:45 -0400, "Brian K. Lawton"
>> ><NOSPAM.Lawton_at_RDAConsultants.Com> wrote:
>> >
>>
>> !!!snip!!!
>>
>> >
>> >>For example:
>> >>
>> >>Step #1: Two users (call them U1 & U2) both select row #9 (call it R9)
 out
>> >>of table X (call it TX). We are in a stateless environment, so no locks
 are
>> >>acquired. By this I mean that the users issue their queries via a middle
>> >>layer (an intranet server) therefore, a lock is not held between the read
>> >>and update.
>> >>
>> >>Step #2: Based on the values selected, U1 updates R9 and commits the
>> >>changes. R9 now becomes R9+.
>> >>
>> >>Step #3: Meanwhile, U2 attempts to update R9 however the database (or
>> >>update statement) releases that R9 has been changed (its now R9+) and
 rolls
>> >>back U2's update.
>> >
>> >If U2 attempts to update R9 after it has been updated and commited by
>> >user U1 like you wrote, then no rollback will take place, since after
>> >U1 issues the COMMIT statement, the row is no longer locked by U1.
>> >This way, U2 will be able to perform his update normally.
>>
>> Agreed. However data's consistentncy may not be what the user would expect.
>> Use a banking account example. At the same time, my wife and I both attempt
>> to withdrawl money from sererate ATM machines. First, we check our balance.
>> The ATM returns a balance of $100. Seeing the available balance, we both
>> attempt to withdrawl the $100. My wife, being a faster typist them me,
>> enters a withdrawl of $100 and receives the cash. Meanwhile, I also enter a
>> withdrawl of $100. What should happen?
>>
>> Using your example above, I would also receive $100. Applying optimistic
>> concurrency, the ATM should stop and indicate that the actual balance has
>> changed and not allow the withdrawl.
>>
>> >
>> >However, if U2 tries to update R9 after it has been updated by U1 but
>> >not yet commited, then U2 will wait (that's the default behaviour) for
>> >U1 to issue either a COMMIT or ROLLBACK statement and release the
>> >lock. Only after any of these statements is issued will U2 be able to
>> >perform the UPDATE statement.
 

>> >
>>
>> Agreed. During the actual update, it should be the responsibility of the
>> lock manager to restrict access to the row. My point is that in a stateless
>> environment, the lock manager has no ability to tie the initial query to the
>> final update.
>>
>> >
>> >
>> >Good luck,
>> >
>> >
>> >
>> >Nuno Guerreiro

Nuno Guerreiro



As I don't normally appreciate unsolicited commercial e-mail (widely known as SPAM), I encoded my e-mail address. If you want to reply by e-mail, please remove the text added to fool spam software

"The art of arts, the glory of expression and the sunshine that lights the light of letters  is simplicity"

                                                        Walt Whitman Received on Mon Nov 09 1998 - 00:00:00 CST

Original text of this message

HOME | ASK QUESTION | ADD INFO | SEARCH | E-MAIL US