\"Create a simple transaction log (using the format shown in Table 10.14) to rep
ID: 3746929 • Letter: #
Question
"Create a simple transaction log (using the format shown in Table 10.14) to represent the actions of the transactions in Problems 6a and 6b." (This is the question I need answered, this is so confusing to me).
_______________________________________________
Here are 6a and 6b answers:
(6a)
BEGIN TRANSACTION;
UPDATE CUSTOMER SET CUS_BALANCE = CUS_BALANCE + 118.8, CUS_DATELSTPUR = '2014-05-11' WHERE CUS_CODE = 10010;
INSERT INTO INVOICE (INV_NUMBER, CUS_CODE, INV_DATE, INV_TOTAL, INV_TERMS, INV_STATUS) VALUES (10983, 10010, '2014-05-11', 118.8, '30', 'OPEN');
INSERT INTO LINE (INV_NUMBER, LINE_NUMBER, P_CODE, LINE_UNITS, LINE_PRICE) VALUES (10983, 1, '11QER/31', 1, 110);
UPDATE PRODUCT SET P_QTYOH = P_QTYOH - 1 WHERE P_CODE = '11QER/31';
COMMIT;
_____________________________________________
(6b)
BEGIN TRANACTION;
UPDATE CUSTOMER SET CUS_BALANCE = CUS_BALANCE - 118.8, CUS_DATELSTPMT = '2014-06-03' WHERE CUS_CODE = 10010;
UPDATE INVOICE SET INV_TERMS = 'CASH', INV_STATUS = 'PAID' WHERE INV_NUMBER = 10983;
INSERT INTO PAYMENTS (PMT_ID, PMT_DATE, CUS_CODE, PMT_AMT, PMT_TYPE, PMT_DETAILS) VALUES (3428, '2014-06-03', 10010, 118.8, 'CASH','');
COMMIT;
______________________________________________
And here is table 10.14 (Please HELP!!)
TABLE 10.14 Wait/Die and Wound/Wait Concurrency Control Schemes TRANSACTION REQUESTING LOCK OWNING LOCK T1 (11548789) TRANSACTION WAIT/DIE SCHEME WOUND/WAIT SCHEME · TI waits until T2 is T1 preempts (rolls back) T2. same timestamp. completed and T1 releases T2 (19562545) completed and T2 releasesT2 is rescheduled using the its locks. T2 dies (rolls back). T2 is rescheduled using the same times tamp T2 (19562545) T1 (11548789) · T2 waits until T1 is · · its locksExplanation / Answer
I think this article is related to concurrency control of the database .
Now, let us think that , for an account, any transaction is taking place. Now, that transaction should follow 4 properties. ACID;
Atomicity - If a transaction takes place , it takes place at the full or does not take place at all. No partial completion is allowed
Consistency - Consistency is a very important property of SQL type database. For, NOSQL type it may not always hold. If before any transaction, the database was consistent, it will be consistent even after the transaction. Consistent state means , if two transaction read a account value at the same time, they both should get the same value.
Isolation : Although there are several transactions running on the database system, the database system should ensure that the end user feels that his transaction is the only one happening on the database. Every transaction should be isolated from each other, no interference is tolerated.
Duralibity : DBMS should be durable enough to recover in some extreme conditions , like power failure , system breakdown etc.
Now, The above example is about concurrent transaction problems.
Let us say Transaction T1 is holding a lock on a resource let us say customer balance.
and transaction T2 wants to also access the same Customer Balance.
Now, there are two scheme -
Wound Wait Scheme-
if T1 is older than T2 ->
Transaction log will look like -
T1 - lock held on resource Balance
T2 - waits for resource
T1 - Continue with the transaction
T1 - finishes transaction
T1 -Commits
T2 - secure the resource and put lock on it
T2 - Continue with the transaction
Wait/Die Scheme -
T1 - lock held on resource Balance
s - save timestamp for T2
T2 - dies
T2 - Reinitialize later with the same timestamp(s)
T1 - Continue with the transaction
T1 - finishes transaction
T1 -Commits
T2 - secure the resource and put lock on it
T2 - Continue with the transaction
if T2 was older than T1 :
Wound/Wait Scheme -
T1 - holds lock
T2 - Wounds T1 by preempting it
s - save timestamp of T1
T1 - initialize with same timestamp
T2 - Initialize Transaction
T1 - Waiting in the wait list for get
T2 - Commits
T1 - secure lock on resource
Wait/Die Scheme -
(as T2 is older this time, it is allowed to wait)
T1 - Holds lock
T2 - Wait to get the resouce , it does not wound T1
T1 - Continue with Transaction
T1 - Commits
T2 - Secure lock on resource
In both the schemes, the older file gets better probability to execute.
Related Questions
Navigate
Integrity-first tutoring: explanations and feedback only — we do not complete graded work. Learn more.