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?



"Rami" <ramialhasan (AT) gmail (DOT) com> wrote

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

Rami wrote:

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.



Uri Dimant wrote:

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.

dmarkle wrote:

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

"Rami" <ramialhasan (AT) gmail (DOT) com> wrote

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



Uri Dimant wrote:

Rami
It does not block readers , it does block writers.

Read about setting transaction isolation level in the BOL








"Rami" <ramialhasan (AT) gmail (DOT) com> wrote

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



Uri Dimant wrote:

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.

"Rami" <ramialhasan (AT) gmail (DOT) com> wrote