3 ms·
Can you explain? Due to Node's evented architecture, you should be executing all the transaction code in ONE sql query -- otherwise the transaction can be inte
by EGreg 11y ago
Can you explain?
Due to Node's evented architecture, you should be executing all the transaction code in ONE sql query -- otherwise the transaction can be interleaved with other queries!
- wslh 11y ago(downvotes started here too! please educate me instead of downvoting, I am here to learn) Yes, that is an additional issue that I suppressed for my comment trying to narrow the criticism to executing several SQL queries in a readable and maintainable way. I will not expand on how we solved that specific issue, just say that the transaction in question is fired by external events that can't be fired until a previous one finished. But, following your suggestion about executing all the transactions in one SQL query, how do you do this without using a store procedure? Because we were thinking in using store procedures but we couldn't implement all the logic inside the transaction with a SP. This is where other languages solve the problem but I couldn't find a concrete solution in Node, so any suggestions will be very appreciated.
- EGreg 11y agoI was also worried I'd need stored procedures (which MySQL requires root privileges to create) until I realized (by trying) that felixge's mysql module (and probably others) simply accept queries with multiple statements :-D So that solved it for me!
- manyxcxi 11y agoWe did exactly that- create stored procedures- to get around certain transactional issues we were facing. We didn't have any problems being able to create them as we manage the schema outside of the application. In general this has been a fine trade off for us and hasn't caused any pain. I remember way back in the day when using stored procedures is what you were SUPPOSED to be doing...
- mkozlows 11y agoNo. When you start a transaction, you hold onto the particular connection you started it with, and that connection is busy in your connection pool and won't be assigned to unrelated queries. See for instance: https://github.com/brianc/node-postgres/wiki/Transactions https://github.com/brianc/node-postgres/wiki/Transactions (If you're just saying "other unrelated queries could happen between opening the transaction and closing it," well, yes, but that's true of any web application, whether it uses threads or events.)
- EGreg 11y agoUsually a thread or an app holds a connection. But here the connection may be shared among many evented handlers!