No More Table Locks for Multi Statement Transactions #databricks #dataengineering #sql

Everybody thank me. It is my my most requested feature [laughter] and they finally did it. >> Multi-table transactions, again, [music] not to be confused with multi-statement transactions. This is the ability to make uh >> [music] >> changes to two separate tables within one transaction. So, if something goes wrong with one and you need to roll back, you're never going to end up with that kind of intermediate state between half between that. [music] One thing I will say that because it's in Databricks, whilst the update is still happening, that table is not locked and that might not necessarily be true for some of the more legacy warehouses that you are using. It is something that is managed Unity Catalog, so these do need to be Unity Catalog managed tables. But, it is very similar a simple syntax. >> [music] >> It's just begin atomic and then you do your changes to your particular table and then you end it. And so, if one of these fails, it will roll back. And I think I have a dud example here where I try and break something and I try and add in a fake column. What will happen here is this is going to say, "Ah, this isn't working." The assistant is going to try and kick in and do something. [music] And I can see here that actually my my dud has not been updated at all. Amazing. This is like the final boss of transaction Audi. I'm serious, this is amazing. I feel like we've been waiting for this since since Delta, since transactions on parquet became a thing, right? So, we're talking quite a long time here actually. It's terrific. I did have a few questions actually. Um >> [music] >> So, you said it's multi-table. Is there a limit to the number of tables? Sorry to get in the weeds, but like is it like two or is it like I think it's like 20 or 100 or something. There we go, transactions can be Yeah, 100 tables combined and can read from up to 100 views. So, hopefully that's enough for people. Yeah, so it's So, really so it's a multi-state kind of like you could think of it like this. We support multi-statement transactions and now those multi-statements can use multi-tables. Yay. >> Correct. This is uh incredible.