Re: Usage of RowId...

I didn't know about the SQL SERVER/SYBASE. Thanks.

For Oracle 8, I think we can always use Update triggers to do the needful.

Ravi

zfred wrote:
>
> I don't like that idea because it essentially requires two updates to
> achieve one update,
> I think i see the potential for a race condition....
> SQL SERVER/SYBASE have the perfect solution in their timestamp column,
>
> the server itself generates a unique number for the row every time it
> updates,
> therefor if everone is reading the timestamp, and everyone updates
> using the timestamp,
> it is gaurenteed that ONE update is always sucessful!!! - the others
> fail and need to
> try again.
>
> I do now have the problem of converting the timestamp idea to Oracle 8
> database,
> I don't want to change the application code, any ideas ?
>
>
> Ravishankar Bhat 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
> >
> > 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 )
> > 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
  Received on Mon Nov 23 1998 - 00:00:00 CST