 |
 | Home | Contact Us | FAQ | Search & Site Map | Link to Us |
 | Sign In | Join | Other 45 Sites in Network |
.NET Forum / .NET Framework / Distributed Applications / January 2007Transactions and Locking
I have some requirement for an automated payment system. The system has four machines setup as follows: 1- Two machines have a clustered database. 2- Two machines have a .net business logic component that will handle payment transactions with certain external component (Payment Gateway) My query is regarding transaction handling inside the business logic component. Because this component is running on two machines and these two instances of the component are accessing the same transactions table. I need these two components to work together without blocking. So transaction isolation level "Serialized" will not work with this model. I am not clear about how to lock the specific rows that are being handled by certain instance. If I set a flag (column) to lock the row, then that instance may fail before resetting the flag then this row will never be handled. In such a scenario I would expect the other instance to take over and handle whatever was locked previously by the other instance. Is there any ideas regarding arrangement between such components which access the same table? Thanks a lot for the help, Rami AlHasan Rami What do the queries do? Do the transactions UPDATE/INSEERT/DELETE operation? How big are the tables? Do you have indexes defined on the tables? >I have some requirement for an automated payment system. The system has > four machines setup as follows: [quoted text clipped - 21 lines] > Thanks a lot for the help, > Rami AlHasan Thanks Uri, Yes transactions do all opertaions like UPDATE/INSEERT/DELETE. Tables will be growing fast becuase these are payment transactions. Indexes are defined on the columns used too much in WHERE clauses. > Rami > What do the queries do? Do the transactions UPDATE/INSEERT/DELETE operation? [quoted text clipped - 25 lines] > > Thanks a lot for the help, > > Rami AlHasan Rami You have to wrap the transactions with BEGIN TRAN ...COMMIT TRAN make sure that if you get a value an later on update it , use lockin hints as the below example DECLARE @ord INT BEGIN TRAN SELECT @ord=MAX(OrderId) FROM Order WITH (UPDLOCK,HOLDLOCK) UPDATE Table SET orderid =@ord WHERE......... COMMIT TRAN > Thanks Uri, > [quoted text clipped - 32 lines] >> > Thanks a lot for the help, >> > Rami AlHasan Thanks again, This seems to be a very interesting idea... But I want to clarify, this means that one instance of the component will execute this select statement and take the lock. Then the other instance may execute the same statement again but get a failure because the rows are already locked. So the second instance will be blocked from handling transactions. Is this correct? In my case I want each instance to select 100 rows for example and process them completely before releasing them. But at the same time, I want the other instance of the component to lock another 100 rows and work on them exclusively. Any help on this? and thanks a lot for your valuable answer. Rami > Rami > You have to wrap the transactions with BEGIN TRAN ...COMMIT TRAN make [quoted text clipped - 44 lines] > >> > Thanks a lot for the help, > >> > Rami AlHasan Rami > This seems to be a very interesting idea... But I want to clarify, this > means that one instance of the component will execute this select > statement and take the lock. Then the other instance may execute the > same statement again but get a failure because the rows are already > locked. So the second instance will be blocked from handling > transactions. Is this correct? It does not block readers , it does block writers. > In my case I want each instance to select 100 rows for example and > process them completely before releasing them. But at the same time, I > want the other instance of the component to lock another 100 rows and > work on them exclusively. Read about setting transaction isolation level in the BOL > Thanks again, > [quoted text clipped - 69 lines] >> >> > Thanks a lot for the help, >> >> > Rami AlHasan One last question: I updated your sample query in the following form: DECLARE @ord INT SET TRANSACTION ISOLATION LEVEL READ COMMITTED BEGIN TRAN SELECT @ord=MAX(ORDER_ID) FROM Order WITH (UPDLOCK, READPAST) UPDATE Order SET ORDER_STATUS= something WHERE (ORDER_ID = @ord) COMMIT TRAN The basic change here is that I removed the HOLDLOCK and put the READPAST hint instead. I think the READPAST hint, will help in filtering out those locked transactions. so each instance will see unlocked transactiosns. But I removed HOLDLOCK because there was an error generated if I included it with READPAST. For me this seems to be working, is there any problem with this or any hidden implications? Thanks, Rami > Rami > > This seems to be a very interesting idea... But I want to clarify, this [quoted text clipped - 86 lines] > >> >> > Thanks a lot for the help, > >> >> > Rami AlHasan Rami Why do you need READPAST hint? In very busy enviroment you can get DEADLOCK. BOL says READPAST Skip locked rows. This option causes a transaction to skip rows locked by other transactions that would ordinarily appear in the result set, rather than block the transaction waiting for the other transactions to release their locks on these rows. The READPAST lock hint applies only to transactions operating at READ COMMITTED isolation and will read only past row-level locks. Applies only to the SELECT statement. > One last question: > [quoted text clipped - 119 lines] >> >> >> > Thanks a lot for the help, >> >> >> > Rami AlHasan I need it because I need each instance of my component to handle different set of transactions, So if the first instance selected 50 transactions to handle, then it will lock them.using the SELECT WITH(UPDLOCK, READPAST). But the second instance should not get the same 50, so I used the READPAST to filter out those who already been locked. Does this make since? But I didn't understand why deadlocks may happen? Regards, Rami > Rami > Why do you need READPAST hint? In very busy enviroment you can get DEADLOCK. [quoted text clipped - 130 lines] > >> >> >> > Thanks a lot for the help, > >> >> >> > Rami AlHasan > locked. Does this make since? If it depends on your business requieremnts, it's OK > But I didn't understand why deadlocks may happen? Open more than three connection and run this script , well , in than case you are going to get Primary Key Violation --create table people (id int not null primary key, name char(1)) declare @id int set @id=100 begin tran if not exists (select * from people WITH ( updlock,readpast ) where id=@id ) begin waitfor delay '00:00:30' insert into people (id, name) values (@id ,'h') end commit tran >I need it because I need each instance of my component to handle > different set of transactions, So if the first instance selected 50 [quoted text clipped - 160 lines] >> >> >> >> > Thanks a lot for the help, >> >> >> >> > Rami AlHasan Rami: I think you have a conceptual error in your design that you need to examine. You said that you want the components to work together without blocking, yet you say that you want to lock a row. When you "lock" a row, you essentially are blocking another process from operating on that row (it depends on the type of lock). -D > I have some requirement for an automated payment system. The system has > four machines setup as follows: [quoted text clipped - 21 lines] > Thanks a lot for the help, > Rami AlHasan Thanks dmarkle, Perhaps I didn't describe it very well. I meant that I need the two instances of the components to handle part of the payment transactions(rows), and the other instance handles the other part. This is what I meant by no one should block the other. > Rami: > [quoted text clipped - 31 lines] > > Thanks a lot for the help, > > Rami AlHasan > I have some requirement for an automated payment system. The system has > four machines setup as follows: [quoted text clipped - 21 lines] > Thanks a lot for the help, > Rami AlHasan Sounds like a good scenario to use a service broker. ----------------------- Alex Kuznetsov http://sqlserver-tips.blogspot.com/ http://sqlserver-puzzles.blogspot.com/ Free MagazinesGet these publications absolutely FREE for up to 12 months. There are no hidden fees and no obligation. Simply choose a title, complete the application form and submit it. Read more ...
|
Reply to this Message
|
 Sign In
 Join
 My Latest Posts My Monitored Threads My Blog My Photo Gallery My Profile My Homepage Start New Thread Enable EMail Alerts Rate this Thread
|
©2008 Advenet LLC Privacy Policy - Terms of Use
This website includes both content owned or controlled by Advenet as well as content owned or controlled by third parties.