Mysql – How are DB locks tied to connections and sessions

innodblockingMySQLsession

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.

Best Answer

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.

Related Question