Register | Sign In


Understanding through Discussion


EvC Forum active members: 52 (9179 total)
3 online now:
Newest Member: Jorge Parker
Post Volume: Total: 918,174 Year: 5,431/9,624 Month: 456/323 Week: 96/204 Day: 12/26 Hour: 1/0


Thread  Details

Email This Thread
Newer Topic | Older Topic
  
Author Topic:   NodeJS Discussion
Percy
Member
Posts: 22689
From: New Hampshire
Joined: 12-23-2000
Member Rating: 3.7


Message 1 of 17 (911185)
06-17-2023 9:27 AM


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

  
Percy
Member
Posts: 22689
From: New Hampshire
Joined: 12-23-2000
Member Rating: 3.7


(1)
Message 2 of 17 (911188)
06-17-2023 11:09 AM


Issues From StackOverflow Question
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:
const conn=pool.getConnection()
conn.query("lock tables Table1 write, Table2 write")
const table1Row = conn.query("select * from Table1 where ...")
...processing...
conn.query("insert into Table1 set ...")
const numItems = conn.query("select count(*) from Table1 where ...")
conn.query(`update Table2 set ItemCount=${numItems} where ...`)
conn.query("unlock tables")
conn.release()
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

Replies to this message:
 Message 3 by Percy, posted 06-17-2023 1:16 PM Percy has seen this message but not replied

  
Percy
Member
Posts: 22689
From: New Hampshire
Joined: 12-23-2000
Member Rating: 3.7


Message 3 of 17 (911192)
06-17-2023 1:16 PM
Reply to: Message 2 by Percy
06-17-2023 11:09 AM


Re: Issues From StackOverflow Question
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

This message is a reply to:
 Message 2 by Percy, posted 06-17-2023 11:09 AM Percy has seen this message but not replied

Replies to this message:
 Message 4 by PaulK, posted 06-17-2023 1:39 PM Percy has replied

  
PaulK
Member
Posts: 17852
Joined: 01-10-2003
Member Rating: 4.0


Message 4 of 17 (911193)
06-17-2023 1:39 PM
Reply to: Message 3 by Percy
06-17-2023 1:16 PM


Re: Issues From StackOverflow Question
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.

This message is a reply to:
 Message 3 by Percy, posted 06-17-2023 1:16 PM Percy has replied

Replies to this message:
 Message 5 by Percy, posted 06-17-2023 9:21 PM PaulK has replied

  
Percy
Member
Posts: 22689
From: New Hampshire
Joined: 12-23-2000
Member Rating: 3.7


Message 5 of 17 (911198)
06-17-2023 9:21 PM
Reply to: Message 4 by PaulK
06-17-2023 1:39 PM


Re: Issues From StackOverflow Question
PaulK writes:
Transactions were invented to maintain database integrity and they may be more efficient than whole-table locks.
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.
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.
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

This message is a reply to:
 Message 4 by PaulK, posted 06-17-2023 1:39 PM PaulK has replied

Replies to this message:
 Message 6 by PaulK, posted 06-18-2023 4:04 AM Percy has replied

  
PaulK
Member
Posts: 17852
Joined: 01-10-2003
Member Rating: 4.0


Message 6 of 17 (911200)
06-18-2023 4:04 AM
Reply to: Message 5 by Percy
06-17-2023 9:21 PM


Re: Issues From StackOverflow Question
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 ?

This message is a reply to:
 Message 5 by Percy, posted 06-17-2023 9:21 PM Percy has replied

Replies to this message:
 Message 7 by Percy, posted 06-18-2023 8:21 AM PaulK has replied

  
Percy
Member
Posts: 22689
From: New Hampshire
Joined: 12-23-2000
Member Rating: 3.7


Message 7 of 17 (911201)
06-18-2023 8:21 AM
Reply to: Message 6 by PaulK
06-18-2023 4:04 AM


Re: Issues From StackOverflow Question
PaulK writes:
Transactions are also old technology - dating back to the ‘70s.
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.
If locks are per-connection then aren’t you always risking inconsistencies when using a connection pool ?
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

This message is a reply to:
 Message 6 by PaulK, posted 06-18-2023 4:04 AM PaulK has replied

Replies to this message:
 Message 8 by Percy, posted 06-18-2023 10:14 AM Percy has seen this message but not replied
 Message 10 by PaulK, posted 06-18-2023 1:46 PM Percy has replied

  
