Please start any new threads on our new
site at https://forums.sqlteam.com. We've got lots of great SQL Server
experts to answer whatever question you can come up with.
| Author |
Topic |
|
isurues
Starting Member
1 Post |
 Posted - 2007-09-12 : 02:22:47
|
| Transaction and Concurrency Control SimulationsIn your bank database, create two accounts, with account numbers “1234567899�, “1234567898� and place Rs. 100 in each of them. You may choose the accounts to be of any type belonging to a single customer.Now, write a script to transfer funds from the accounts. The accounts numbers and amount to transfer must be declared as variables @soucreAcc, @destAcc and @fundsToTransfer respectively.In your fund transfer script, ensure that the destination account is credited prior to deducting from the source account.You should use appropriate syntax to ensure proper transaction mechanisms for the fund transfer (i.e. BEGIN TRANSACTION/COMMIT TRANSACTION).Save your script as “Lab7.sql�.Important: It is important to set the variable XACT_ABORT (i.e. SET XACT_ABORT ON) prior to executing transactions in order to simulate “Atomicity� property. Also, ensure that the constraint in balance >= 0 is present in the account table. (Note: You can add constraints using ALTER TABLE).• Simulation 1: AtomicityInput: @sourceAcc = 1234567899 @destAcc = 1234567898 @ fundsToTransfer = 150.00Expected Behavior: The system should abort the transaction (constraint – balance > 0 - is violated). The account balances are not changed. Verify this behavior.Briefly explain this simulation result focusing on the reason for its behavior.• Simulation 2: Concurrency ControlOpen two database connections (say A and B). Load the Lab7.sql script to both applications. Use the inputs of simulation 1 for both scripts. In Database Connection A:Execute only the part of the script till the first update statement In Database Connection B:Execute only the part of the script till the first update statement.Expected Behavior: The script in B has not completed execution. The transaction is waiting for the release of locks from A’s script.Then complete the rest of script in A. Expected Behavior: The update in B completesThen execute a “SELECT * FROM Account� in A.Expected Behavior: The query executes and waits until B releases the locksNow execute the rest of B’s transaction.Expected Behavior: A’s select statement completes and results are output. |
|
|
SwePeso
Patron Saint of Lost Yaks
30421 Posts |
Posted - 2007-09-12 : 02:31:51
|
Is this homework? If so, you should try for yourself.If you are stuck, please post what you have come up with this far.
E 12°55'05.25"N 56°04'39.16" |
 |
|
|
Kristen
Test
22859 Posts |
Posted - 2007-09-12 : 02:32:50
|
| We don't do your homework for you. |
|
|
|
jen
Master Smack Fu Yak Hacker
4110 Posts |
Posted - 2007-09-12 : 06:02:48
|
but we do other "professional's" work  --------------------keeping it simple... |
|
|
|
|
|
|
Need help