Обсуждение: Database Integrity
I need to delete several records from a table so my query is composed of a DELETE command with a condition, something like the following:
DELETE FROM tblTable WHERE field1 = something
Do I need to use a transaction in an operation like this to be sure the database remains integral in case of an error?
Respectfully,
Jorge Maldonado
The purpose of using a transaction is so that if you run your delete statement and realize that you've deleted the wrong rows, you can roll the transaction back and get those rows back. You can then modify your query and try again and when you finally get it right, issue a commit command. Hope that answers your question. On 11/18/11 2:32 PM, JORGE MALDONADO wrote: > I need to delete several records from a table so my query is composed > of a DELETE command with a condition, something like the following: > > DELETE FROM tblTable WHERE field1 = something > > Do I need to use a transaction in an operation like this to be sure > the database remains integral in case of an error? > > Respectfully, > Jorge Maldonado
On Fri, 18 Nov 2011 13:32:03 -0600 JORGE MALDONADO <jorgemal1960@gmail.com> wrote: > I need to delete several records from a table so my query is composed of a > DELETE command with a condition, something like the following: > > DELETE FROM tblTable WHERE field1 = something > > Do I need to use a transaction in an operation like this to be sure the > database remains integral in case of an error? AFAIK no because even if you're not in your transaction, Pg opens its own (hence rollback if something bad happens). -- Living on Earth may be expensive, but it includes an annual free trip around the Sun.
On Fri, November 18, 2011 2:34 pm, Ramy Abdel-Azim wrote: > The purpose of using a transaction is so that if you run your delete > statement and realize that you've deleted the wrong rows, you can roll > the transaction back and get those rows back. You can then modify your > query and try again and when you finally get it right, issue a commit > command. In other words: What do you mean by 'database integrity'? If all you are asking is if the database will still return queries, you don't need the transaction. If you need those queries to return some consistent logical state that the delete would have affected, you probably do need the transaction. If your application can run correctly with 'half' of the delete run, you don't need to be sure the whole delete ran. If it can't, here's a tool to make sure either the whole delete ran or none of it did. The choice for which you need is up to you, and depends on what you are doing. ;) Daniel T. Staal --------------------------------------------------------------- This email copyright the author. Unless otherwise noted, you are expressly allowed to retransmit, quote, or otherwise use the contents for non-commercial purposes. This copyright will expire 5 years after the author's death, or in 30 years, whichever is longer, unless such a period is in excess of local copyright law. ---------------------------------------------------------------
On Fri, 18 Nov 2011 20:44:33 +0100 "Jean-Yves F. Barbier" <12ukwn@gmail.com> wrote: > > AFAIK no because even if you're not in your transaction, Pg opens its own (hence > rollback if something bad happens). Oops: forgot to say that in this case, you'll loose the concurrency you could have with SET TRANSACTION SERIALIZABLE, which is bad if there are many concurrent DELETEs & users making them. -- Evening hours "all clear" for romance! (Tell mate you have to work late.)