[Feature request] Enable sql transaction management

[mmaderic]

Hi, As I couldn't find any information if Hasura currently supports SQL transactions, I would like to post a request for the feature which I think is upgraded solution than executing multiple mutations in a single request.

Please consider this feature for example:

Request to open SQL transaction with admin header is sent. SQL transaction is opened with defined transaction read settings. Hasura returns new transaction id. Multiple queries and/or mutations are executed with the transaction id provided as additional request argument. Request with the given transaction id is sent to either rollback or commit the transaction by the client. Hasura responds with the outcome.

[Bessonov]

I'm sure you don't want to do this. If the transaction is controlled outside, there is a big chance of dead lock. Or live lock. Or concurrent queries which waiting to long to complete the transaction. May be you can resolve a part of your use cases already with this one:

If multiple mutations are part of the same request, they are executed sequentially in a single transaction. If any of the mutations fail, all the executed mutations will be rolled back.

[mmaderic]

I get your point, but transaction is already controlled from the outside if it is going to be used in any way that is not directly from the sql server instance. Take some ORM or .NET framework for example. You are able to manipulate over transaction state. Ofcourse some failback mechanism has to be implemented but it isn't something that hasn't been already done.

[tirumaraiselvan]

Hi @mmaderic

As it happens, we already have a solution for keeping open transactions in graphql: https://github.com/hasura/graphql-engine/pull/3557

The protocol, which runs over websocket, is described here: https://github.com/rakeshkky/graphql-engine/blob/graphql-tx-poc/server/graphql-tx-protocol.md

[mmaderic]

Hi @tirumaraiselvan, thank you for the provided documentation. I am glad that the feature already exists. This information is valuable and I believe it's in your interest to have it published at the main documents page: https://hasura.io/docs/1.0/graphql/core/index.html

Regards, Mateo

[tirumaraiselvan]

@mmaderic Glad to hear it! The work is still to be released. Although it is usable via the preview image: hasura/graphql-engine:pull3557-2e391cf9 and sample code: https://gist.github.com/gautambt/b852e3052c6c6f02fcd52415ce3937b1

We are aiming to release it in the next version.

[jchiatt]

+1 for being able to start a transaction on the fly

[kadut]

Any activity on this?

This is a game changing feature, it should be massively prioritized, I don't understand the neglect.

[Bessonov]

but transaction is already controlled from the outside if it is going to be used in any way that is not directly from the sql server instance. Take some ORM or .NET framework for example.

@mmaderic from my point of view, it's not the same. Uncontrollable environments put huge challenges and trade-offs on the story.

@kadut can you explain a little bit more on "This is a game changing feature"? May be I need it without knowing :)

[aaronhayes]

@tirumaraiselvan any word on this?

[ilijaNL]

Bump

[nosovk]

It would be very nice to have transactions support in hasura to avoid duplication of data model in backend behind hasura.

[ilijaNL]

A temporary solution could be https://github.com/domasx2/graphql-combine-query, it "merges" the queries/mutation so it will be a single operation.

[rsd1122]

Link to a PoC Implementation (thanks to @nosovk ) https://github.com/rakeshkky/graphql-engine/blob/graphql-tx-poc/server/graphql-tx-protocol.md

[jcalfee]

It is really nice to see better support for insert on conflict in more recent hasura releases. I'm staring to use graphql in more deeply nested data structures. It would be over the top to have an auto commit toggle, commit, and rollback in the admin console. That would save a lot of incorrect test data from getting saved. Also, in the pgAdmin console I have not had an issue with dead-lock so far using the default transaction levels although I know that is something to watch out for.

[jcalfee]

If you do go down this route: Typically, depending on the database (production for example) I tend to leave auto-commit off 100% of the time and generally do this for all database just to be consistent. So, that may be a good habit for others as an extra layer to protect production environments. So, I would suggest at the minimum a database specific saving of this auto commit or persisting the setting globally for all connections works too.

[beepsoft]

@tirumaraiselvan @coco98 Do you still plan to make this happen? Or did it turn out to be a bad idea to have transaction support?

[shadi5858]

Any update on having transaction support?

[manasag]

Thanks everyone for your comments and feedback on this issue. We have been closely listening into all the feedback and requests from the community, and have been working on a re-imagined, re-architectured Hasura, that tackles many of these from ground up. We are pleased to announce that we are launching V3-Alpha of Hasura (Data Delivery Network) on our next Community call on Nov 30. In V3:

• Data modelling actions will be powered by a PGAdmin kind of embedded utility in Console, thus allowing users to possibly leverage transactions and many other such DB specific features.
• Regarding transactions across queries, we feel that it's not possible to come up with a general architecture in V2. However in V3, there is a possibility of achieving this, due to decoupling of the core Engine and Postgres. Postgres support in V3 is in a form of an open sourced Connector (build against a standardised SDK), which can be evolved much faster (and independently as well) for such requirements.

We would request to join this community call to learn more about Hasura V3.