[ExamVT13] Block 6: Transactions

[Niklas Broberg]

============================================

Schema reposted from previous block for convenience:

Tracks(trackId, title, length)

 length > 0

Artists(artistId, name)

Albums(albumId, title, yearReleased)

TracksOnAlbum(album, trackNr, track)

 album -> Albums.albumId

 track -> Tracks.trackId

 (album, track) unique

 trackNr > 0

Participates(track, artist)

 track -> Tracks.trackId

 artist -> Artists.artistId

Users(username, email, name)

 email unique

Playlists(user, playlistName)

 user -> Users.username

InList(user, playlist, number, track)

 (user, playlist) -> Playlists.(user, playlistName)

 track -> Tracks.trackId

PlayLog(user, time, track)

 user -> Users.username

 track -> Tracks.trackId

 (user, time) unique

Use the relations for the music site from [above] when answering the following problems.

============================================

6A (3p)

Consider the situation where an administrator adds a new track to the database. Data then needs to be added to the tables Tracks, Participates and TracksOnAlbum. We assume for the sake of simplicity that relevant data already exists in the Artists and Albums tables, and also that the track being added is recorded by one single artist, and appears on one single album.

Consider the following program (partly in pseudo-code), for handling this situation. In the code I prefix program variables with : just to distingush them from attributes (i.e. you don’t need to worry about any connection to PSM or the like).

1 ... admin submits :title, :length, :artist, :album and :trackNr ...

2 SELECT MAX(trackId)+1 INTO newTrackId

   FROM Tracks

   WHERE post = :post;

3 INSERT INTO Tracks VALUES (:newTrackId, :title, :length);

4 INSERT INTO Participates VALUES (:newTrackId, :artist);

5 INSERT INTO TracksOnAlbum VALUES (:album, :trackNr, :newTrackId);

(i) (1p)

For the program as specified above, what atomicity problems could arise if it was not run as a transaction?

(ii) (2p)

For the program as specified above, what isolation problems could arise if it was not run as a serializable transaction?

============================================

6B (6p)

Consider the situation where a user asks the system to play the songs on one of her playlists, in order. Each time a new song begins playing, the system should log this fact. Consider the following program (partly in pseudo-code), for handling this situation. In the code I prefix program variables with : just to distingush them from attributes (i.e. you don’t need to worry about any connection to PSM or the like).

1 ... user (:user) asks to play list (:playlist) ...

2 SELECT MIN(number), MAX(number)

   INTO (currentTrackNr, lastTrackNr)

   FROM InList

   WHERE user = :user AND playlist = :playlist;

3 while (:currentTrackNr <= :lastTrackNr) {

4 SELECT track INTO currentTrack

   FROM InList

   WHERE user = :user AND playlist = :playlist

   AND number = :currentTrackNr;

5 INSERT INTO PlayLog VALUES (:user, NOW(), :track);

6 ... fetch and stream the requested track ...

7 SET currentTrackNr = currentTrackNr+1;

8 }

Compare what would happen if the program above was run as a transaction with isolation level SERIALIZABLE, to if it was run with isolation level READ COMMITTED. Point out benefits and drawbacks of the two choices for this particular problem, and suggest a suitable transaction strategy to use.

============================================

[Evgeny Kotelnikov]

Possible answer.

6A

(i) Atomicity problems arise when some queries don't get executed during execution of a program. Atomic transactions either execute all of them or none of them. If "INSERT INTO TracksOnAlbum" didn't get executed, we wouldn't see the track on the album. If "INSERT INTO Participates" didn't get executed either, we wouldn't also find the track by its artist. If "INSERT INTO Tracks" didn't get executed as well, we wouldn't have any information about the track whatsoever.

(ii) Isolation errors arise when two transactions work in parallel and affect each other's data. In this case, if right after execution of (2) query some other transaction adds a track to Tracks, :newTrackId variable would contain not the id for the new track, but the id of newly added track and (3) query will fail.

6B

There's a typo in the question. Read (4) query as "SELECT track INTO track ...".

The difference between SERIALIZABLE and READ COMMITTED is that the former provides total isolation of transactions by elimination of concurrency, whereas the latter allows transactions to work in parallel and only see changes when they are commited.

When the program is wrapped inside SERIALIZABLE transaction, no other transaction can execute (2) query, which ends up with other users not being able to play their playlists, while there's someone already plaing hers. This is of course completely unviable solution.

When the program is wrapped inside READ COMMITTED transaction we wouldn't have this problem. But now the user could delete one of the tracks from the playlist and "SELECT track" query will fail. This is, however, less of a problem, because we can just catch the error, skip the track and go on with the next one.

For these reasons, READ COMMITTED isolation level is more suitable for the task.

--
You received this message because you are subscribed to the Google Groups "tda357-vt2014" group.
To unsubscribe from this group and stop receiving emails from it, send an email to tda357-vt201...@googlegroups.com.
For more options, visit https://groups.google.com/d/optout.