PLEASE HELP Reading: Chapter 9 (Managing multi user database systems) Part A Que

Question

PLEASE HELP

Reading: Chapter 9 (Managing multi user database systems)

Part A

Question 1

1.1 Describe the Lost Update problem. Show an example.

1.2 What is/are solution(s) for lost update problems.

1.3 What is the problem when DBMS use lock approach? How to solve this problem

Question 2

2.1 What is the checkpoint? What is the role of checkpoint (why we need it)?

2.2 This is an exercise for your understanding to the process of database recovery.

There are 3 transactions (T1, T2, T3) since last backup. T1 finished successfully before the latest checkpoint p1. T2 finished successfully after p1. T3 was aborted after p1 due to main memory crash. Explain

how a DBMS deal with these transactions during the recovery process if memory crashed.

How DBMS does recovery if the hard drive crashed.

Question 3

Describe two phase locking (2PL) and serializability (Why it is needed, what it is, how it works).

Question 4.

Describe the ACID features/principles of a transaction. For each feature/principle, give an example that violates this feature. Namely, discuss a situation where a principle is violated and the corresponding problem(s). For example, what situation will cause a transaction not Atomic.

Part B SQL queries

Read Study the chapter 2 comparing the difference between join operation and subquery. Formulate the SQL statements for the following queires:

1. List name of employee work on two or more projects (use join, group by and having.)

2. List the name of employee who do not work on any project (hint: USE "IN" and sub-query)

3. List the name of project that has budget over the average budget of all project in company. (use sub-query)

4. List the name of division 'chen' does not work for.

Explanation / Answer

1)

1.1)Answer:

A lost update may cause data within the database to be incorrect, which can lead toproblems with normal operations, such as fulfilling customer orders. ... A lost update occurs when two different transactions are trying to update the same column on the same row within a database at the same time.

Example:

Lost update problem:
Deposit (10)
A = A+10;

View Balance:
print A

Now, suppose initial balance A is 100 someone deposits 20 by calling Deposit(10) two times and then called View Balance to view the updated balance. If they are called sequentially no problem here. But suppose they are called concurrently:

Suppose in first call to Deposit,  
A = A + 10;

Here, A is read, 10 is added to it and A+10 is stored back to A.  

Similarly happens in next call to Deposit. But if the read of A in second happens before the final store to A in first call, the first call of Deposit has no effect. The update done by first Deposit is lost (when the second Deposit stores the value it will be 110 and not 120 as it has read 100 instead of 110)- and this is called Lost update problem

1.2)Answer:

There are many techniques that can be used to prevent multiuser concurrency problems. I'll present a few of the most common ones here.

Ignore It

The simplest technique is to just ignore it, hoping it will never happen; or if it does happen, that there won't be a terrible outcome. This technique may sound unacceptable, but depending on the application, it can successfully be used. Consider an application in which users are in charge of updating their own data, such as their credit card information at an online auction site. In this kind of application, it would be very rare for the lost update problem to occur because a single user should not be in two places at once. If he was and it did cause a lost update problem, you might argue that it was the user's own fault. Of course, this technique has the benefit of not requiring any additional code, but is usually is unacceptable in many applications. It's especially improper for those applications in which money is involved.

Locking

Another popular technique for preventing lost update problems is to use locking techniques. With this approach, when a record is retrieved and will likely be updated (or even when there is only a remote possibility that it will be updated), the application tells the DBMS to lock the record so other processes can't retrieve and update it. It basically tells the DBMS, "I'm using this record, so don't let anyone else have it." Although this type of locking, more formally called pessimistic locking, can be used to prevent lost update problems, it introduces its own set of problems.

Not all DBMSs support the concept of record locking; some support only so-called page locking. Page locks lock a large chunk of data at a time—for example, 4KB—so they can potentially prevent more than one record from being retrieved. Still worse, some DBMSs support locking only at the table level, and some don't support it at all.

Another problem with locking is the way the DBMS handles a request for a record that is already locked. When this occurs, the DBMS won't immediately return an error saying the record is being used. Instead, it waits for a period of time, hoping the process that locked it will free the record in question. Eventually a "time-out" error will be returned if the DBMS gives up waiting for the record to be freed. This time-out period is typically a configuration setting, and it can last from a few seconds to several minutes.

If you are a user of an application that locks records, and you request a record someone else already has locked (maybe they are keeping it locked while they talk on the phone), you have to wait until either the record is freed by the other user or until the DBMS returns a "time-out" error. In either case, you have to sit there and wait while your application appears to be doing nothing. So this locking technique often results in new blocking problems. It also favors the user who requests the record first and takes a long time to make changes to the record, rather than favoring the user who makes changes quickly.

Locking techniques also require that a connection to the DBMS be retained for the duration of the lock, which is not easily done by Web applications that use the stateless HTTP protocol.

Read Before Write

A technique that does work for Web applications is sometimes called read before write. It works like this:

When a record is retrieved that might later be updated, the contents of all the values in the record are saved as user state information somewhere. This is the "before" image of the record. When the user makes changes to the data, they are saved to a "new" image of the record in memory. Before the new version of the record is written to the database, the record is retrieved again into a third, "current," image of the record. The values of each field in the before image are compared with the values of the fields in the current image. If anything is different, the application knows that another user (or process) must have made changes to the record. It then cancels the update process, and displays a message to the user stating that the record has been updated after he or she first retrieved it, and that he or she should start the update task over again from the beginning.

