CODEWITHSUNDEEP
javasqljakarta-ee
Landister
Landister

Reputation: 2224

SQL multi-command atomicity question

I am trying to create a program that updates 2 different tables using sql commands. The only thing I am worried about is that if the program updates one of the tables and then loses connection or whatever and does NOT update the other table there could be an issue. Is there a way I could either

A. Update them at the exact same time

or

B. Revert the first update if the second one fails.

Upvotes: 3

Views: 234

Answers (3)

OMG Ponies
OMG Ponies

Reputation: 332581

Depending on the database, I'd suggest using a stored procedure or function based on the operations involved. They're supported by:

  • MySQL
  • Oracle
  • SQL Server
  • PostgreSQL

These encapsulate a database transaction (atomic in nature -- it either happens, or it doesn't at all), without the extra weight of sending the queries over the line to the database... Because they already exist on the database, the queries are parameterized (safe from SQL injection attacks) which means less data is sent -- only the parameter values.

Upvotes: 2

kwantam
kwantam

Reputation: 441

Most SQL servers support transactions, that is, queueing up a set of actions and then having them happen atomically. To do this, you wrap your queries as such:

START TRANSACTION;
 *do stuff*
COMMIT;

You can consult your server's documentation for more information about what additional features it supports. For example, here is a more detailed discussion of transactions in MySQL.

Upvotes: 1

jzd
jzd

Reputation: 23629

Yes use a SQL transaction. Here is the tutorial:JDBC Transactions

Upvotes: 8

Related Questions