How to find who has allocated a user lock via dbms_lock

Would it be possible to find out which line of a stored procedure is causing these row lock contentions?

Not exactly but you can get the SQL statement causing the lock and in turn identify the related lines in the procedure.

SELECT sid, sql_text
FROM v$session s
LEFT JOIN v$sql q ON q.sql_id=s.sql_id
WHERE state = 'WAITING' AND wait_class != 'Idle'
AND event = 'enq: TX - row lock contention';

What would be the general guideline to reduce/avoid/eliminate such problems with coding?

The Oracle Concepts Guide section on locks says, "A row is locked only when modified by a writer." Another session updating the same row will then wait for the first session to COMMIT or ROLLBACK before it can continue. To eliminate the problem you could serialize the users, but here are some things that can reduce the problem perhaps to the level of it not being an issue.

• COMMIT more frequently. Every COMMIT releases locks, so if you can do the updates in batches the likelihood of another session needing the same row is reduced.
• Make sure you aren't updating any rows without changing their values. For example, UPDATE t1 SET f1=DECODE(f2,’a’,f1+1,f1); should be rewritten as the more selective (read fewer locks) UPDATE t1 SET f1=f1+1 WHERE f2=’a’;. Of course if the changing the statement will still lock the majority of rows in the table then the change will only have a readability benefit.
• Make sure you are using sequences rather than locking a table to add one to the highest current value.
• Make sure you aren’t using a function that is causing an index to not be used. If the function is necessary consider making it a function based index.
• Think in sets. Consider whether a loop running a block of PL/SQL doing updates could be rewritten as a single update statement. If not then perhaps bulk processing could be used with BULK COLLECT ... FORALL.
• Reduce the work that gets done between the first UPDATE and the COMMIT. For example, if the code sends an email after each update, consider queuing the emails and sending them after committing the updates.
• Design the application to handle waiting by doing a SELECT ... FOR UPDATE NOWAIT or WAIT 2. You can then catch the inability to lock the row and inform the user that another session is modifying the same data.

If this is true, what benefits does this have over row-/page-level locks?

You may have misunderstood what he meant.

A client connection and a session are essentially the same thing. A session starts after a client authenticates and ends when the client disconnects. (These are also referred to in the documentation as threads; each connection/session typically has a dedicated server thread servicing it.)

All locks in MySQL -- row locks, table locks, and named locks (which are advisory mutexes, not data structure locks) -- are always associated with, and held by, the session that created the locks.

The following statement is taken from the MySQL documentation for table-level locks, but it is true of all locks:

A session can acquire or release locks only for itself. One session cannot acquire locks for another session or release locks held by another session.

If not explicitly released, all locks are always automatically released when the session holding the locks ends. A session ends when the client disconnects, the client thread is killed, or times out due to expiration whichever of the interactive_timeout or wait_timeout timers applies to the session.

So I suspect that in these senses, it could be said that locks are "scoped to" the session; however, locks are always global in their scope and impact. If that weren't the case, there wouldn't be much point in locking -- which exists precisely to keep other sessions from doing things that conflict with what your thread is doing.

InnoDB supports multiple granularity locking which permits coexistence of record locks and locks on entire tables. To make locking at multiple granularity levels practical, additional types of locks called intention locks are used. Intention locks are table locks in InnoDB. The idea behind intention locks is for a transaction to indicate which type of lock (shared or exclusive) it will require later for a row in that table. _{— http://dev.mysql.com/doc/refman/5.5/en/innodb-lock-modes.html}

This is referring to the SELECT ... LOCK IN SHARE MODE (obtains "Intention Shared" row locks) and SELECT ... FOR UPDATE (obtains "Intention Exclusive" row locks) statements, which do allow you to deliberately obtain locks on rows... however, to be precise, these are not at the session level -- they are even more granular: they're a the transaction level, which is a subset of a session. As soon as you COMMIT or ROLLBACK the current transaction, these are released. Of course, if your session terminates unexpectedly, the transaction rolls back, which releases the locks also.