I found that creating a new transaction will open a new database connection, but closing the transaction will not close the current connection. How can I manually close the connection?
There is no need to manually close the connection. Mendix uses a connection pool with the database to optimize response times when a microflow needs to communicate with the database. If a connection is already open in the pool and available, Mendix will use it for a microflow. If not, it will create a new connection, up to a specified limit, configurable in a custom runtime setting. When the microflow completes, it releases the connection back to the pool.
When connections are unused for some time (also configurable), the runtime closes some of them.
There's a difference between a connection and a transaction.
A microflow is executed within a transaction, submicroflows are executed within that same transaction. At the end of the (main) microflow, the transaction is ended. Only at this point are changes actually committed to the database.
You can force a transaction to be ended. In the CommunityCommons module for example there are the endTransaction and startTransaction actions. These can be used to manipulate Mendix's default transaction behaviour, but as also mentioned in this thread, be carefull with this, as it could lead to unexpected and weird behaviour.
There are other alternatives in the community commons module:
A transaction makes use of a db connection. When the transaction is ended, the db connection can remain open for connection pooling, so next transactions can use an already existing db connection. (setting up a db connection is relatively heavy on performance, so by reusing them, performance is improved).
As Eric said, you don't have to worry about closing db connections normally. Only if you run into specific problems, there are settings you can tweak. For this, you can take a look at: Tricky Custom Settings: Connection Pooling
Not a proper answer, but comments are not nice for posting links.
From what you wrote in a comment above I assume you already read https://forum.mendixcloud.com/link/questions/2887 :)
did you also find this https://forum.mendixcloud.com/link/questions/2912 ? It links back to the pseudo code you know about but maybe you can use the extra bit of info in the comments there.