What is a database transaction?

Question

Can someone provide a straightforward, yet not oversimplified explanation of a transaction as applied to computing?

Answer 1

If you need to run some queries in sequence, if all of them have to be executed or none of them should be executed, we wrap those queries inside a transaction. Classic example, sending money from A to B. we have run an "UPDATE" query to update the user A balance and run another "UPDATE" query to update the user B balance.

If you have a MySQL or Postgres set up on your pc, open a new workspace tab, you can start a transaction with BEGIN in both dbs. This will create an isolated workspace. Any query you run inside here, will not be seen in other open tabs. For example if you have accounts table of users in a banking app, you run this command in the isolated workspace where you started a transaction:

UPDATE accounts
SET balance=balance-100
WHERE name="yilmaz"

In any other workspace, if you run this query

 SELECT * FROM accounts WHERE name="yilmaz"

you will not see the updated result. any change we have made in the transaction workspace is not committed to the main database yet. In the transaction workspace, we can run another query to increase the balance of the receiver

UPDATE accounts
SET balance=balance+100
WHERE name="B"

After we are done running queries in the transaction workspace, we have to merge those changes to the main database. For this we run

COMMIT

During running queries inside the transaction workspace, if we lose the connection, any change we have made will be deleted

Answer 2

According to the Database Fundamentals book(Sharma, et al., 2010, p. 162), a transaction or unit of work is a set of database operations all of which should be executed successfully in order to call the transaction successful.

For example, if a bank has to transfer 1,000 dollars from account A to account B, then the following steps are required before the transfer is successful.

• Reduce the balance of account A by the amount of 1,000
• Increase the balance of account B by the amount of 1,000

References:

Sharma, N., Perniu, L., Chong, R. F., Iyer, A., Nandan, C., Mitea, A. C., Nonvinkere, M. & Danubianu, M. (2010). Database fundamentals.

Answer 3

Transaction - is just a logically composed set of operations you want all together be either committed or rolled back.

Answer 4

Transaction can be defined as a collection of task that are considered as minimum processing unit. Each minimum processing unit can not be divided further.

The main operation of a transaction are read and write.

All transaction must contain four properties that commonly known as ACID properties for the purpose of ensuring accuracy , completeness and data integrity.

Answer 5

A transaction is a way of representing a state change. Transactions ideally have four properties, commonly known as ACID:

• Atomic (if the change is committed, it happens in one fell swoop; you can never see "half a change")
• Consistent (the change can only happen if the new state of the system will be valid; any attempt to commit an invalid change will fail, leaving the system in its previous valid state)
• Isolated (no-one else sees any part of the transaction until it's committed)
• Durable (once the change has happened - if the system says the transaction has been committed, the client doesn't need to worry about "flushing" the system to make the change "stick")

See the Wikipedia ACID entry for more details.

Although this is typically applied to databases, it doesn't have to be. (In particular, see Software Transactional Memory.)

Answer 6

Here's a simple explanation. You need to transfer 100 bucks from account A to account B. You can either do:

accountA -= 100;
accountB += 100;

or

accountB += 100;
accountA -= 100;

If something goes wrong between the first and the second operation in the pair you have a problem - either 100 bucks have disappeared, or they have appeared out of nowhere.

A transaction is a mechanism that allows you to mark a group of operations and execute them in such a way that either they all execute (commit), or the system state will be as if they have not started to execute at all (rollback).

beginTransaction;
accountB += 100;
accountA -= 100;
commitTransaction;

will either transfer 100 bucks or leave both accounts in the initial state.

Answer 7

A transaction is a unit of work that you want to treat as "a whole." It has to either happen in full or not at all.

A classical example is transferring money from one bank account to another. To do that you have first to withdraw the amount from the source account, and then deposit it to the destination account. The operation has to succeed in full. If you stop halfway, the money will be lost, and that is Very Bad.

In modern databases transactions also do some other things - like ensure that you can't access data that another person has written halfway. But the basic idea is the same - transactions are there to ensure, that no matter what happens, the data you work with will be in a sensible state. They guarantee that there will NOT be a situation where money is withdrawn from one account, but not deposited to another.

Answer 8

A transaction is a sequence of one or more SQL operations that are treated as a unit.

Specifically, each transaction appears to run in isolation, and furthermore, if the system fails, each transaction is either executed in its entirety or not all.

The concept of transactions is motivated by two completely independent concerns. One has to do with concurrent access to the database by multiple clients, and the other has to do with having a system that is resilient to system failures.

Transaction supports what is known as the ACID properties:

• A: Atomicity;
• C: Consistency;
• I: Isolation;
• D: Durability.

Answer 9

Transaction is an indivisible unit of data processing -All transactions must have the ACID properties:

ie:Atomicity,Consistency,Isolation and Durable Transaction is all or nothing but not intermidiate (it means if you transfer your money from one account to another account,one account have to lose that much and other one have to gain that amount,but if you transfer money from one account and another account is still empty that will be not a transaction)

Answer 10

In addition to the above responses, it should be noted that there is, at least in theory, no restriction whatsoever as to what kind of resources are involved in a transaction.

Most of the time, it is just a database, or multiple distinct databases, but it is also conceivable that a printer takes part in a transaction, and can cause that transaction to fail, say in the event of a paper jam.

Answer 11

I think a transaction is an atomic action in terms of DBMS.

that means it cannot be seperated. yes, in a transction, there may be several instructions for the system to execute. but they are binded together to finished a single basic task.

for example. you need to walk through a bridge (let's treat this as a transction), and to do this, say, you need 100 steps. overall, these steps cannot be seperated. when you've done half of them, there is only two choice for you: continue to finish them all, and go back to the start point. it's just like the to result of a transaction: success( committed ) and fail( rollback )

Answer 12

http://en.wikipedia.org/wiki/Database_transaction
http://en.wikipedia.org/wiki/ACID
ACID = Atomicity, Consistency, Isolation, Durability

When you wish for multiple transactional resources to be involved in a single transaction, you will need to use something like a two-phase commit solution. XA is quite widely supported.

Answer 13

I would suggest that a definition of 'transaction processing' would be more useful, as it covers transactions as a concept in computer science.

From wikipedia:

In computer science, transaction processing is information processing that is divided into individual, indivisible operations, called transactions. Each transaction must succeed or fail as a complete unit; it cannot remain in an intermediate state.

http://en.wikipedia.org/wiki/Transaction_processing#Implementations

Answer 14

"A series of data manipulation statements that must either fully complete or fully fail, leaving the database in a consistent state"