Home » SQL & PL/SQL » SQL & PL/SQL » want to commit by changes in database after second transaction? (Oracle9i)
want to commit by changes in database after second transaction? [message #564600] Sun, 26 August 2012 08:30 Go to next message
anoopmanagoli
Messages: 20
Registered: August 2012
Location: india
Junior Member
Hi All,

I hv a situation where a webservice interacts with the database.

Here the webservice will first make a request to database for some operation but i dont want the database to commit changes in first request itself. A response will be sent to webservice further a second request will be sent to database for committing the changes. So can that be done?

thanks for your advise in advance.
Re: want to commit by changes in database after second transaction? [message #564603 is a reply to message #564600] Sun, 26 August 2012 08:56 Go to previous messageGo to next message
Michel Cadot
Messages: 59279
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator
A database commits ONLY if you tell you so.

Regards
Michel
Re: want to commit by changes in database after second transaction? [message #564618 is a reply to message #564603] Sun, 26 August 2012 22:06 Go to previous messageGo to next message
anoopmanagoli
Messages: 20
Registered: August 2012
Location: india
Junior Member
Hi Micheal,

My point here is that when a request is sent by the webservice for the first time is performs some operation but until the second request is sent by webservice to commit or rollback, what will happen to the data?

I dont want t atable lock or record to get locked up until the data is commited. So wat shall be the best way to compoly to this.

Please advise.
Re: want to commit by changes in database after second transaction? [message #564619 is a reply to message #564618] Sun, 26 August 2012 22:17 Go to previous messageGo to next message
BlackSwan
Messages: 22839
Registered: January 2009
Senior Member
>I dont want t atable lock or record to get locked up until the data is commited.
>So wat shall be the best way to compoly to this.

Realize & remember that no other session can ever see uncommitted DML.
Re: want to commit by changes in database after second transaction? [message #564621 is a reply to message #564619] Mon, 27 August 2012 00:03 Go to previous messageGo to next message
Michel Cadot
Messages: 59279
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator
... and if you modify a recrd, no one can modify it (or see the modification as BlackSwan said) and the row is locked until you commit (or rollback).

Regards
Michel
Re: want to commit by changes in database after second transaction? [message #564622 is a reply to message #564619] Mon, 27 August 2012 00:03 Go to previous messageGo to next message
anoopmanagoli
Messages: 20
Registered: August 2012
Location: india
Junior Member
ya got that. Request you to help me understand the following scenario
Considering an example:

We have employees table. The table has a column "Salary"
And we have 4 employees in the database.

Now we want to do an activity wherein we want to deduct amount 10000(cumulative) in all from the 4 employees.
EMP1 - 2000
EMP2-3000
EMP3-4000
EMP4- 1000

Finally there needs to only one activity of - 10000 is to be made.


Four requests(emp1-emp4) is sent by web service to deduct the above mentioned amount. but i want the database must not commit the changes. A second request will be sent by web service to database to either commit or rollback depending.

So is this possible and if yes how shalli proceed. Please let me know if the example is un clear i will try mybest to make it comprehensible.


Re: want to commit by changes in database after second transaction? [message #564623 is a reply to message #564622] Mon, 27 August 2012 00:21 Go to previous messageGo to next message
anoopmanagoli
Messages: 20
Registered: August 2012
Location: india
Junior Member
so in the above example as i understand thatthe record will be locked until i either commit or rollback.
But Micheal/Blackswan i cannot afford to put a table lock as so many other things will get impacted. i need an alternate solution to how shall i proceed with above example.

[Updated on: Mon, 27 August 2012 00:28]

Report message to a moderator

Re: want to commit by changes in database after second transaction? [message #564624 is a reply to message #564623] Mon, 27 August 2012 00:28 Go to previous messageGo to next message
BlackSwan
Messages: 22839
Registered: January 2009
Senior Member
>But Mark/Blackswan i cannot afford to put a table lock as so many other things will get impacted.
It is not "table lock"; but row lock.
Two session can not update same record at same time; but readers can always obtain old values.

How can we reproduce this problem that needs to be solved?
Re: want to commit by changes in database after second transaction? [message #564625 is a reply to message #564624] Mon, 27 August 2012 00:31 Go to previous messageGo to next message
anoopmanagoli
Messages: 20
Registered: August 2012
Location: india
Junior Member
yes blackswan thats a row lock am sorry for that. But my aim is to do an activity which i have given in the example. where we will be having 4 requests made. on that example can you just bried me a bit.
Re: want to commit by changes in database after second transaction? [message #564626 is a reply to message #564625] Mon, 27 August 2012 00:37 Go to previous messageGo to next message
BlackSwan
Messages: 22839
Registered: January 2009
Senior Member
Please post reproducible test case, your words just make no sense.
We speak SQL. Please do the same so no misunderstanding exists.
Re: want to commit by changes in database after second transaction? [message #564627 is a reply to message #564626] Mon, 27 August 2012 00:42 Go to previous messageGo to next message
anoopmanagoli
Messages: 20
Registered: August 2012
Location: india
Junior Member
alrite blackswan. Thanks for your time. I will do the needful

[Updated on: Mon, 27 August 2012 00:42]

Report message to a moderator

Re: want to commit by changes in database after second transaction? [message #564628 is a reply to message #564627] Mon, 27 August 2012 01:16 Go to previous message
Michel Cadot
Messages: 59279
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator
Your problem is more an application one than a database one.
The database does not end the transaction (commit or rollback) unless you tell it to do so.
So ensure your application does not commit.
Check your web service code. Most of the time each web service call is an independent transaction.

In addition, note that if you want to update 4 rows you don't need to send 4 updates but only one with the 4 ids.

Regards
Michel

Previous Topic: PL/SQL mutating error (IFS)
Next Topic: log of a stored procedure
Goto Forum:
  


Current Time: Wed Oct 01 00:02:11 CDT 2014

Total time taken to generate the page: 0.10099 seconds