--- tags: DB --- # final - 考試重點 ## What is a transaction A transaction is **a logical unit of work** composed of one or more SQL statement. ex. ```sql= SELECT CUST_NUMBER CUST_BALANCE FROM CUSTOMER ``` **transaction log:** A record for **the beginning of transaction** ## Transaction properties [ACID](https://ithelp.ithome.com.tw/articles/10247034) - **Atomicity** - **All** transaction operations must be **completed**. - Incomplete transactions aborted. - **Consistency** - Permanence of the database's consistent state. - **Isolation** - Data used during execution of a transaction cannot be used by second transaction **until first one is completed** - **Durability** - Once trasaction changes are done (**commited**), they cannot be undone or lost. - **Serializability** - Concurrent transcation are treated as though they were executed in serial order (one after another) - Ensures that the concurrent execution of several transactions yields consisitent results ## What is a Deadlock? Condition that occurs when two transaction wait for each to unlock data. ## Four Conditions for deadlock - **Mutual Exclusion** Condition - each resource assigned to 1 process or is available - **Hold and Wait** Condition - process holding resources can requset additional - **No Preemption** Condition - previously granted resources cannot forcibly taken away - **Circular Wait** Condition - must be a **circular chain** of 2 or more processes - each is waiting for resource held by next member of the chain ## How can it be avoided? The transaction must obtain all the locks it needs before it can be executes. ## What is an exclusive lock? An exclusive lock doesn't allow other transactions to access the database. The exclusive lock is granted if and only if no other locks are hold on the data item. ## What is a shared lock? A shared lock allows other read only transaction to access the database. Shared lock exist when two transactions are granted read access. ## What is two-phase locking? 2PL defines how transactions acquire and relinquish locks 1. **Growing phase:** Transaction acquires all required locks without unlocking any data. 2. **Shrinking phase:** Transaction releases all locks and cannot obtain any new lock. ## What is two-phase commit protocol? Because multisite and multiple-process are much more likely to create data inconsistencies and deadlock. If a portion of a transaction cannot be commited, all changes made at the other sites will be undone to maintain a consistent state. ## What is data replication? Data replication refers to the storage of data copies at multiple sites served by a computer network. - Three strategies: - **Fully replicated database** stores multiple copies of **each** database fragment at multiple sites - **Partially replicated database** stores multiple copies of **some** database fragments at multiple sites - **Unreplicated database** stores each database fragment at a single site ## Levels of Backup - **Full backup** of the database - It backs up or dumps the whole database. - **Differential backup** of the database - Only the **last modifications** done to the database are copied. - Backup of the **transaction log only** - It backs up all the transaction log operations that are not reflected in a previous backup copy of the database.