"Concurrency Control" Please respond to the following:
There are times when a database is accessed by many users across the enterprise.
Transaction logs can help maintain concurrency in a multiuser platform.
Describe at least two business scenarios where COMMIT and ROLLBACK would be required.
Describe how these functions would be essential for business continuity and concurrency control.
Identify how these functions would enable an organization to recover from a database corruption event.
Describe how locks could be used in order to qualify data transactions and enhance concurrency control. Suggest at least two business scenarios that would benefit from implementing locking methods.
Classmates:
Describe at least two business scenarios where COMMIT and ROLLBACK would be required. Describe how these functions would be essential for business continuity and concurrency control. Identify how these functions would enable an organization to recover from a database corruption event.
A transaction begins when data is read or written. A transaction ends with a COMMIT or ROLLBACK statement or with the end of an application process. The COMMIT statement commits the database changes that were made during the current transaction, making the changes permanent.
The ROLLBACK statement backs out, or cancels, the database changes that are made by the current transaction and restores changed data to the state before the transaction began.
If anything goes wrong with any of the grouped statements, all changes need to be aborted. The process of reversing changes is called rollback. If everything is in order with all statements within a single transaction, all changes are recorded together in the database and saved, that means it's committed.
For example, in a banking sectors, a transaction is when User A withdrew say $40 from his Saving account with the balance of $100 and added it to Checking acct, that has a balance of $100, all happening at the same time, to update both Saving and Checking accts. in database with the new transaction, provided the statement is without error, the COMMIT command must be executed, but if there is error in the statement, User A can execute ROLLBACK command to cancel the transaction.
Another User B, working on the same acct, and commit it, will have his statement updated first before User A, that started earlier because he did not commit but he rollback.
Therefore, in the event that organization database is corrupted, the use of ROLLBACK, will allow the organization to remove the corrupted data, and re-installed the database to the time the database wasn't corrupted. This will only affect those data that couldn't be committed before corruption, these is better than loosen the whole organization database.
Describe how locks could be used in order to qualify data transactions and enhance concurrency control. Suggest at least two business scenarios that would benefit from implementing locking methods.
When many people are reading the same data item at the same time, it is usually necessary to ensure that only one application at a time can change a data item. Locking is a way to do this.
Because of locking, all changes to a particular data item will be made in the correct order in a transaction. The amount of data that can be locked with the single instance or groups of instances defines the granularity of the lock. There two rights that can be granted when locking, EXCLUSIVE (x)- that's write and read, and SHARED (s)- ready only.
If two transactions are occurring at the same time, T1 and T2, say T1 has requested for write x lock, then T2 cannot be granted write x lock, but if the T2 request is read s, then it's granted because harm cannot be wrecked for reading the data, in the concurrency control.
For example, assuming a bank customer wrote two checks for transaction A and B, and both checks were presented to cashier at the same time, both cashiers would be requesting for exclusive lock at the same time then there would be conflict, the system honored both check amount, if the customer doesn't have the total check amount on the account, the bank run into over paying the customer.
Another example is when booking a single seat on a flight, the booking Agent need exclusive lock, so that a seat will not be booked for multiple travelers. In both example if one Cashier or Booking Agent requested for Exclusive lock and the other for Shared locks, both will be granted.