【COMP207 Final】

最新推荐文章于 2024-07-07 12:29:28 发布

WINNER505

最新推荐文章于 2024-07-07 12:29:28 发布

阅读量324

点赞数

分类专栏： COMP207 文章标签： mysql sql 数据库

本文链接：https://blog.csdn.net/weixin_64482265/article/details/128722618

版权

COMP207 专栏收录该内容

8 篇文章 3 订阅

订阅专栏

1. INSERT ：

INSERT INTO transaction( , , ,) VALUES ( , , , );

3. Create table :

1. 注意table属性的顺序例： from_account, to_account

2. 注意table在create的时候要有名字

3. 注意有时候varchar(5)的时候不够大，需要varchar(50)

4. CONSTRAINT primary key and foreign key

1. 注意名字不要搞反了

2. 后面reference的时候要加表格例：

CONSTRAINT FK_Transactions_From_Account FOREIGN KEY (from_account) REFERENCES Accounts(id)

5. Group by

6. Relational Algebra

7. ACID properties

4 level of Isolation：

A更新的时候B都可以读取

A只有读取时B可以读取更新

A读取时B都只能读取

依次进行

8. Undo / Redo

>> Property do we need to ensure both atomicity and durability

UNDO : Force / Steal

REDO : No Force / No Steal

>> Undo X,Y,Z 值

从下往上看，哪个transaction被commit了就忽略哪个，然后最上面保留的xyz值就是最后的值

9. Cases is it required and sufficient to change the log when Undo/Redo logging

At the start of a transaction
On committing a transaction
On aborting a transaction
When a transaction writes an item

10. Shorthand notation of schedule

从上往下，不管是T1还是T2，COMMIT要写c1，c2

11. Shared buffer change

解析：这里T2读到的x是没有被T1更改的，所以还是22，并且他比T1晚write，write就是把值写入buffer的过程，所以最后buffer的值是最后write的值

12. Conflict

13. Strict

1. A schedule is strict if each transaction in it reads and writes only values that were written by transactions that have already committed. 就是如果是同一事务，只能read和write已经被commit的

14. Recoverable

1. 如果T2读取了T1的数据，那么T1应该先于T2commit

15. Cascadeless Schedules

1. T1的读，写， commit 都在T2的读，写，commit前面，那么就是cascadeless schedule

2. Cascadeless 的 schedule 都是 recoverable的

15. Conflict-Serializability

1. Precedence graph 没有圈

2. 所以没有conflict的肯定是conflict-serializability

16. Strict 2PL (two-phase locking )

1. Conflict-serialisability and Strict schdules

2. Strict 2pl 也可能有deadlock

17. Valid 2PL transaction

1. 第一个unlock之后就不再有lock了不管是什么lock

2. shared lock (s-lock) 后只能是read 不能是write

3. 注意shared lock(x)后的read一定要是read(x),不能是其他

18. Timestamp ( LEC15 )

1. Each transaction T is assigned a unique integer TS(T) upon arrival (the timestamp of T). If T1 arrived earlier than T2, we require TS(T1) < TS(T2) 哪个transaction更早执行，timestamp越小

2. Timestamps for Deadlock Detection里，即使事务restart，timestamp是不变的：

但正常情况下，restart后需要有新的timestamp：

3. Read time of X : RT(X):Timestamp of youngest transaction that read X

Write Time of X: WT(X) :Timestamp of youngest transaction that wrote X

4. Read Requests:

If T1 requests to read X: Abort & restart T1 if WT(X) > TS(T1)

Write Requests:

If T1 requests to write X: Abort & restart T1 if RT(X) > TS(T1) or WT(X) > TS(T1)

5. Starvation can occur (cyclic aborts & restarts of transactions)

6. Multiversion concurrency control

This means that a transaction only need to restart if it tries to write AND the read timestamp is later than your timestamp (同时满足1. write 2. read timestamp RT(X) > TS(T1)