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: Brian K. Lawton <NOSPAM.Lawton_at_RDAConsultants.Com>
Date: 1998/04/16
Message-ID: <6h5i4r$9cl$1@ha2.rdc1.md.home.com>#1/1

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
Received on Thu Apr 16 1998 - 00:00:00 CDT

Original text of this message

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