A great read on isolation levels, concurrency, and locking:
http://www.oracle.com/technology/oramag/oracle/05-nov/o65asktom.html
While Oracle centric, this article is not necessarily Oracle specific. It compares how Oracle performs (or avoids) locking using multi-versioning to other databases. Detailed examples are provided.
Actually - I think this article is even more relevant to developers NOT using Oracle, since with Oracle, deadlocks are rare. In any case, understanding the basics can go a long way to solving application issues.
Regarding SQL Server vs MySQL deadlocks, doesn’t MySQL use some form of multi-versioning?