Full connection and transaction control

[otoolep]

This change sees the addition of a new endpoint /db/connections.

A POST to this endpoint returns a new location, that is backed by a dedicated connection to the database. On this connection transactions are supported, meaning commands such as BEGIN and COMMIT are supported. These connections can be configured to abort any transaction that has been without activity for a period of time, or close altogether for the same reason.

[otoolep]

Interesting post on building HTTP handlers, which may be of use: https://blog.merovius.de/2017/06/18/how-not-to-use-an-http-router.html

[al-sabr]

Is this feature controllable via REST API because I am implementing an EntityManager which will interface RQLite in remote connection mode and I would like to know where can I find the information to include BEGIN and COMMIT via REST.

[otoolep]

This development effort was abandoned, and is not present in the 5.x release series.

I hope to plan to return to it sometime in the future. You can send explicit BEGIN and COMMIT commands to rqlite, but it's undefined what will happen if the cluster has a leader election, or fails, while in a transaction.

[al-sabr]

I only use one instance in local it will not be in cluster does it work in single mode?

You can send explicit BEGIN and COMMIT

Can you point out in the documentation what are the params for that?

Best regards

[otoolep]

Just send a BEGIN like you would an INSERT, see if it works. I have not tested it, and the behaviour of the system is undefined. It'll probably work like you expect, but the transaction will not be cleared by a node restart.

curl -XPOST 'localhost:4001/db/execute?pretty&timings' -H "Content-Type: application/json" -d '[
    "BEGIN"
]'

[al-sabr]

I get this error and it is weird because there is no other transaction at all that's the first one:

{
    "results": [
        {
            "error": "cannot start a transaction within a transaction"
        }
    ],
    "time": 0.0029917
}

[otoolep]

Well, SQLite -- where that error message is coming from -- is telling you otherwise. Clearly you've issued two BEGIN statements without a COMMIT or ROLLBACK in between them.

[otoolep]

Are you using the tx query param at all? Or the bulk API? To debug this issue, I'd need a repro case -- instructions that show me how you made this happen.

[al-sabr]

Just send a BEGIN like you would an INSERT, see if it works. I have not tested it, and the behaviour of the system is undefined. It'll probably work like you expect, but the transaction will not be cleared by a node restart.

curl -XPOST 'localhost:4001/db/execute?pretty&timings' -H "Content-Type: application/json" -d '[
    "BEGIN"
]'

Can someone update the change so that there is no incongruency in here and in documentation please?

This change sees the addition of a new endpoint /db/connections. A POST to this endpoint returns a new location, that is backed by a dedicated connection to the database. On this connection transactions are supported, meaning commands such as BEGIN and COMMIT are supported. These connections can be configured to abort any transaction that has been without activity for a period of time, or close altogether for the same reason.

I am now confused!

[otoolep]

Can someone update the change so that there is no incongruency in here and in documentation please?

Which documentation? BEGIN and COMMIT are not officially supported. You can try it, but I don't know what will happen.

This change sees the addition of a new endpoint /db/connections.

This code is not in rqlite. It was reverted. This PR (#493) was completely backed out, and all activity associated with it is obsolete.

[al-sabr]

Is it possible to just separate the BEGIN COMMIT from the query like we receive a transaction ID that I can track inside of my ORM and then when I do a COMMIT I pass the ID of the transaction?

I realized that transactions is not practical and make no sense if we are going to open many transactions in parallel by my ORM. It should be not like localhost:4001/db/execute?pretty&transaction but more like localhost:4001/db/transaction?op=BEGIN and it should return something like:

{
    "results": [
        {
            "txId": "df7g6weuih23893476354"
        }
    ],
    "time": 2.478862
}

Then we could do something like:

curl -XPOST 'localhost:4001/db/execute?pretty' -H "Content-Type: application/json" -d "[
    \"INSERT INTO foo(name) VALUES('fiona')\",
    \"INSERT INTO foo(name) VALUES('sinead')\"
]"

It would end the transaction by a commit with localhost:4001/db/transaction?op=COMMIT&txId=df7g6weuih23893476354. The ORM has specific function to beginTransaction and commitTransaction in ASYNC mode because I am mapping it to RQLite. That's where two steps gets merged into one with the current implementation which make no sense.

