How are DB locks tied to connections and sessions?

Question

I've heard of page- and row-level locks, but never heard them used with regards to sessions and connections. Today, our (MySQL) DBA was talking about row locks and implied that their scope can be set at the connection or session level. Is this true, or did I misunderstand him?

I didn't realize that locks could be set when a connection is made (or when a session is started) and then released when that connection/session ends. If this is true, what benefits does this have over row-/page-level locks? Thanks in advance.

Answer 1

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.

Answer 2

To some extent, yes.

When it comes to InnoDB, you can choose what rows can get locked. It is not so much a particular setting for the DB Connection. It is a specific query you have to call before doing UPDATEs.

• SELECT ... FOR UPDATE
• SELECT ... LOCK IN SHARE MODE

This can initiate exclusive or shared row locks based on the result set of those SELECTs.

You can find more information on this in the MySQL Documentation.

I wrote earlier posts on using these:

• Aug 08, 2011 : Are InnoDB Deadlocks exclusive to INSERT/UPDATE/DELETE?
• Jan 02, 2012 : LOCK IN SHARE MODE
• Mar 18, 2012 : select for update gives error on indexed column
• May 09, 2012 : Transaction Lock Timeouts When Updating a Row
• May 13, 2012 : Cannot update certain rows in innodb tables
• Aug 10, 2012 ; Similar function NOWAIT in MySQL

With regard to sessions and connections

• Performing SELECT ... FOR UPDATE will initiate locks on rows you intend to update and still permit other DB Connections to read.
• Performing SELECT ... LOCK IN SHARE MODE essentially does the reverse
  • lock rows for you to read
  • allow SELECTs of the same locked rows
  • prevent connections from doing SELECT ... FOR UPDATE or direct DML

The only advantage I see in using these SELECT queries is preventing needless deadlocks on pages.

I hope this helps !!!