MySQL Sessions & Connections: Acquiring Locks

[syedmoizurrehman]

MySQL provides user-level locks, which are exclusive to sessions. However, as the linked page mentions, it is possible for one session to acquire multiple locks of the same name (See this section)

It is even possible for a given session to acquire multiple locks for the same name

I would like to know the following:

The term "connection" as used in Readme's executing-queries-in-parallel section,

The MySQL protocol is sequential, this means that you need multiple connections to execute queries in parallel. You can use a Pool to manage connections, one simple approach is to create one connection per incoming http request.

does it refer to what MySQL docs refer to as "session" in the section regarding locks (first quotation)?

Further elaborating, using this package, is it possible for one connection to acquire multiple locks of same name if concurrent requests are executed? Please consider the following code for demonstration:

const db = mysql.createPool({
  connectionLimit: 10,
  host: ..,
  user: ..,
  password: ..,
  database: ..,
});

function runQuery() {
  return new Promise(function (resolve, reject) {
    db.query("SELECT GET_LOCK('A', 10)", (err, result) => {
      if (err) reject(err);

      console.log(result);
      resolve(result);
    })
  }
}

If the function runQuery is executed concurrently, is it possible to acquire the lock 'A' multiple times?

[dougwilson]

The term "connection" as used in Readme's executing-queries-in-parallel section, does it refer to what MySQL docs refer to as "session" in the section regarding locks (first quotation)?

So the term refers to the definition from MySQL documentation. You can find all the definitions in the glossary of the MySQL docs (https://dev.mysql.com/doc/refman/8.0/en/glossary.html)

connection
The communication channel between an application and a MySQL server. The performance and scalability of a database applications is influenced by on how quickly a database connection can be established, how many can be made simultaneously, and how long they persist. The parameters such as host, port, and so on are represented as a connection string in Connector/NET, and as a DSN in Connector/ODBC. High-traffic systems make use of an optimization known as the connection pool.

As for if that is the same as session, I'm not sure. I see they used that term in that page of their docs, but the term does not appear in the glossary. We'll have to find out where they define what a session is to understand how it relates to a connection.

Further elaborating, using this package, is it possible for one connection to acquire multiple locks of same name if concurrent requests are executed? Please consider the following code for demonstration:

I'm not sure, as I haven't on the gular used that feature. This module just passes your query as a string to the MySQL for processing, however it does that. As in, this module effectively provides functions to create a connection and then send the queries you enter as strings to the database on that connection and then translates back the response as JavaScript objects.

[syedmoizurrehman]

Thanks. The glossary is very useful but it is unfortunate that the term "session" is not covered by it. I've experimented a bit with MySQL and have found that a named lock L acquired by connection C1 cannot be acquired by any other connection until C1 releases L. From this, I assume that whatever a session is, it does not expand beyond the scope of a connection and no session is shared with more than one connection. This reasonably implies that we can assume session and connection to be synonymous for this specific case.

If a connection executes a procedure which acquires the lock, then due to MySQL protocol being sequential, any other request to acquire the lock (while the procedure is still executing) must be from a different connection and must fail to acquire the lock.

I am closing this for now. If you happen to come across any useful information regarding this in future, please do add it here.