NodeJS Discussion

This is a thread for discussing NodeJS in case I succeed in convincing anyone to discuss it here.--Percy

StackOverflow uses a Q/A format that doesn't encourage discussion. Yesterday I posted a question where I think discussion would be helpful. The question was Is my understanding of how "lock tables" works in nodejs/MySQL correct?The first comment and the first answer agreed that locking tables should only rarely be necessary. My own understanding is that failure to lock tables allows databases to be accessed while in an inconsistent state, or even worse, to be left in an inconsistent state. I would very much like to better understand why they believe locking tables is rarely necessary.In the example that I presented I broke my original "lock tables" statement up into multiple "lock tables" statements, and the answer noted that each "lock tables" statement removes all previous table locks. But in actual practice I don't break my "lock tables" statements up. My example only did that because it illustrated what I believe is a bug in nodejs/MySQL. I'd very much like to understand if nodejs/MySQL mishandles explicit table locks in pool.query. If this is truly a bug then it should be reported.The documentation for nodejs/MySQL at mysqljs/mysql does not mention table locking, and for pool connections it gives the most attention to pool.query. I mistakenly thought pools of connections were related, but they're not. The pool connections are all wholly independent from one another. Since each pool.query stands alone, any state you attempt to set such as setting a variable or locking a table is lost after that query completes.What I'm trying to say is that pool.query would be useful, particularly since it provides parallelism, if table locking were truly unimportant. But if I'm correct that table locking is essential then I think the nodejs/MySQL documentation should have given pool.query much less attention and stressed that in real world applications the pool.getConnection approach is usually necessary.Here's a pseudo-code example of locking tables using pool connections: Without those table locks another process could simultaneously execute the same code, and if process interplay happens in an unfortunate order, as will invariably happen with millions of sessions, then Table1 will have a row with incorrect data inserted, and Table2 will have an incorrect value of ItemCount.--Percy

I just exchanged a few comments over at StackOverflow, and it seems the two respondents both assumed I was using the InnoDB engine. If you use that engine and take advantage of transactions then you don't need to do the table locking because, as of my current understanding from reading impenetrable MySQL documentation, it does the locking for you.So the respondents didn't really mean table locking is rarely necessary. They meant that if you use InnoDB and transactions that you don't have to do table locking yourself because it is done for you. It's even possible that they've been using InnoDB for so long, or have exclusively used InnoDB, that they've forgotten that locking is still happening, even if they don't see it.But I'm using the MyISAM engine. It's what I started with back in 2005 (the original 2000 version of EvC Forum did not have a database), and I've stuck with it. InnoDB replaced MyISAM as the default engine in 2010, but I saw no reason to change. I did read up on the differences between MyISAM and InnoDB earlier this year, and I concluded that InnoDB held no advantages for me unless I updated EvC's code to take advantage of transactions.But I could easily change to InnoDB for the new website, and I just might do that.--Percy

Transactions were invented to maintain database integrity and they may be more efficient than whole-table locks. Given that InnoDB is now the default it may be better to learn about that rather than learning how to manage the locks in MyISAM.

It depends upon the read/write ratio. If your database if read heavy and write light then MyISAM is fine. It *is* old technology now, but it's also a mature technology. InnoDB has been around a while, too, but the important difference is that the focus regarding performance and improvements and bug fixes and so forth is much more on InnoDB these days. MyISAM locks are a solved problem. This site has been using them for nearly a couple decades. My familiarity with MyISAM is why I continued using it for the new website.The problem with nodesjs/MySQL is that there's a bug in pool.query that locks any table referenced in the query and then fails to release the locks when it completes. Eventually there's an attempt to reference a table that's still locked for writing and the nodejs process hangs while it waits for the the table to unlock. I do not know why pool.query is locking tables, but testing revealed that after nothing more than a single simple query like "select * from Table1", Table1 is locked for SHARED_READ and remains that way until the nodejs process is killed, as revealed by the MySQL command line app: "use performance_schema; select * from metadata_locks;"Changing from pool.query to pool.getConnection() -> conn.query("lock tables...") -> conn.query(query) -> ...more conn.queries... -> conn.query("unlock tables") works. No spurious table locks. Since I still used promises and useEffect and so forth in the exact same way I was with pool.query I doubt that the pool.query problem is some odd interaction between my code and pool.query.Switching to transactions would be straightforward, so when I complete the broad outline of functionality I'll probably take the time to switch over to InnoDB. Such a switch is not likely in the short term at EvC because there is so much database accessing already coded. I would be more inclined to reimplement using the same approach as for the new website, ReactJS/MUI for the frontend and nodejs/Express/MySQL for the backend.--Percy