Percy
Member
Posts: 22689
From: New Hampshire
Joined: 12-23-2000
Member Rating: 3.7


Message 8 of 17 (911202)
06-18-2023 10:14 AM
Reply to: Message 7 by Percy
06-18-2023 8:21 AM


Re: Issues From StackOverflow Question
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

This message is a reply to:
 Message 7 by Percy, posted 06-18-2023 8:21 AM Percy has seen this message but not replied

  
Tangle
Member
Posts: 9538
From: UK
Joined: 10-07-2011
Member Rating: 4.0


(1)
Message 9 of 17 (911203)
06-18-2023 11:56 AM


Are you having fun Percy?

Je suis Charlie. Je suis Ahmed. Je suis Juif. Je suis Parisien. I am Mancunian. I am Brum. I am London. Olen Suomi Soy Barcelona. I am Ukraine.

"Science adjusts it's views based on what's observed.
Faith is the denial of observation so that Belief can be preserved."
- Tim Minchin, in his beat poem, Storm.


Replies to this message:
 Message 12 by Percy, posted 06-18-2023 5:08 PM Tangle has not replied

  
PaulK
Member
Posts: 17852
Joined: 01-10-2003
Member Rating: 4.0


Message 10 of 17 (911204)
06-18-2023 1:46 PM
Reply to: Message 7 by Percy
06-18-2023 8:21 AM


Re: Issues From StackOverflow Question
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,

This message is a reply to:
 Message 7 by Percy, posted 06-18-2023 8:21 AM Percy has replied

Replies to this message:
 Message 11 by Percy, posted 06-18-2023 5:07 PM PaulK has replied

  
Percy
Member
Posts: 22689
From: New Hampshire
Joined: 12-23-2000
Member Rating: 3.7


Message 11 of 17 (911210)
06-18-2023 5:07 PM
Reply to: Message 10 by PaulK
06-18-2023 1:46 PM


Re: Issues From StackOverflow Question
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

This message is a reply to:
 Message 10 by PaulK, posted 06-18-2023 1:46 PM PaulK has replied

Replies to this message:
 Message 13 by PaulK, posted 06-18-2023 5:14 PM Percy has replied

  
Percy
Member
Posts: 22689
From: New Hampshire
Joined: 12-23-2000
Member Rating: 3.7


Message 12 of 17 (911211)
06-18-2023 5:08 PM
Reply to: Message 9 by Tangle
06-18-2023 11:56 AM


Tangle writes:
Are you having fun Percy?
Fun is when something finally works. Misery is when you can't figure out why something doesn't work.
--Percy

This message is a reply to:
 Message 9 by Tangle, posted 06-18-2023 11:56 AM Tangle has not replied

  
PaulK
Member
Posts: 17852
Joined: 01-10-2003
Member Rating: 4.0


Message 13 of 17 (911212)
06-18-2023 5:14 PM
Reply to: Message 11 by Percy
06-18-2023 5:07 PM


Re: Issues From StackOverflow Question
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.

This message is a reply to:
 Message 11 by Percy, posted 06-18-2023 5:07 PM Percy has replied

Replies to this message:
 Message 14 by Percy, posted 06-18-2023 5:52 PM PaulK has replied

  
Percy
Member
Posts: 22689
From: New Hampshire
Joined: 12-23-2000
Member Rating: 3.7


Message 14 of 17 (911213)
06-18-2023 5:52 PM
Reply to: Message 13 by PaulK
06-18-2023 5:14 PM


Re: Issues From StackOverflow Question
PaulK writes:
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

This message is a reply to:
 Message 13 by PaulK, posted 06-18-2023 5:14 PM PaulK has replied

Replies to this message:
 Message 15 by PaulK, posted 06-19-2023 12:05 AM Percy has replied

  
PaulK
Member
Posts: 17852
Joined: 01-10-2003
Member Rating: 4.0


Message 15 of 17 (911215)
06-19-2023 12:05 AM
Reply to: Message 14 by Percy
06-18-2023 5:52 PM


Re: Issues From StackOverflow Question
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.

This message is a reply to:
 Message 14 by Percy, posted 06-18-2023 5:52 PM Percy has replied

Replies to this message:
 Message 16 by Percy, posted 06-19-2023 9:06 AM PaulK has replied

  
Newer Topic | Older Topic
Jump to:


Copyright 2001-2023 by EvC Forum, All Rights Reserved

™ Version 4.2
Innovative software from Qwixotic © 2024