Transcript Git Version Control and Projects

Transactions
How do we measure performance?
Example Databases:
◦ Web content server supporting a forum
◦ HackerRank leaderboard holding high scores
◦ Medical images server containing dental X-rays
◦ Tax accountant database tracking payments
All of these desire two things:
◦ Throughput - How many requests can the database handle
◦ The more requests a database can handle, the more useful the database is
◦ Response Time - How long does it take to handle a request
◦ Low latency (short response time) means the requests are handled quickly
How do we make faster databases?
Same way we make any program faster:
◦ Run it on a faster computer (better hardware)
◦ Write it to use less computational resources (optimizations)
◦ Reduce unnecessary IO (better cache performance)
But the biggest way is to allow for concurrency
◦ Instead of only allowing one connection (and one command) to run at a time, we allow for multiple
executions.
◦ This means we can utilize the multiple cores / processes / threads that the computer can give us.
◦ It also means we can get other work done while waiting for the slow parts (human interaction, disk IO,
long SQL queries)
But concurrency can add complexity (as we will see)
Transaction
In database vocabulary, a transaction is a unit of execution. The
DBMS strives to ensure that transactions are all-or-nothing affairs.
This means that one of two things must happen after a transaction is
completed:
◦ Either, the transaction successfully read/modified the database,
◦ Or, the transaction was aborted and made no changes to the database.
To make it easier to reason about transactions, the database ensures
that incomplete transactions can't have an effect on the database
Transaction A (Add Josh)
Original Table "students"
INSERT INTO students
VALUES ('Josh', 3.5);
name
grade
Tyler
3.2
Grant
3.4
A then B
Transaction B (Improve Grade)
UPDATE students SET grade
= grade + 0.5;
B then A
name
grade
name
grade
Tyler
3.7
Tyler
3.7
Grant
3.9
Grant
3.9
Josh
4.0
Josh
3.5
Which is the correct final table?
Both are Okay
A then B
Transaction A (Add Students)
name
grade
INSERT INTO students
VALUES ('Josh', 3.5);
Tyler
3.7
Grant
3.9
A(1) then B then A(2)
Josh
4.0
name
grade
Charles
2.7
Tyler
3.7
Grant
3.9
Josh
4.0
Charles
2.2
INSERT INTO students
VALUES ('Charles', 2.2);
Transaction B (Improve Grade)
UPDATE students SET grade
= grade + 0.5;
Original Table "students"
name
grade
Tyler
3.2
Grant
3.4
B then A
name
grade
Tyler
3.7
Grant
3.9
Josh
3.5
Charles
2.2
This is bad! Josh got an
improved grade, but Charles
didn't. This violates the ACID
test!
ACID
Properly implemented transactions are commonly said to meet the “ACID test,”
where:
◦ A - stands for atomicity, the all-or-nothing execution of transactions.
◦ I - stands for isolation, the fact that each transaction must appear to be executed as if no
other transaction is executing at the same time.
◦ D - stands for durability, the condition that the effect on the database of a transaction must
never be lost, once the transaction has completed.
The remaining letter, C, stands for consistency. That is, all databases have
consistency constraints, or expectations about relationships among data
elements (e.g., account balances may not be negative after a transaction
finishes). Transactions are expected to preserve the consistency of the database.
When Josh's grade was improved, but Charles'
wasn't, what ACID property was violated?
Atomicity
Consistency
Isolation
Durability
Consistency
A database has a state, which is a value for each of its elements.
Intuitively, we regard certain states as consistent, and others as inconsistent.
Consistent states satisfy all constraints of the database schema, such as key constraints and
constraints on values.
However, consistent states must also satisfy implicit constraints that are in the mind of the
database designer.
Examples:
◦ Conservation of Money - no transfer of money between accounts should change the total amount of
money represented in the database
◦ Valid Data - If I remove a student from the table "students", they will also be removed the table
"friends".
The Correctness Principle:
"If a transaction executes in the absence of any other transactions or
system errors, and it starts with the database in a consistent state,
then the database is also in a consistent state when the transaction
ends."
All transactions must conform to this rule.