Database Systems: Query Optimization and Transactions - Prof. Jun Huan, Study notes of Deductive Database Systems

Download Database Systems: Query Optimization and Transactions - Prof. Jun Huan and more Study notes Deductive Database Systems in PDF only on Docsity!

EECS 647: Introduction to

Database Systems

Instructor: Luke Huan

Spring 2009

4/15/

Luke Huan Univ. of Kansas

Query optimization

z^

One logical plan

!^

“best” physical plan

z^

Questions z^

How to enumerate possible plans z^

How to estimate costs z^

How to pick the “best” one

z^

Often the goal is not getting the optimum plan, butinstead avoiding the horrible ones1 second

1 hour

1 minute

Any of these will do

4/15/

Luke Huan Univ. of Kansas

Review DBMS Architecture

Query ExecutorBuffer ManagerStorage Manager

Storage

Transaction Manager

Logging &Recovery

Lock Manager Buffers

Lock TablesMain Memory

User/Web Forms/Applications/DBA

query

transaction

Query ParserQuery RewriterQuery Optimizer Files & Access Methods

4/15/

Luke Huan Univ. of Kansas

Query Compiler

query

Execution Engine

Logging/Recovery

LOCK TABLE Concurrency Control

Storage Manager

BUFFER POOL

BUFFERS

Buffer Manager

Schema Manager

Data Definition

DBMS: a set of cooperating software modules

Transaction Manager

transaction

Components of a DBMS

Correctness criteria: The

ACID

properties

z^ z

AA

tomicity: All actions in the Xact happen, or none happen. z^ z

CC

onsistency: If each Xact is consistent, and the DB starts consistent, it ends up consistent. z^ z

I^ solation: Execution of one Xact is isolated from that of otherI Xacts.

z^ z

DD

urability: If a Xact commits, its effects persist.

4/15/

Luke Huan Univ. of Kansas

An Example about SQL Transaction

z^

Consider two transactions (

Xacts

T1:

BEGIN

A=A+100,

B=B-

END

T2:

BEGIN

A=1.06*A,

B=1.06*B

END

-^

1st xact transfers $100 from B’s account to A’s

-^

2nd credits both accounts with 6% interest.

-^

Assume at first A and B each have $1000. What are thelegal outcomes of running T1 and T2???•^

-^

There is no guarantee that T1 will execute before T2 orvice-versa, if both are submitted together. But, the neteffect

must

be equivalent to these two transactions

running serially in some order.

4/15/

Luke Huan Univ. of Kansas

SQL transactions

z^

Syntax in pgSQL: BEGIN^ COMMIT [ROLLBACK] z^

A transaction is automatically started when a user executes anSQL statement (begin is optional) z^

Subsequent statements in the same session are executed as part ofthis transaction z^

Statements see changes made by earlier ones in the same transaction z^

Statements in other concurrently running transactions do not seethese changes

z^

COMMIT

command commits the transaction (flushing the update

to disk) z^

ROLLBACK

command aborts the transaction (all effects are

undone)

4/15/

Luke Huan Univ. of Kansas

Atomicity

z^

Partial effects of a transaction must be undone when z^

User explicitly aborts the transaction using

ROLLBACK

z^

E.g., application asks for user confirmation in the last stepand issues

COMMIT

or

ROLLBACK

depending on the

response z^

The DBMS crashes before a transaction commits

z^

Partial effects of a modification statement must beundone when any constraint is violated z^

However, only this statement is rolled back; thetransaction continues

z^

How is atomicity achieved? z^

Logging (to support undo)

4/15/

Luke Huan Univ. of Kansas

Concurrency control

z^

Goal: ensure the “I” (isolation) in ACID

A^

B

C

T^1

: read(

A ); write(

A );

read(

B ); write(

B );

commit;

T^2

: read(

A ); write(

A );

read(

C ); write(

C );

commit;

4/15/

Luke Huan Univ. of Kansas

Good versus bad schedules

T^1

T^2

r(

A )

w(

A )

r(

B )

w(

B )

r(

A )

w(

A )

r(

C

w(

C

T^1

T^2

r(

A )

w(

A )

r(

A )

w(

A )

r(

B )

r(

C

w(

B )

w(

C

T^1

T^2

r(

A )

r(

A )

w(

A )

w(

A )

r(

B )

r(

C

w(

B )

w(

C

Good!

Good! (But why?)

Bad!

Read 400

Read 400

Write400 – 100

Write 400 – 50

4/15/

Luke Huan Univ. of Kansas

Conflicting operations

z^

Two operations on the same data item conflict if at leastone of the operations is a write z^

r( X

) and w(

X ) conflict

z^

w(

X ) and r(

X ) conflict

z^

w(

X ) and w(

X ) conflict

z^

r( X

) and r(

X ) do not

z^

r/w(

X ) and r/w(

Y ) do not

z^

Order of conflicting operations matters z^

E.g., if

T

.r( 1

A ) precedes

T

.w( 2

A ), then conceptually,

T

1

should precede

T

2

4/15/

Luke Huan Univ. of Kansas

Precedence graph

z^

A node for each transaction z^

A directed edge from

T

to i^

T

if an operation of j^

T

i

precedes and conflicts with an operation of

T

in the j^

schedule

T^1

T^2

r( A

) w(

A )

r( A

) w(

A )

r( B

)

r( C

)

w(

B )

w(

C )

T^1

T^2

r( A

)

r( A

)

w(

A )

w(

A )

r( B

)

r( C

)

w(

B )

w(

C )

T^1 T^2 Good:no cycle

T^1 T^2 Bad: cycle

4/15/

Luke Huan Univ. of Kansas

Summary

z^

Transaction view of DBMS z^

Read(x) z^

Write(x)

z^

ACID z^

Atomicity: TX’s are either completely done or not done at all z^

Consistency: TX’s should leave the database in a consistent state z^

Isolation: TX’s must behave as if they are executed in isolation z^

Durability: Effects of committed TX’s are resilient against failures

z^

SQL transactions --

Begins

implicitly

SELECT

…;

UPDATE

…;

ROLLBACK

|

COMMIT;