1

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?

  • "First of all, I can't simply execute a single .sql file to see the experiment result. I have to open 2 database connections" ... why? Do you have some strange setup? Or could it be that you have misunderstood some concept? If you can explain to us … the more detail you give in your question, the better the help that you will receive. – Mawg Apr 18 at 7:45
  • 1
    Hi @Mawg, Thanks for the comment. I added an example of how I did the experiments. Please take a look. Thank you. – johnlinp Apr 19 at 4:11
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.

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.