Homework answers / question archive / Experiment 7?Transaction and Concurrency Control Goal To practice the To grasp the concept transaction, and how to create the transaction

Experiment 7?Transaction and Concurrency Control Goal To practice the To grasp the concept transaction, and how to create the transaction

Computer Science

Share With

---

1. Experiment 7?Transaction and Concurrency Control
   1. Goal

To practice the

1. To grasp the concept transaction, and how to create the transaction.
2. Understand the data inconsistency problems in concurrency operation, and can use lock and ioslation mechanisms

 

1. 2. Content

  Suppose that the university allows students to bind the bank card with the campus card. There are the following basic tables in the student database, in which the campus card number (cardid) is the student number:

icbc_card(studcardid, icbcid, balance) //campus ID?icbc bank ID?balance of bank card

campus_card(studcardid, balance)    // campus card ID?c_card balance

Some example data in this experiment

create table icbc_card(

icbcid int,

sno varchar(8),

balance int

);

create table campus_card(

sno varchar(8),

balance int

);

insert into campus_card values ('20200032', 1);

insert into campus_card values ('20200033', 100);

insert into icbc_card values (1, '20200032', 300);

insert into icbc_card values (2, '20200033', 400);  

 

According to the requirements, try to complete the following experiments based on the database above:

1. Write a transaction to achieve the following operations: a student( student number is 20200032) transfers 200 yuan from the bank card to the campus card, and if there is a failure during the transfer process, it will be rolled back. (10 points)
2. According to the database and tables, use specific examples to show several data inconsistency problems: such as missing and modifying, reading dirty data, non repeatable reading and phantom reading (deletion and insertion). If there is any situation that cannot be displayed, please explain the reasons. (20 points, 10 points for each data inconsistency)
3. By using the isolation levels or lock mechanism of the database, design solutions to solve the data inconsistency problems you have set in question 2. (20 points, 5 points for each data inconsistency)
4. Construct two transactions and update one tuple in the database at the same time. Try to use the following SQL commands to view and understand the feed back information of transaction and lock status in the current system. (10 points)

 

• show engine innodb status (MySQL 8.0 or 5.7)
• select * from information_schema.innodb_trx  (MySQL 8.0 or 5.7)
• select * from performance_schema.data_locks; (MySQL 8.0)
• select * from sys.innodb_lock_waits; (MySQL 8.0)
• select * from information_schema.innodb_lock_waits (MySQL 5.7)
• select * from information_schema.innodb_locks (MySQL 5.7)

5. Construct a deadlock situation. (10 points)
6. Construct the transaction containing some ‘savepoint’ and roll back to a savepoint at a certain time. (10 points)
7. Through experiments try to check all kinds of logs in MySQL: query log, error log and slow query log. (10 points)
8. Use mysqlbinlog to view the transaction log of the database, and try to recover the data according to the following scenarios. (10 points)

Steps?

1) crate db1, and create table t1, t2 . Table t1,t2 structures are the same: create table t1 (id int);

2) Insert data into t1: 11,12,13

3) Insert data into t2: 21, 22, 23

3)  drop table t1;

4) t2 can also be used normally, insert data 24 into t2

After finished the 4^th step above, use mysqlbinlog to recover T1 data.

 

The SQL of the above four steps is as follows:          

create database db1;

create table t1(id int);

create table t2(id int);

insert into t1 values(11);

insert into t1 values(12);

insert into t1 values(13);

insert into t2 values(21);

insert into t2 values(22);

insert into t2 values(23);

drop table t1;

insert into t2 values(24);