CS634 – Homework 5

Due Mon., Mar. 25

Transactions, Locking, more DBA work

Problem 1 Loading the bench table into your own mysql

See /var/local/cs634/setq for a copy of the Set Query Benchmark setup files for Oracle that were previously used to load the bench table on Oracle that you queried in homework 2. Also there is loadbench_mysql.sh for the mysql bulk load. Create a hw5 directory under your /var/local directory for your files for this assignment. Copy *.sql and *.sh from setq to your hw5 directory. Don't copy the huge data file.  Also copy ../mysql.sh to this directory so you can run your own mysql conveniently. Convert them to set up the bench table in your own mysql installation, in the database <username>db that you created in hw3. Use loadbench_mysql.sh for the bulk load.

setq_cre.sql: create table (modified from Oracle case)
loadbench_mysql.sh: load the table from /var/local/cs634/setq/bench1M.dat (provided)
setq_index1.sql: build secondary indexes (modified from Oracle case)
analyze.sql: run analyze on bench (modified from Oracle case)
check_bench: get basic info on table from catalogs (modified from Oracle case)
scan.sql: sample table scan, with one row of output
queryK500K.sql: your query from hw2 that uses K500K (on column with index)
queryK250K.sql: your query from hw2 that uses K250K (on column without index)

Note: for my eoneil1 database,  I have

mysql.sh:  
mysql --defaults-file=/home/eoneil/.my.eoneil1.cnf --verbose --port=3506

/home/eoneil/.my.eoneil1.cnf:   Keep .my.cnf for system-wide mysql use.
[mysql]
user=eoneil1
password=eoneil1
database=eoneil1db
socket=/var/local/cs634/eoneil1/run/mysqld/mysqld.sock

a. Run the timings of hw2 testing the effectiveness of the index. You can bring the server down and up again ("bounce" the server) to empty the buffer pool. Again use uptime to check that the system is not loaded. Specifically: bounce server, run queryK500K.sql, bounce server, run queryK250K.sql. Also try running scan.sql twice in a row without bouncing the server to see if it is faster the second time due to buffering some pages (bounce server, run scan.sql, run scan.sql again). Note that OS buffering is in use here, so bouncing the server does not remove all the buffering.

b. To make mysql more like Oracle, and avoid buffering data twice over, turn off the use of OS buffering by putting  innodb_flush_method = O_DIRECT in my.cnf (in the [mysqld] area).  Rerun the experiments of a after bouncing the server to set the new parameter. Now the buffer pool should be the only source of buffering, and it is too small (128MB) to hold the bench table.

c. Reconfigure the buffer pool size to 500MB and rerun the two-scans experiment. Explain how you increased the buffer size and the results of the experiment. Put the buffer pool size back to its original value when you're done.

Worked example of Schedules and 2PL locking, for next questions:

Recall the example from class 18: original sequence

R₁(A) R₂(A) R₂(B) R₁(B) W₂(B) R₂(B) W₂(B) C₁  C₂

Running with strict 2PL, with T2 requesting a write lock for R(B) W(B):

The following sequence shown on the slide assumes the R1(B) executes after X2(B) but before the R2(B):

S₁(A) R₁(A) S₂(A) R₂(A) X₂(B) <S₁ (B)-blocked> R₂(B) W₂(B) C₂  <S₁ (B)-unblocked> R₁(B) C₁

But the simple interpretation of the above sequence is as follows, where T2 does the R2(B) just after getting the lock for it, so use this as a model:

S₁(A) R₁(A) S₂(A) R₂(A) X₂(B) R₂(B) <S₁ (B)-blocked> W₂(B) C₂  <S₁ (B)-unblocked> R₁(B) C₁

Problem 4

• Determine if the following schedule is conflict serializable by indicating conflicts by arcs connecting operations and constructing the associated precedence graph. If it is, then find the equivalent serial schedule. The number after each R/W represents the transaction identifier executing each action. Show the precedence graph, and write down the serialization order.

 R1(A) R2(B) W1(B) W2(C)R3(C)W3(A) C1 C2 C3

•  Show how the schedule would run under 2PL, by adding appropriate locking calls, and blocking if necessary, to this sequence, as in the above example. Compare the resulting serialization order to what you found in 4.

Problem 5

• Determine if the following schedule is conflict serializable by constructing the associated precedence graph. If it is, then find the equivalent serial schedule. The number after each R/W represents the transaction identifier executing each action. Show the precedence graph and write down two possible serialization orders.

R2(X) W3(Y) R1(Y) W2(X) W1(X) W1(Y) W4(Y),W4(X) C1 C2 C3 C4

• Then show its execution under Strict 2PL locking as in Question 4. What is this schedule’s serialization order?

Problem 6

• Determine if the following schedule is conflict serializable by constructing the associated precedence graph. If it is, then find the equivalent serial schedule.

Run the following sequence under Strict 2PL. Show the waits-for-graph as the sequence of actions progresses.

W1(A) R2(B) W2(B) W3(C) R1(B) W1(B) W2(C) W3(A) C1 C2 C3 C4

• On deadlock, abort the lowest-numbered transaction and restart it. Show what happens under 2PL.