Hello Vijay, Thanks for your mail. I know I can synchronize the transactions using "synchronized" method in Java or use semaphores. This will avoid the row-level locks, but,I understand it may cause performance bottlenecks when there are many concurrent users. I always update the tables in the same order.So, there is no question of cyclic issue. The issue here is concurrent users may update the same rows of the concerned table, and at some point, the database cannot handle the updates, depending on the number of users.
Do you think using a stored procedure for the transaction will help? Thanks, Shyam --- "Balakrishnan, Vijay" <[EMAIL PROTECTED]> wrote: > Hi Shyam, > > You can synchronise the whole transaction with the 2 > updates.That will take > care of 1 of the issues.The other issue might be > cyclic i.e are you updating > table C first and then updating table A or B > somewhere in your > application.You will then have to make the updates > in the same order i.e > always update A first before C.This issue is a wild > guess of mine-so I might > be way off base here depending on row level locking > etc. > > Vijay > > -----Original Message----- > From: Shyam A [mailto:[EMAIL PROTECTED] > Sent: Monday, March 08, 2004 6:36 PM > To: Struts Users Mailing List > Subject: Re: [OT] Database locking and deadlock > > > Vic, > > Yes, I do get a "deadly embrace" or deadlock, as the > row locks on the table multiply when they occur, > holding up connections in the connection pool, > eventually consuming all connections, and then all > hell breaks loose. > > Were you referring to synchronizing with semaphores? > > I read somewhere that the Oracle database is > supposed > to handle deadlocks by rolling back one of the > affected transactions. But the row locks seem to > occur > intermittently, possibly when there are large > numbers > of concurrent users. > > I cannot avoid the transaction as the database > updates > have to be done as one unit. An alternative that > comes > to mind is using a stored procedure for the update > operations. Anyway, I need to do some research on > passing Java objects to stored procedures. > > Thanks, > Shyam > > > > --- Vic Cekvenich <[EMAIL PROTECTED]> > wrote: > > Do you have a deadly embrace? > > Maybe just syncronise w/ semaphorse or the like. > > .V > > > > Shyam A wrote: > > > Vic, > > > > > > Thanks again. I'm not in a position to switch to > > > IBatis as my application is currently in use. > > Right > > > now, I'm manually killing the user sessions > which > > lock > > > up the table in the database. My database is > > Oracle > > > 9i, and this problem seems to occur only with > > large > > > numbers of concurrent users. In my DAO,I update > > three > > > tables as part of a transaction - table A and/or > > table > > > B (in loops), and table C. Commit operation is > > done > > > only after all tables are updated successfully. > > So, it > > > may be possible that the DBMS locks up rows of > > table A > > > or B (before commit) and waits until it updates > > table > > > C for the commit. Is this the cause of the > > problem? > > > > > > I've not been able to determine (using SQl Plus, > > TOAD > > > ) which query is causing the row-level lock. > > > > > > Any ideas? > > > > > > Thanks, > > > Shyam > > > > > > --- Vic Cekvenich <[EMAIL PROTECTED]> > > wrote: > > > > > >>Is there a way you can reproduce this w/ iSQL > > > > > > > --------------------------------------------------------------------- > > To unsubscribe, e-mail: > > [EMAIL PROTECTED] > > For additional commands, e-mail: > > [EMAIL PROTECTED] > > > > > __________________________________ > Do you Yahoo!? > Yahoo! Search - Find what you're looking for faster > http://search.yahoo.com > > --------------------------------------------------------------------- > To unsubscribe, e-mail: > [EMAIL PROTECTED] > For additional commands, e-mail: > [EMAIL PROTECTED] > > --------------------------------------------------------------------- > To unsubscribe, e-mail: > [EMAIL PROTECTED] > For additional commands, e-mail: > [EMAIL PROTECTED] > __________________________________ Do you Yahoo!? Yahoo! Search - Find what you’re looking for faster http://search.yahoo.com --------------------------------------------------------------------- To unsubscribe, e-mail: [EMAIL PROTECTED] For additional commands, e-mail: [EMAIL PROTECTED]
