[FEATURE] Transaction Support

[SiPurdy]

Is your feature request related to a problem? Please describe. There may be situation where being able to do a set of updates on records within a transaction would be useful, this would be more so when cursors come into play where we loop through a series of records in a cursor, wanting to execute multiple operations within a transaction.

Describe the solution you'd like Support for SQL keywords begin transaction and commit (I can't see how/why you would do rollback). Any statements within are run within the transaction are executed within an ExecuteTransactionRequest. Transaction size limits would also need to be handled.

As an example:

begin transaction
update contact set firstname = 'Hello' where statecode=0
commit

Should result in all active contacts having their firstname set to hello". If any update failed, all of the updates would be rolled back. Should there be more UpdateRequests in the transaction than a limit, the transaction would be rolled back.

Describe alternatives you've considered Not really any alternatives.

Additional context Would be especially useful with cursors. Processing on each record returned by the cursor could be done within a transaction to avoid partial updates.

[Byttemos]

I don't know how to accurately describe how much I second this request. I work with large datasets in dataverse and I am losing my mind. Being able to safely work with our database through SQL would be so valuable! +1 on this one!

[Byttemos]

Not that I want to rush, but are there any updates on this? I feel like it is an essential part of SQL functionality. Don't get me wrong I am ecstatic that his tool even exists, but I would sleep better at night with transactions ;-)

[MarkMpn]

This isn’t going to be any time soon, if at all. All the SQL 4 CDS logic for each transaction would need to be shifted to a plugin to run within the main database transaction, and things like elastic or virtual tables would still not be transactional anyway.