{"action":"created","issue":{"url":"https://api.github.com/repos/go-goracle/goracle/issues/126","repository_url":"https://api.github.com/repos/go-goracle/goracle","labels_url":"https://api.github.com/repos/go-goracle/goracle/issues/126/labels{/name}","comments_url":"https://api.github.com/repos/go-goracle/goracle/issues/126/comments","events_url":"https://api.github.com/repos/go-goracle/goracle/issues/126/events","html_url":"https://github.com/go-goracle/goracle/issues/126","id":384873220,"node_id":"MDU6SXNzdWUzODQ4NzMyMjA=","number":126,"title":"Call dpiConn_release on transaction commit or rollback","user":{"login":"rvegas","id":5328717,"node_id":"MDQ6VXNlcjUzMjg3MTc=","avatar_url":"https://avatars2.githubusercontent.com/u/5328717?v=4","gravatar_id":"","url":"https://api.github.com/users/rvegas","html_url":"https://github.com/rvegas","followers_url":"https://api.github.com/users/rvegas/followers","following_url":"https://api.github.com/users/rvegas/following{/other_user}","gists_url":"https://api.github.com/users/rvegas/gists{/gist_id}","starred_url":"https://api.github.com/users/rvegas/starred{/owner}{/repo}","subscriptions_url":"https://api.github.com/users/rvegas/subscriptions","organizations_url":"https://api.github.com/users/rvegas/orgs","repos_url":"https://api.github.com/users/rvegas/repos","events_url":"https://api.github.com/users/rvegas/events{/privacy}","received_events_url":"https://api.github.com/users/rvegas/received_events","type":"User","site_admin":false},"labels":[],"state":"open","locked":false,"assignee":null,"assignees":[],"milestone":null,"comments":0,"created_at":"2018-11-27T16:16:04Z","updated_at":"2018-11-27T17:33:50Z","closed_at":null,"author_association":"NONE","body":"Hello, I've been further running investigating a bug we currently have in our architecture, according to #124 every time `commit` or `rollback` is called upon a transaction, the connection that hangs from the `TX` should be released.  \r\n  \r\nI ran into this situation:\r\n\r\n1. Open a database pool against a `Oracle DB`\r\n2. Run a `BeginTransaction` with goracle\r\n3. Run `netstat` and find **1** connection was establised\r\n4. Run a second `BeginTransaction` in the same pool\r\n5. Run `netstat` and find **2** connections established\r\n6. Commit the first transaction\r\n7. Run `netstat` and find **2** connections established\r\n7.1 In here I was expecting for the pool to eventually shrink back to 1\r\n8. Run another `BeginTransaction` with the same pool\r\n9. Run `netstat` and find **3** connections established\r\n9.1 I was expecting `goracle` to reuse the one that belonged to the commited TX.  \r\n  \r\nAnyways, other permutations of the test end up in a similar result:  \r\n- `netstat` finding an ever increasing number of connections in **ESTABLISHED** \r\n- committing or rolling back transactions not decreasing this number\r\n- new transactions that are open eventually open up new connections\r\n- hit the ` ORA-02391 exceeded simultaneous SESSIONS_PER_USER limit Errors` error  \r\n  \r\n  \r\nFurther digging down I came to this piece of code, which is where `goracle` handles *commits and rollbacks* via **C**:  \r\n```//-----------------------------------------------------------------------------\r\n// dpiConn_commit() [PUBLIC]\r\n//   Commit the transaction associated with the connection.\r\n//-----------------------------------------------------------------------------\r\nint dpiConn_commit(dpiConn *conn)\r\n{\r\n    dpiError error;\r\n\r\n    if (dpiConn__check(conn, __func__, &error) < 0)\r\n        return dpiGen__endPublicFn(conn, DPI_FAILURE, &error);\r\n    if (dpiOci__transCommit(conn, conn->commitMode, &error) < 0)\r\n        return dpiGen__endPublicFn(conn, DPI_FAILURE, &error);\r\n    conn->commitMode = DPI_OCI_DEFAULT;\r\n    return dpiGen__endPublicFn(conn, DPI_SUCCESS, &error);\r\n}\r\n```\r\n  \r\nAnd I also came to this function:  \r\n```\r\n//-----------------------------------------------------------------------------\r\n// dpiConn_release() [PUBLIC]\r\n//   Release a reference to the connection.\r\n//-----------------------------------------------------------------------------\r\nint dpiConn_release(dpiConn *conn)\r\n{\r\n    return dpiGen__release(conn, DPI_HTYPE_CONN, __func__);\r\n}\r\n```  \r\n  \r\nI see that `goracle` only calls for connection release when closing the actual connection object, I guess it's because Go's `sql` should take care of that, just haven't dug that deep.  \r\n  \r\nAnyways, any insight regarding this can be very helpful for me. Thanks in advance."},"comment":{"url":"https://api.github.com/repos/go-goracle/goracle/issues/comments/442147954","html_url":"https://github.com/go-goracle/goracle/issues/126#issuecomment-442147954","issue_url":"https://api.github.com/repos/go-goracle/goracle/issues/126","id":442147954,"node_id":"MDEyOklzc3VlQ29tbWVudDQ0MjE0Nzk1NA==","user":{"login":"tgulacsi","id":256074,"node_id":"MDQ6VXNlcjI1NjA3NA==","avatar_url":"https://avatars2.githubusercontent.com/u/256074?v=4","gravatar_id":"","url":"https://api.github.com/users/tgulacsi","html_url":"https://github.com/tgulacsi","followers_url":"https://api.github.com/users/tgulacsi/followers","following_url":"https://api.github.com/users/tgulacsi/following{/other_user}","gists_url":"https://api.github.com/users/tgulacsi/gists{/gist_id}","starred_url":"https://api.github.com/users/tgulacsi/starred{/owner}{/repo}","subscriptions_url":"https://api.github.com/users/tgulacsi/subscriptions","organizations_url":"https://api.github.com/users/tgulacsi/orgs","repos_url":"https://api.github.com/users/tgulacsi/repos","events_url":"https://api.github.com/users/tgulacsi/events{/privacy}","received_events_url":"https://api.github.com/users/tgulacsi/received_events","type":"User","site_admin":false},"created_at":"2018-11-27T17:33:50Z","updated_at":"2018-11-27T17:33:50Z","author_association":"CONTRIBUTOR","body":"I could release the connection after Commit/Rollback, but how should we inform the database/sql pool that this connection is unusable? We could mark the connection and force it to return ErrBadConn on every succeeding action, but this is gross.\r\n\r\nPlease, what is your REAL problem? Really 3 TCP connections is too much?\r\nThe number of sessions should stabilize, esp. with correctly set poolMax.\r\n\r\nWhat is your use case? How many concurrent transactions, queries?\r\nCan you create a small reproducing test case? "}}
{"actor":{"display_login":"tgulacsi"}}