Part $1$ Transactions

In session A$,$ using your own database, insert a new record to one table. That is$,$ execute an $$INSERT INTO$$ statement with session A$.$ You need to make up the data.

Check that the item was inserted by executing a SELECT statement.

Now switch to session B$,$ and check again the content of the table with the same SELECT statement.

Do you see the record that you just inserted with session A$?($hint: the answer is no$!)$

Why?

Go back to session A and click the $$Commit the current transaction$$ button $($the Checkbutton to the left of the auto$-$commit toggle button$).$

Go to session B and check again the content of table that you inserted into.

Do you see the record that you just inserted with session A$?$

Why?

Part $2$ Locking

With session A$,$ write an UPDATE statement to modify an existing record. Again, you need to make up the data. My example is $($you need to completely rewrite this for your database$)$:

UPDATE instructor

SET lname $=$LIU$$

WHERE id $=1$;

Check with a SELECT statement that the record is updated.

Now switch to session B$,$ write an UPDATE statement to modify this same record. For example:

UPDATE instructor

SET lname $=$Luke$$

WHERE id $=1$;

Are you able to complete the update? What happens with MySQL$?$

Why?

Now back to session A and commit.

What happens to your session B now? Are you able to complete the update?

Why?

Making sure there is no active transactions by committing both sessions $($otherwise your session B is still not committed$).$

Try again with the UPDATE statements, but this time update two different records from the same table, separately in the two sessions. For example, you may update the record with id $=1$ in session A$,$ and the record with id $=2$ in session B$.$

Are you able to do this concurrently?

What does it mean? Does your transactions lock the entire table or just records?