Обсуждение: Stored Procedures
We are currently switching to stored procedures for a lot of our database activity. The question has come up about the transactional nature of the stored procedures. I was wondering if stored procedures can have transactions in them or if you must start the transaction in your code and call the stored procedure from there to get the safety of a transaction?
Thanks
Kent Anderson
EZYield.com
407-629-0900
========================================
This electronic message transmission contains information from the Company that may be proprietary, confidential and/or privileged. The information is intended only for the use of the individual(s) or entity named above. If you are not the intended recipient, be aware that any disclosure, copying or distribution or use of the contents of this information is prohibited. If you have received this electronic transmission in error, please notify the sender immediately by replying to the address listed in the "From:" field.
On Tue, Sep 21, 2004 at 07:41:31AM -0400, Kent Anderson wrote: > We are currently switching to stored procedures for a lot of our database > activity. The question has come up about the transactional nature of the > stored procedures. I was wondering if stored procedures can have > transactions in them or if you must start the transaction in your code and > call the stored procedure from there to get the safety of a transaction? There's only one transaction (whether it's an explicit transaction block or an implicit one), and the query that invokes the stored procedure is already running inside it. So the stored procedure always has the safety of it, and it can't get out (except by raising an error and aborting the whole thing). The transaction can only be committed _after_ the stored procedure has finished succesfully. -- Alvaro Herrera (<alvherre[a]dcc.uchile.cl>) Jajaja! Solo hablaba en serio!
On Tue, Sep 21, 2004 at 11:11:33AM -0700, Chris Travers wrote: > Alvaro Herrera wrote: > > >There's only one transaction (whether it's an explicit transaction block > >or an implicit one), and the query that invokes the stored procedure is > >already running inside it. So the stored procedure always has the > >safety of it, and it can't get out (except by raising an error and > >aborting the whole thing). The transaction can only be committed > >_after_ the stored procedure has finished succesfully. > > I am assuming that save points would still work as advertised in stored > procedures.... Not at all. What you actually use is exception blocks. -- Alvaro Herrera (<alvherre[a]dcc.uchile.cl>) "El sentido de las cosas no viene de las cosas, sino de las inteligencias que las aplican a sus problemas diarios en busca del progreso." (Ernesto Hernández-Novich)
Alvaro Herrera wrote: >On Tue, Sep 21, 2004 at 07:41:31AM -0400, Kent Anderson wrote: > > >>We are currently switching to stored procedures for a lot of our database >>activity. The question has come up about the transactional nature of the >>stored procedures. I was wondering if stored procedures can have >>transactions in them or if you must start the transaction in your code and >>call the stored procedure from there to get the safety of a transaction? >> >> > >There's only one transaction (whether it's an explicit transaction block >or an implicit one), and the query that invokes the stored procedure is >already running inside it. So the stored procedure always has the >safety of it, and it can't get out (except by raising an error and >aborting the whole thing). The transaction can only be committed >_after_ the stored procedure has finished succesfully. > > > I am assuming that save points would still work as advertised in stored procedures.... Best Wishes, Chris Travers Metatron Technology Consulting