Can SQL-Adapter handle transaction(begin/insert/roolback)?

Question

Worklight Studio v6.2
MySQL Community Server 5.6.21

I did simple experiment for this. I created 4 procedures in a sql adapter.

"begin" procedure, which invokes just "BEGIN;" statement.
"commit" procedure, which invokes just "COMMIT;" statement.
"rollback" procedure, which invokes just "ROLLBACK;" statement.
"insert" procedure, which invokes "INSERT" statement which insert a row to a table.

In addtion,I created hybrid app which has buttons. Each button executes the procedure above.

I opened it by browser for test. I execute "begin -> insert -> commit" and "begin -> insert -> rollback". Both work fine.

Next, I opend the application from two browsers(A/B). And I execute below.

1.From browser A:begin
2.From browser A:insert
3.From browser B:commit

It is committed.
The database can not distinguish that each request comes from diffrent user by this way.
Any solutions?

Thanks in advance.

Answer 1

Update: What you could try is:

1. Make your code more atomic, in the sense that instead of 4 procedures, make all of these various calls in 1 procedure, or
2. Unique-ify your SQL query, by sending with it a unique identifier ... this way there shouldn't be any mixing between browsers' sessions. You will need to verify that you are operating on this unique identifier.

---

Edit: this is not the correct soution, as connectAs="endUser" is not supported in SQL adapeders

I think what you need to do is add the connectAs="endUser" attribute to the adapter procedures in the in the adapter XML file: <procedure name="myProcedure" connectAs="endUser"/>.

From this following blog post, which you should read, connectAs="endUser" means:

In order to support stateful backend HTTP adapter procedures can be configured to work in a connectAs=“endUser” mode. This mode means that a separate instance of HTTP session will be created for each client session

Blog post: Configuring HTTP adapters for stateless/stateful backend connectivity and user identity propagation