CST363 - Week 22

In what situations would an index be helpful?  not helpful?

An index is helpful when a column is used frequently in search conditions and joins, thereby eliminating the need to scan the entire table each time, but rather go to the data by using the index. This speeds up the process and makes it more efficient. This is best when the column has a large number of distinct data and will not be updated frequently. Indexes are not helpful when the column is updated so frequently that it becomes a hassle to update the index each time a row is added, updated, or deleted.

What does consistency (the C in ACID) mean to you?

Consistency of data is making sure that the data is consistent before and after the transaction, in keeping with the requirements of the database. For example, in a banking transaction, consistency would be making sure the total amount of money is maintained after a transfer between a checking and savings account. The database should always be in a valid state.

What are the 4 isolation levels and what do they mean?

1. Serializable - Each action is performed one after the other, never concurrently. This prevents concurrency problems when reading and modifying data. However, performance declines because it has to manage every lock and increases access time for each restriction.

2. Repeatable Read - This is the default setting in which locks are put on all data used by the transaction so the rows read will be read consistently within the transaction and other users can't modify the data.

3. Read Committed - This prevents a transaction from seeing data that another transaction changed, but did not commit. This eliminates any problems in which the first transaction is rolled back and then the second transaction refers to data that doesn't exist (AKA dirty read).

4. Read Uncommitted - This level performs each query, executing them immediately without placing any locks. Therefore, this level has the best performance but does not help solve any concurrency issues.