serializable isolation level

Last 3 days have been playing around with DB isolation level trying to solve one customer issue. And this has confused me even more than I was till 3 days ago regarding this.

I always had the impression that serializable isolation level meant that DB will serialize all queries i.e if one query is being processed which involves certain rows and another query also comes for execution involving a subset of those rows, then DB will block this query and execute it only after 1st one is finished (committed or rolled back). But the result was no where close to what I was expecting.

Here is what happened when I tried with SQL server and Oracle server

Microsoft SQL Server

ssn1>select * from mytable where id=1;
no rows selected
ssn2>select * from mytable where id=2;
no rows selected
ssn1>insert into mytable values(1, 'name1');
[waiting...]
ssn2>insert into mytable values(2, 'name2');
[dead lock..]

At this point "ssn2" gets deadlocked and then its deadlock mechanism detects it and rolls back the changes done by ssn2 and allows ssn1 to proceed.

I simply dont understand why is "ssn1" waiting while trying to insert. This behaviour simply means that DB client should take care of serializing the query and its not actually DB who is serializes.

Here is what Oracle DB does

ssn1>select * from mytable where id=1;
no rows selected

ssn2>select * from mytable where id=2;
no rows selected

ssn1>insert into mytable values(1, 'name1');
1 row inserted
ssn1>commit;

ssn2>insert into mytable values(2, 'name2');
ORA-08177: can't serialize access for this transaction

So for oracle too, I need to serialize the queries myself?
The important thing is that both the sessions are trying to insert different rows and even then they can't do it.

Can someone explain to me when is this isolation level used, if they are used at all ;-) because in concurrent scenarios cases like above will happen very frequently and they are bound to fail.

I am not a DB guy and I might have overlooked some scenarios where 'serializable' is usable. But at this point of time this behaviour surely looks absurd to me.