Transactional commit does not work when autocommit is off and a database is not selected

[VinaiRachakonda]

Consider the following example:

1. Client 1 is modifying a table (with the database being unselected) and turns off autocommit and makes an update

mysql> set autocommit = off;
Query OK, 1 row affected (0.00 sec)

mysql> select * from dsimple.tblPersons;
+-----------+------------+-----------+------------+
| person_id | first_name | last_name | random_txt |
+-----------+------------+-----------+------------+
|         1 | dasda      | adasda    | dasda      |
+-----------+------------+-----------+------------+
1 row in set (0.00 sec)

mysql> insert into dsimple.tblPersons values (2, 'vinai', 'rachakonda', 'dads');
Query OK, 1 row affected (0.00 sec)

mysql> commit;
Query OK, 0 rows affected (0.00 sec)

2. Client 2 connects and should see updated data but does not.

mysql> use dsimple;
Reading table information for completion of table and column names
You can turn off this feature to get a quicker startup with -A

Database changed
mysql> describe tblPersons;
+------------+--------------+------+------+---------+-------+
| Field      | Type         | Null | Key  | Default | Extra |
+------------+--------------+------+------+---------+-------+
| person_id  | int          | NO   | PRI  | NULL    |       |
| first_name | varchar(100) | YES  |      | NULL    |       |
| last_name  | varchar(200) | YES  |      | NULL    |       |
| random_txt | text         | YES  |      | NULL    |       |
+------------+--------------+------+------+---------+-------+
4 rows in set (0.00 sec)

mysql> select * from tblPersons;
+-----------+------------+-----------+------------+
| person_id | first_name | last_name | random_txt |
+-----------+------------+-----------+------------+
|         1 | dasda      | adasda    | dasda      |
+-----------+------------+-----------+------------+
1 row in set (0.01 sec)

The offending line of code is here.

I will followup with a clear skipped bats or engine test

[VinaiRachakonda]

This issue was found while integrating Dbeaver with Dolt. @jennifersp please do a deep dive of Dbeaver with Dolt as per the "First 50 steps" spreadsheet.

[VinaiRachakonda]

This is related to the issue: #3521

[VinaiRachakonda]

Update: After studying this more closely both this issue and issue #3521 need to be fixed for Dbeaver to be working robustly.

There are several requirements

1. I should be able to commit changes when a database is unselected. This is enough to solve the example above.
2. I should not be able to commit changes to multiple databases in a single transaction. In the future we will fix this
3. A session that has selected Database A should be able to read the latest committed changes from Database b

[zachmu]

To fix this, we need to move the interface for transactions off of sql.Database onto sql.Session. Then the session implementation can decide what semantics it wants to enforce when dealing with multiple DBs being edited in the same session.

[timsehn]

Merging this and #3521