I like this technique because it doesn't lead to blocking problems, and it favors the user who makes changes most quickly, instead of favoring the user who retrieves the record first—as the locking technique does. Read before write also has the benefit of being completely DBMS-independent, it even works for non-DBMS data stores, and it doesn't require additional fields to be added to the database. However, if the number of fields in the record is large, it will result in a lot of extra coding to compare the before image with the current image.

Timestamping

The technique I like to use to prevent concurrency problems, and the one I implement in this article, is called timestamping, in which each record in the database contains a "last modified date/time" field. This value is then updated whenever a new record is inserted and when an existing record is updated. Timestamping then works much like the read before write technique, except that instead of saving the entire record as a before image, only its last modified date/time field needs to be saved as user state information. Likewise, just before changes to a record are saved to the database, only the timestamp field is retrieved and compared with the timestamp that was saved when the record was first retrieved. At this point, timestamping works just like read before write. If the timestamp values are different, the application knows that another user has made changes to the record, so it cancels the update process and an error message is displayed to the user.

I like timestamping as a general solution because

It is DBMS-independent.

It works even with non-DBMS data such as random access files.

It doesn't require the application to retain a connection with the database, so it works in stateless environments such as HTTP.

It is simpler to code than read before write

1.3)Answer:

There are a number of problems that can be caused by database locking. They can generally be broken down into 4 categories: Lock Contention, Long Term Blocking, Database Deadlocks, and System Deadlocks.

approches for solving:

ock contention issues can be frustrating problems to investigate and debug. But anytime a database system is involved, the potential exists for locking to cause concurrency problems. Before blaming the database system (as is the usual response), try to answer the following questions to help identify the cause of the locking problems:

Anyone who has written applications to access database data probably has had to deal with concurrency problems at some point in their career. When one application program tries to read data that’s in the process of being changed by another, the DBMS must control access until the modification is complete to ensure data integrity. Typically, DBMS products use a locking mechanism to control access and modifications while ensuring data integrity.

When one task is updating data on a page (or block), another task can’t access data (read or update) on that same page (or block) until the data modification is complete and committed. When multiple users can access and update the same data at the same time, a locking mechanism is required. This mechanism must be capable of differentiating between stable data and uncertain data. Stable data has been successfully committed and isn’t involved in an update in a current unit of work. Uncertain data is currently involved in an operation that could modify its contents.

Most modern DBMS products allow you to control the level of locking (table, page/block, row), as well as to adjust other locking criteria (for example, locks per users, time to wait for locks, etc.). But I do not want to get into all of the nuances and implementation details of database locking. This is a simplified discussion of locking. It is not the intent of this discussion to explain all the nuances of database locking. And, indeed, each specific DBMS implements locking techniques a little bit differently. Instead, we will take a look at the types of issues and some techniques for remediating locking problems.

Lock timeouts are one of the most perplexing issues encountered by database professionals. The longer a lock is held, the greater the potential impact to other applications. When an application requests a lock that’s already held by another process, and the lock can’t be shared, that application is suspended. A suspended process temporarily stops running until the lock can be acquired. When an application has been suspended for a pre-determined period of time, it will be terminated. When a process is terminated because it exceeds this period of time, it’s said to timeout. In other words, a timeout is caused by the unavailability of a given resource.

To minimize lock timeouts, be sure to design your application programs with locking in mind from the start. Limit the number of rows accessed by coding predicates to filter unwanted rows. Doing so reduces the number of locks on pages containing rows that are accessed but not required, thereby reducing timeouts and deadlocks. Also, you should design update programs so the update is issued as close to the COMMIT point as possible. Doing so reduces the time that locks are held during a unit of work, which also reduces timeouts (and deadlocks).

Deadlocks also cause concurrency problems. A deadlock occurs when two separate processes compete for resources held by one another. For example, a deadlock transpires when PGMA has a lock on PAGE1 and wants to lock PAGE2 but PGMB (at the same time) has a lock on PAGE2 and wants a lock on PAGE1. One of the programs must be terminated to allow processing to continue. One technique to minimize deadlocks is to code your programs so that tables are accessed in the same order. By designing all application programs to access tables in the same order, you reduce the likelihood of deadlocks.

Earlier, I mentioned the COMMIT statement. It is important to design all your programs with a COMMIT strategy. A COMMIT externalizes the modifications that occurred in the program since the beginning of the program or the last COMMIT. A COMMIT ensures that all modifications have been physically applied to the database, thereby ensuring data integrity and recoverability. Failing to code COMMITs in a data modification program can cause lock timeouts for other concurrent tasks.

You can also control the isolation level, or serializability,of the data requests in your programs.

Programs using the repeatable read locking strategy hold their locks until a COMMIT is issued. If no COMMITs are issued during the program, locks aren’t released until the program completes, thereby negatively affecting concurrency. This can cause lock timeouts and lock escalation.

There are some techniques available to DBAs to minimize lock timeouts. When an object is being accessed concurrently by multiple programs or users, consider increasing free space, causing fewer rows to be stored on a single page, at least until data is added. The fewer rows per page, the less intrusive page locking will be because fewer rows will be impacted by a page lock.

Locking is a complex issue and can be at the root of many performance problems. But if you follow the guidance offered here you can reduce the frequency of locking issues in your shop

Related Questions

Navigate

• Browse (All)
• Browse P
• Subjects

Integrity-first tutoring: explanations and feedback only — we do not complete graded work. Learn more.