Remember that I am only on local mode no cluster.

Thanx for answering my threads.

[otoolep]

Is it possible to just separate the BEGIN COMMIT from the query like we receive a transaction ID that I can track inside of my ORM and then when I do a COMMIT I pass the ID of the transaction?

I'm afraid I'm not following you. There is no concept of transaction ID in rqlite.

[al-sabr]

Isn't my description clear enough?

[otoolep]

Your description is referencing functionality that does not exist in rqlite.

[al-sabr]

Well if there is a REST API the BEGIN; COMMIT, ROLLBACK and such functions should be by design controllable via the REST API by design isn't it? There an architecture and design flaw with the actual state don't you think?

[otoolep]

rqlite is not just a SQLite database. It's a distributed system, and transactions in a distributed system are not simple to implement. To call it a "flaw" is not correct. You might like to read this thread to understand more about the challenges with implementing distributed transactions.

Right now rqlite doesn't support the type of transactions available from direct use of SQLite. This doesn't mean that you can't issue BEGIN and COMMIT -- you can. However the behaviour of rqlite is not defined if a node, say, crashes during a transaction. That is why no guarantees are made right now.

To be clear, I would like to add a form of distributed transactions, but it requires a lot of thought and careful programming. The first attempt (as captured in this PR) didn't work as well I had hoped, unfortunately, so I abandoned the work.

[al-sabr]

Well an alternative way of successfully achieving this endeavor would be to first split this project in 2 ways.

1. RQLite as an individual not clustered version which supports the points that I talked about which is if you are able to understand and implement in this local mode would teach you all the aspects to take in consideration when you are deciding to scale this in a distributed system.

2. RQLite as a distributed system based on the errors and mistakes learned from 1.

Right now I don't need the distributed part of RQLite but I end up having to deal with it.

Don't you think that this could be a good design & architecture choice?

[otoolep]

I added a FAQ entry about this.

Thanks for your suggestion, but it wouldn't be super helpful. If rqlite was a single-node system then it would be much easier to implement full transaction support. It because it's meant to be distributed that I want to solve the distributed case.

[otoolep]

Again, someone could probably solve the single-node case much easier, such that it could work with an ORM. But that would require forking the project, as it's not something I'm interested in solving for rqlite. I want a solution that works for clusters.

[al-sabr]

Well what I was expecting to be able to do with RQLite is via my ORM, which will be connected to an p2p protocol, will take care of the distributed part in every node that will received the propagation.

Every node will have their single instance of RQLite running in the background and the synchronisation is just done by simple propagation from my ORM.

What are the part that we have to remove to make RQLite single node mode? If this is not too complicated I will implement the single node transaction management the way I described.

I think that would be a flavor which will be interesting for people who don't want RQLite in distributed mode. Is there any other RDBMS for SQLite that support full transaction management via REST API like RQLite only in single node mode?

[otoolep]

If you don't want any distributed support, you could modify rqlite by removing all references to the Raft subsystem and just writing straight to the SQLite database. Then you'd have what you want. But you probably need to be familiar with Go to do that, to be honest.

[al-sabr]

Well I know some basics of Go which is a good start.

Could you direct me which parts are irrelevant so that I can remove them all and then start the implementation?

[otoolep]

At this stage I suggest you trace through the code, and study its implementation. The code you would need to modify will mostly be in store/store.go. Instead of calling Raft functions, the code should call functions in db/db.go.

[otoolep]

I'd like to help further, but unfortunately don't have the time. :-/

[al-sabr]

Thank you for the routing I will check that out and see if I understand the code.

In the mean time is there any other RDBMS like RQLite that has the functionalities I'm looking for maybe I don't need to implement anything.

Can you describe a little bit your development environment what are you using to code for this project?

[al-sabr]

I moved the thread to #642

Is it possible to also move some of our comments concerning single instance mode there too ?

[al-sabr]

Repo created am I on the good path?

https://github.com/gdeverlant/rqlite/tree/single-node

[otoolep]

Makes sense to me.