CODEWITHSUNDEEP
oracle-databaseconcurrency
Jitesh
Jitesh

Reputation: 244

How oracle handles concurrency in clustered environment?

I have to implement a database solution wherein contention is handled in a clustered environment. There is a scenario wherein there are multiple users trying to access a bank account at the same time and deposit money into it if balance is less than $100, how can I make sure that no extra money is deposited? Basically , this query is supposed to fire :-

update acct set balance=balance+25 where acct_no=x ;

Since database is clustered , account ends up getting deposited multiple times. I am looking for purely oracle based solution.

Upvotes: 0

Views: 202

Answers (2)

tvCa
tvCa

Reputation: 816

Clustering doesn't matter for the system which is trying to prevent the scenario you're fearing/seeing, which is locking.

Behold scenario user A and then user B trying to do an update, based on a check (less than 100 dollar in account): If both the check and the update is done in the same transaction, locking will prevent that user B does a check, UNTIL user A has done both the check, and the actual insert. In other words, user B will find the check failing, and will not perform the asked action.

When a user says "at the same time", you should know that the computer does not know that concept, as all transactions are sequential, no matter what millisecond is identical. Behold the ID that is kept in the Redo Logs, there's only one counter. Transaction X and Y is done before or after each other, never at the same time.

Upvotes: 1

Ditto
Ditto

Reputation: 3344

That doesn't sound right ... When Oracle locks a row for update, the lock should be across all nodes. What you describe doesn't sound right. What version of Oracle are you using, and can you provide a step-by-step example of what you're doing?

Oracle 11 doc here: http://docs.oracle.com/cd/B28359_01/server.111/b28318/consist.htm#CNCPT020

Upvotes: 0

Related Questions