Transactions are also old technology - dating back to the ‘70s.If locks are per-connection then aren’t you always risking inconsistencies when using a connection pool ?

All I meant is that MyISAM is older (e.g., no transaction support) and receives less attention than InnoDB.
You'll have to ask the database people why they didn't begin taking advantage of the transaction concept until the 1990's. Where I worked we added transactional support to our toolset in the late 1990's.But from where I sit as someone who has never used transactions in a database, they seem a mixed bag. It doesn't take a genius to write MyISAM queries that don't lock up - no one has ever encountered a problem at EvC due to database lockup. Transactions remove responsibility for determining which, when and in what way tables should be locked, but now there's the possibility of rollback. I've already started thinking about how I'll handle rollbacks, and all I have is questions right now. For example, when an attempt to write a message to the database gets rolled back, what do I report to the user? "Sorry, try again later?" Or do I automatically retry x times before reporting failure? Something else? Won't know until I actually start implementing. Each connection in a pool is independent of all other connections in the pool. Variable values and locks in one connection are not available to any other connection, though of course table locks in one connection will affect how other connections can access those tables. If connection 1 has a read lock on TableX then connection 2 cannot write TableX.When a connection is released back to the pool then variable values and table locks are cleaned out so that the connection is available for reallocation. This is necessary for security purposes, as you don't want information about one account lingering behind in state variables or temp tables when the connection is reallocated for use with a different account.My guess is that mysqljs/nodejs offers pool.query() as a handy shortcut for when you need to perform only a single query. If you need a sequence of queries and need to maintain state and lock tables (only for MyISAM) then you have to go the "pool.getConnection() -> conn.query() -> conn.query() -> ... -> conn.query() -> conn.release()" route, although you could also go the "multiple statements in a single query" route.One possibility that just occurred to me that might explain the pool.query() bug is that it assumes you're using the Innodb engine. Unfortunately my code is no longer in a state where this would be easy to test, but I might do it anyway.--Percy

I did do the test of changing a table to InnoDB, and that fixed the problem. While the table still ended up in the SHARED_NO_READ_WRITE state, it had no effect. Evidently the connection state and lock tables are cleared out just before reallocation, not upon release.Intriguingly, changing the table back to MyISAM didn't cause pool.query() to begin failing again. It continued to work. My guess is that changing just one table temporarily to InnoDB altered MySQL's internal state.This makes MySQL a more likely culprit than mysqljs/nodejs.--Percy

Are you having fun Percy?

Transaction rollbacks shouldn’t be a problem.If you choose to rollback then you’ve just stopped the transaction from making any changes, and that should be what you want.If an error causes a rollback then the only difference a transaction makes is that you don’t need any recovery code to account for partial changes to the database. If you retry you have to start from the beginning of the transaction again, but that should not be difficult,

I'm not worried about the technical side of transaction rollbacks. My concern is for the user experience. There's database error checking in the current EvC code, but I'm the only one who ever sees errors, while I'm screwing up updating old code or writing new code. By the time the code gets released it's been tested and users never see database errors, or at least that's the goal. If a users sees a database error then that's a bug and I'll fix it.If the same is true in a transaction style approach, that in properly written and tested code rollbacks don't happen, that they're only seen during the development and testing stages to let you know when you screwed up, then that's great. I don't want an error like, "Message save failed, sorry," to just be something that happens to users sometimes.--Percy

Fun is when something finally works. Misery is when you can't figure out why something doesn't work.--Percy

A rollback will only happen if you expressly ask for one or if the commit can’t be done. Really the only difference is atomicity - the commit is all or nothing while writes outside of a transaction can fail individually.If your transaction is just being used for read consistency - with only a single update - then it shouldn’t behave much differently.

These are the database queries involved in writing a message:

• Lock tables
• Fetch ID for new message
• Fetch number for new message
• Write the message and all it's accompanying data
• Record the user's posting of the message
• If message was a response to a message, update the message's response status
• Update the message ID of the thread's last message
• Update the thread ID of the forum's last thread to be posted a message
• Unlock tables

Would it be accurate to say that the only difference will be that locking the tables is replaced by BEGIN and unlocking the tables is replaced by COMMIT? Can't confirm if I've got the right keywords, just going from memory, have to go.--Percy

Assuming that the transaction works in the standard way, the explicit locking and unlocking of the tables should be unnecessary.If you have any code to unwind partial changes, in the event of an error, that will be unnecessary too.