Learning is not a spectator sport

Just a quick gotcha that often catches me out on LiveSQL, so posting it here to make sure you also don’t get caught out.

(Quick Segue: If you’ve only used LiveSQL in the past, be sure to check it out again – it has been totally re-jigged give a much more modern experience.)

LiveSQL allows you to model how real world applications work nowadays, namely, 99.9% of the worlds applications work via a browser to a middle tier in a stateless fashion.

Each interaction with the database is essentially

• Connect to the database (in reality, you are given a pre-existing connection from a pool, but from the application’s perspective, it is like a brand new connection)
• You do some work
• You give back the connection

This means that any transaction you initiated will end once you give back that connection. (I’m skipping over a super cool new 23ai feature called session-less transactions, but I’ll save that for a future post).

Bringing this back to LiveSQL, this mean that running a script with (say) 4 statements, is different to running each of the statements in sequence, because a script is a single interaction with the database, whereas running 4 statements will be four separate interactions with the database.

This has implications when it comes to transactions, because transactions end with each interaction. For example, here are 5 statements that I ran as a script.

This is a single interaction with the database, so the “rollback” command is within the same transaction/context of the session that did the INSERT statement.  Hence, my table is empty at the end of the interaction.

Here’s the same statements run sequentially using the “Run Statement” button.

Notice the difference. When I ran the INSERT statement and returned back to LiveSQL, that transaction is completed and hence the row is committed.

The “rollback” statement is executed in a new session, and thus has no impact on the previously inserted (and committed) row.

It’s nice to have both “modes” available to you, but if you’re debugging an issue with a script, be aware that you’ll need to probably debug it in the context of script execution, not with the individual statements.