Tool to execute commands on two SQL servers

Question

I am learning about database transactions and isolation levels. When I want to do some experiments on what I've read in the book, there are some difficulties.

First of all, I can't simply execute a single .sql file to see the experiment result. I have to open 2 database connections, and then type SQL commands back and forth between these 2 connections.

For example, when I want to reproduce dirty reads phenomenon, I have to do the following steps:

1. Make 2 database connections.
2. Create table and populate the required data.
3. Run "SELECT age FROM person WHERE id = 1;" on the first connection.
4. Run "UPDATE person SET age = 30 WHERE id = 1;" on the second connection.
5. Run "SELECT age FROM person WHERE id = 1;" on the first connection.
6. See the results.

There are some problems:

• It's really tedious to type the same SQL commands over and over again for different isolation levels.
• It's hard to take notes of what I've learned from a bunch of messy SQL commands.
• There is no easy way to share the experiments I've done.

Is there any good tool to solve these problems?

Answer 1

For my own problems, I developed duosql.

You can just create a .duo file containing all the SQL commands you want to execute in the connections. For example, you can create a file called demo.duo:

# connect command
connect: mysql -ujohnlinp -psecret somedb

# create table and populate data
left: DROP TABLE IF EXISTS person;
left: CREATE TABLE person (id INT NOT NULL AUTO_INCREMENT, name VARCHAR(255) NOT NULL, age INT NOT NULL, PRIMARY KEY (id));
left: INSERT INTO person (name, age) VALUES ('John Lin', 29);

# start left transaction
left: SET SESSION TRANSACTION ISOLATION LEVEL READ UNCOMMITTED;
left: BEGIN;
left: SELECT age FROM person WHERE id = 1;

# start right transaction
right: BEGIN;
right: UPDATE person SET age = 30 WHERE id = 1;

# continue left transaction
left: SELECT age FROM person WHERE id = 1;

# continue right transaction
right: ROLLBACK;

Then run:

$ duosql demo.duo

Then you can watch the SQL commands executed sequentially within 2 TMUX panels.

It's easy to repeat the experiments, or share with others as long as they have duosql installed.