By Ger Cannoll - 2/14/2012
I am unsure as to what is, and if it is necessary/desirable to use TransactionKeys in Transactions.
BusinessLayer.TransactionBegin("", IsolationLevel.Serializable);
or
BusinessLayer.TransactionBegin("", "WhatIsATransactionKey",IsolationLevel.Serializable);
It looks like the TransactionKey is optional in the TransactionBegin statement, and wonder if I should use it at all. My users scenario would be that typically there could be 15-20 users accessing the same tables at the same time, and updating the same Tables, possibly updating the same Rows also at the same time
|
By Michel Levy - 2/14/2012
Hi Ger,
the first question is: do you really need this IsolationLevel? it is usefull only if you need a user to have an exclusive access to some tables (if you have developped in FoxPro, it's as a SET EXCLUSIVE ON). Be aware of the risk of locking everybody!
In most cases, a simple ReadCommitted Isolation level is sufficent to manage concurrency with hundreds or thousands of users.
Then the second question is: do you really need to set the transaction from the application client? remember that in SQL Server, all is allways in transaction, even if not explicitely declared (implicit transactions).
You may need to declare a transaction from the client side, if you want to put a set of statements on different tables in a single transaction and you want that all are committed or all are not committed (for example, an insert in a customer table, then insert in orders table, and n insert in a orderdetails table, etc.). In such case, yes, you need to begin transaction from the client.
And now, we can answer to your question: if you have a set of instructions that need to be committed in different batches, you will use named transaction inside a global transaction. this way you will be able to commit one, and rollback others.
I'm not sure you need it.
|
By Ger Cannoll - 2/14/2012
Hi Michel. Thanks for your reply.
Do I need Transactions and Isolation level. ? Scenario is as follows, and there could be 20 simultaneous users doing the same thing:
Enter a transaction which needs to update a Customer Balance with a Value of 5. Customer Balance MUST be updated immediately.
Customer processing pseudo code would be as follows:
1. Read the Current Customer Balance (say its currently = 100)
2. Do a whole pile of processing which say takes 2 seconds for the sake of argument (This bit cannot be avoided)
3. Update the Customer Balance with the Value of 5 (Customer Balance now becomes 100 + 5 = 105)
Now unless I completely LOCK down the Customer Record, User A would have read the Value as being 100, and before User A has finished updating, User B comes along with a transaction value of say 8 reads the Balance also as 100. User A now updates the Customer Balance (100 + 5=105)
User B now comes along and updates the Balance (100 , which User B got , adds 7, = 107) and updates the Balance with 107
This would be incorrect as the balanced should be 100 + 5 = 105 and then 105 + 7 = 112
Other than using a Transaction (which I understand locks the Database) and Isolation Level of Serializable(Which completely locks the row from any updating at all) , I dont know how I can accomplish my task
Is there another way ?
|
By Michel Levy - 2/15/2012
Hi Ger,
If I understand, the value of the update (phase #3) is the result of a pile of processing (phase #2) applied to the initial value acquired in phase #1.
I'm pretty sure that the Repeatable Read level would be sufficent, but not absolutely certain.
In this case, I think the best isolation level would be Read Committed Snapshot. But this level is not in all versions of SQL Server 2008 (Enterprise only, not sure). If your version doesn't support it, choose the Seralizable level.
In all cases, you don't need to name your transaction.
|
|