Question Re: Is alter user doing an auto-commit? in Technology Q&A

Is alter user doing an auto-commit?

MCMartin — Thu, 15 Dec 2022 07:33:55 GMT

Is alter user to change the password doing an auto-commit or is a rollback of alter user possible?

Re: Is alter user doing an auto-commit?

VolkerBarth — Thu, 15 Dec 2022 07:49:30 GMT

As to the docs, no (as you surely have found out yourself), whereas the similar GRANT CONNECT and CREATE USER are told to do so.

But this should be easy to test.

And just in case: You can do point-in-time-recovery with v17, so possibly to recover all operations until an undesired committed password change...

Re: Is alter user doing an auto-commit?

chris_keating — Thu, 15 Dec 2022 08:33:16 GMT

CREATE USER and ALTER USER have an automatic commit side effect. DDL statements have automatic commit side effect (there may be exceptions, but I cannot think of any). The SAP based Help has been updated to indicate that CREATE USER has an automatic commit but the same was not done for ALTER USER. I have reported that documentation issue.

Re: Is alter user doing an auto-commit?

MCMartin — Thu, 15 Dec 2022 08:37:11 GMT

Im wondering if it is missing from the docs or if my observation is wrong.

Re: Is alter user doing an auto-commit?

jack_schueler — Fri, 16 Dec 2022 09:59:35 GMT

I believe that there is also a side affect on server-side connection pooling, in that the cache is cleared when ALTER USER is executed.