When you have the situation of "deadlock" in database?

In an Oracle database you will never have a deadlock. The database engine has deadlock detection built in and will internally kill the session which is about to cause deadlock in the database.

In alert log you will get messages like deadlock detected, killing process id.....

Well this is something really googlable :-)

But in general, deadlock means that two or more entities are blocking some sources, and none of them is able to finish, because their are blocking sources in a cyclic way.

One example: Let's say I have table A and table B, I need to do some update in A and then B nd I decide to lock both them at the moment of usage (this is really stupid behaviour, but it serves it's purpose now). At the same moment, someone else does the same thing in oposite order - locks B firstly, then locks A.

Chronologically, this happens:

proc1: Lock A proc2: Lock B

proc1: Lock B - starts waiting until proc2 releases B proc2: Lock A - starts waiting until proc1 releases A

It's obvious none of them will finish. That's deadlock.

The hole goes much deeper, but this is just entry and if you need to know more, invest your time. In our university there are whole lectures about that - so don't think that reading few articles makes you an expert ;-)

http://stackoverflow.com/questions/2774935/what-is-deadlock-in-a-database