CODEWITHSUNDEEP
database-designtransactionsdatabase-connectionparallel-processing
Pankaj Upadhyay
Pankaj Upadhyay

Reputation: 13594

transaction handling : How are simultaneous transaction managed?

Suppose a withdrawal transaction of 6000 is happening on account ABC(fund:1000) and at the same time a withdrawl of 8000 is carried out from ATM. Then how will the transaction be managed. Will the database open just one connection and doesnt allow new connection to the same account ?? .... What will happen in such cases when a transaction needs to be done on same account simultaneously and how are these things managed.

Upvotes: 3

Views: 2055

Answers (2)

Piskvor left the building
Piskvor left the building

Reputation: 92792

This is a good question on a very complex topic. There's a technical database term, transaction, which guarantees that two conflicting operations cannot happen at the same time; this happens by implementing the ACID doctrine (Atomicity, Consistency, Isolation, Durability). ACID is the very core of relational databases (such as Oracle,Postgres,MySQL and MSSQL). Extremely briefly:

  • Atomicity means either the whole transaction happens, or none of it happens: either the money is withdrawn from one place, deposited to another, and the whole thing is logged (and the transaction completes successfully), or the transaction is aborted (rolled back), as if nothing happened; this guarantees you can't have e.g. only a withdrawal without a deposit.

  • Consistency means that you have an error-free state at all times: transactions either complete or they don't happen at all, so there are no incomplete or botched transactions (e.g. you can't have two updates of the balance at the same time, as you could get an inconsistent state)

  • Isolation means that any request that happens can assume that nothing else is touching the data - e.g. in your case, the ATM doesn't have to care who else is trying to access the acount. This may mean slower execution ("oops, access to this row is not available now, please wait"), but significantly simplifies the application algorithms.

  • Durability means that this still holds even if the system crashes - there's always a consistent state, even if power goes out.

See e.g. this for further reading: http://www.agiledata.org/essays/transactionControl.html

So, in practice, "simultaneously" doesn't happen: one request will be first in line, and another will be second (as the row with your balance is locked by the first request, the second process has to wait until it becomes available). Due to isolation, there's no need to worry about "at the same time": either there's enough money to withdraw now, or there isn't; it doesn't matter at all that another withdrawal is queued and will be attempted in 50 milliseconds (when the first request completes, database removes the lock on the relevant rows, and next request in the queue will happen). With the speed of current computers, you may perceive those to be happening simultaneously (1/20th of a second is almost imperceptible), but in reality they are not, they are sequential.

This may also be of interest: http://en.wikipedia.org/wiki/Concurrency_control#Database_transaction_and_the_ACID_rules

Upvotes: 6

alexius
alexius

Reputation: 2576

It depends on what is happening inside transaction and transaction isolation level. For example SERIALIZABLE (highest isolation level) guarantees that transactions will be performed one after another. Database connection will be allowed anyway but transactions can be rollbacked.

Upvotes: 3

Related Questions