Added by Edit (18-Jun-2023):
I have run more tests and think it's possible the bug lies in MySQL. I'm using version 8.0.19 on MacOS.
I resumed testing this morning by transforming one table from MyISAM to InnoDB and repeating the test with pool.query(). It worked for the first time.
I transformed the table back to MyISAM and repeated the test. It still worked. While the table still ended up in the SHARED_NO_READ_WRITE state, it didn't keep the table from being written. This behavior is consistent not with connections being cleaned out when they're released but just before they're reallocated.
As to why it is now working, one possibility is that the transformation of a single table to InnoDB altered MySQL behavior, perhaps changing the setting of some internal state variable. And Even though the table was later transformed back to MyISAM it continued working.
Added by Edit (17-Jun-2023):
This is a long question, and the comments that didn't realize I was using MyISAM (where explicit table locking is required) and not InNODB and the negative votes at one point made me think I had misinterpreted what was happening, but further testing showed that something was amiss with table locking with pool.query(). It locks the tables referenced in a query and it doesn't free the locks when it releases the connection.
Switching from pool.query to pool.getConnection() -> conn.query("lock tables...") -> conn.query(query) -> <more conn.query's> -> conn.query("unlock tables") works.
It would be very informative to do further testing and track down if the misbehavior of pool.query isn't in some way due to my own code, perhaps the way I use promises and useEffect, but I have already spent too much time on this.
Original question:
I'm writing this to verify that what I think I've learned about locking tables in nodejs/MySQL is actually correct
I've been using MySQL with PHP for many years and have recently begun implementing a new website using nodejs/Express/MySQL as a backend (ReactJS/MUI is the frontend). In PHP I'm accustomed to doing this:
$Result1 = doQuery($query1);
$Result2 = doQuery($query2);
$Result3 = doQuery($query3);
Because nodejs is asynchronous, nodejs/MySQL is implemented using callbacks, so the equivalent in nodejs/MySQL would be something like this:
conn.query(query1, (error, results1, fields) => {
if (error) throw error
conn.query(query2, (error, results2, fields) => {
if (error) throw error
conn.query(query3, (error, results3, fields) => {
if (error) throw error
The nested callback structure looked both cumbersome and inelegant, so I created a database class to allow me to code in a way very similar to PHP. I used pooling connections because it allows parallel queries. Here's an example of what my approach looks like. Error checking is inside the class method structure:
const result1 = await db.doQuery(query1)
const result2 = await db.doQuery(query2)
const result3 = await db.doQuery(query3)
This worked perfectly on a test database I'd created. I was able to respond to requests from the front end, fetch all the data I needed, then return that data to the front end for presentation.
Then I reached the point where I was ready to begin writing to the database (replace, insert, update), and that's when things fell apart. I tried to lock the tables I was writing to and found my app hanging and one table getting locked in the SHARED_NO_READ_WRITE state, essentially inaccessible, even from the MySQL command line app.
Further investigation revealed that with pooling connections each query is one independent connection to the database because it carries out the sequence pool.getConnection -> conn.query -> conn.release.
I'd read this when I originally implemented my database class but hadn't realized the implications. Take this, for example:
db.query('lock tables myTable write')
All this does is fetch a connection from the pool, execute the query that locks the table, then releases the connection, which unlocks the table. When the next query is executed a new connection is opened and no tables are locked.
But I suspect that release of the connection does not actually unlock the tables, because if it were then when my app finished handling that request then no tables would have been locked, but the tables were still locked. As I mentioned earlier, one of the tables was in the SHARED_NO_READ_WRITE state, which implies a deadly embrace (two tables each waiting on the other to unlock).
I was unable to find anything explicit online about the tables being automatically unlocked when the connection was released, but I did find several sites that implied this is the case.
But I'm not sure what to believe because what I observed is consistent with the tables not unlocking when the connection is released. Instead, the nodejs process hung and the database became wedged with one table getting in that unreadable/unwritable state until I killed the nodejs process. Again, I couldn't even access this table from the MySQL command line tool. Here is some pseudocode representative of the nodejs that I ran:
await db.doQuery('lock tables Table1 read, Table1 read, Table 3 read')
const Result1 = await db.getQueryRow(readQuery1)
const Result2 = await db.getQueryRow(readQuery2)
await db.doQuery('lock tables Table2 write')
await db.doQuery(writeQuery1)
const ID = await db.getQueryDatum('select last_insert_id()')
await db.doQuery('lock tables Table1 write')
await db.doQuery(writeQuery2)
await db.doQuery('unlock tables')
I then ran a testcase where instead of using pool connections I just used a single database connection, something like this:
const conn = mysql.connection(...)
conn.query(queryString, (error, results, fields) => {
if (error) throw error
...handle results
}
This code received a MySQL error that I had not locked the table:
Error: ER_TABLE_NOT_LOCKED: Table 'myTable' was not locked with LOCK TABLES
I had never received this error when using pool connections, so this implies that pool.query automatically locks tables. After parsing the query it knows which tables are being read and written and must issue locks for them. When it releases the connection it must also unlock the tables.
But given the behavior I've observed, if the pool.query was actually a "lock tables" request then it doesn't unlock the tables you locked, and eventually you encounter a table locking conflict. When that happens the only way I've figured out to unlock the tables is to kill the nodejs process.
To solve the problem I added a getConnection method to my database class, and using that the above code now looks like this:
const conn = db.getConnection() // Fetch a connection from the pool
await conn.doQuery('lock tables Table1 read, Table2 read, Table 3 read')
const Result1 = await conn.getQueryRow(readQuery1)
const Result2 = await conn.getQueryRow(readQuery2)
await conn.doQuery('lock tables Table2 write')
await conn.doQuery(writeQuery1)
const ID = await conn.getQueryDatum('select last_insert_id()')
await conn.doQuery('lock tables Table1 write')
await conn.doQuery(writeQuery2)
await conn.doQuery('unlock tables')
conn.release()
This works perfectly. For parallelism I can fetch more than one connection and postpone my await statements strategically.
I should mention that had I not used pooling connections but had instead just fetched a single connection at the beginning of a session, locked tables as necessary, then unlocked them before ending the connection, that I would not have run into these problems, but I wanted the option of parallelism.
I've made a number of surmises and guesses. Corrections and additional information would be much appreciated.
