When and how to perform transactions
I’m new to the concept of transactions on SQL, and was interested in understanding if there is a way to apply every change I make to my schema as a transaction in case anything goes wrong, so it can be rolled back. Or am I thinking this wrong? When should transactions be used?
3 Replies
For the three main databases that Drizzle supports, I believe all of them support doing transactions for regular SQL statements (insert, select, delete, etc). However, when it comes to DDL statements (create table, drop table, etc) I believe it is dependent on the specific database. I'm pretty sure that MySQL does not support wrapping DDL statements in a transaction since every DDL statement has an automatic commit which we users can't do anything about, but I think that Postgres allows DDL statements to be wrapped in a transaction. Hope that helps.
Thanks, but when should transactions be applied?
Not sure I fully understand your question, pato, so I'll just answer the best I can. The most popular use of transactions are used with regular SQL statements, and centers around insert/update/delete statements. If you are doing a single insert or a single update or single delete then really there is no need for a transaction. But in some cases we might need to insert a row and update two rows atomically, meaning we want all 3 of these to succeed together and if one of them fails we want to rollback the entire set of changes. This is where transactions come into play.
You would want to start a transaction and then do the insert and 2 updates then commit the transaction. Your changes to the db wouldn't be visible to other db clients until the commit is done. If you want to get your hands oh-so-slightly dirty with transactions check out this online demo that I created. https://onecompiler.com/mysql/42hwz9kwv It is a MySQL example where two transactions are used. Feel free to edit and run it. It doesn't deal with DDL statements, just inserts. (Side note, I'm not clear if drizzle-kit's migrations for postgres are able to be used with transactions or not, so that is part of the reason I didn't give an example with postgres DDL) If you can grasp how this online example works, then it will help you to understand how drizzle's implementation of transactions works a bit better.
42hwz9kwv - MySQL - OneCompiler
Super simple demo showing how to use transactions