473,326 Members | 2,438 Online
Bytes | Software Development & Data Engineering Community
Post Job

Home Posts Topics Members FAQ

Join Bytes to post your question to a community of 473,326 software developers and data experts.

Transactions 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

Jan 15 '07 #1
12 2007
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" <ra*********@gmail.comwrote in message
news:11**********************@51g2000cwl.googlegro ups.com...
>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

Jan 15 '07 #2
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:
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
Jan 15 '07 #3
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:
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" <ra*********@gmail.comwrote in message
news:11**********************@51g2000cwl.googlegro ups.com...
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
Jan 16 '07 #4
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:

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:
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
Jan 16 '07 #5
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" <ra*********@gmail.comwrote in message
news:11**********************@a75g2000cwd.googlegr oups.com...
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:
>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" <ra*********@gmail.comwrote in message
news:11**********************@51g2000cwl.googlegr oups.com...
>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

Jan 16 '07 #6
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
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" <ra*********@gmail.comwrote in message
news:11**********************@a75g2000cwd.googlegr oups.com...
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:
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" <ra*********@gmail.comwrote in message
news:11**********************@51g2000cwl.googlegro ups.com...
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
Jan 16 '07 #7
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


"Rami" <ra*********@gmail.comwrote in message
news:11********************@51g2000cwl.googlegroup s.com...
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
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" <ra*********@gmail.comwrote in message
news:11**********************@a75g2000cwd.googleg roups.com...
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:
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" <ra*********@gmail.comwrote in message
news:11**********************@51g2000cwl.googlegr oups.com...
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


Jan 16 '07 #8
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
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


"Rami" <ra*********@gmail.comwrote in message
news:11********************@51g2000cwl.googlegroup s.com...
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
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" <ra*********@gmail.comwrote in message
news:11**********************@a75g2000cwd.googlegr oups.com...
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:
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" <ra*********@gmail.comwrote in message
news:11**********************@51g2000cwl.googlegro ups.com...
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
Jan 17 '07 #9
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" <ra*********@gmail.comwrote in message
news:11**********************@q2g2000cwa.googlegro ups.com...
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
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


"Rami" <ra*********@gmail.comwrote in message
news:11********************@51g2000cwl.googlegrou ps.com...
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
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" <ra*********@gmail.comwrote in message
news:11**********************@a75g2000cwd.googleg roups.com...
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:
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" <ra*********@gmail.comwrote in message
news:11**********************@51g2000cwl.googlegr oups.com...
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

Jan 17 '07 #10
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
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" <ra*********@gmail.comwrote in message
news:11**********************@q2g2000cwa.googlegro ups.com...
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
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


"Rami" <ra*********@gmail.comwrote in message
news:11********************@51g2000cwl.googlegroup s.com...
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
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" <ra*********@gmail.comwrote in message
news:11**********************@a75g2000cwd.googlegr oups.com...
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:
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" <ra*********@gmail.comwrote in message
news:11**********************@51g2000cwl.googlegro ups.com...
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

Jan 17 '07 #11
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
"Rami" <ra*********@gmail.comwrote in message
news:11*********************@38g2000cwa.googlegrou ps.com...
>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
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" <ra*********@gmail.comwrote in message
news:11**********************@q2g2000cwa.googlegr oups.com...
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
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


"Rami" <ra*********@gmail.comwrote in message
news:11********************@51g2000cwl.googlegrou ps.com...
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
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" <ra*********@gmail.comwrote in message
news:11**********************@a75g2000cwd.googleg roups.com...
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:
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" <ra*********@gmail.comwrote in message
news:11**********************@51g2000cwl.googlegr oups.com...
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


Jan 17 '07 #12

Rami wrote:
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
Sounds like a good scenario to use a service broker.

-----------------------
Alex Kuznetsov
http://sqlserver-tips.blogspot.com/
http://sqlserver-puzzles.blogspot.com/

Jan 17 '07 #13

This thread has been closed and replies have been disabled. Please start a new discussion.

Similar topics

0
by: Heikki Tuuri | last post by:
Hi! Many people have complained over years that Borland's dbExpress driver does not work with MySQL and transactions, because it disconnects from mysqld after each SQL statement. The postings...
2
by: Tim McAuley | last post by:
Hi, I have a stored procedure that is causing deadlocks when called multiple times synchronously. The odd issue is that the deadlock seems to be happening on different threads waiting for locks...
9
by: TD | last post by:
I am trying to add transactions to my code. The original code worked fine until I followed an example to setup transactions, now the code does strange things, but no error messages. Could...
3
by: Ace Calhoon | last post by:
Hello, I have a VBA/Database application which reads files, analyzes them, updates a database, and then moves them to an archive. I would like to make this an atomic transaction -- that is, if...
11
by: Mike P | last post by:
I've been using C# transactions for a while and had no problems with them. Using try catch blocks I can trap basically all possible errors and rollback all necessary data. Over the last few...
2
by: Adnan | last post by:
Hey Ppl, I'm developing an Online Auction Site using ASP.net and am experiencing a problem with Transactions in ADO.Net. When beginTrasaction() function is invoked from a specific connection,...
2
by: Antuane | last post by:
any one have any idea how transactions could be enabled in webservices. i.e., suppose i've got 2 methods - one to add a contact, & the other to set some miscellaneous details for the contact, in a...
8
by: Allan Ebdrup | last post by:
I've implemented transactions in my dotNet 2.0 project using System.Transactions and TransactionScope. --- TransactionOptions options = new TransactionOptions(); options.IsolationLevel =...
3
by: David C. Barber | last post by:
How do you lock a record in SQL Server from ASP 2? I need to read the record, allow the user to edit it, and then have them click Save and rewrite it. Obviously I don't want anyone else getting...
0
by: DolphinDB | last post by:
Tired of spending countless mintues downsampling your data? Look no further! In this article, you’ll learn how to efficiently downsample 6.48 billion high-frequency records to 61 million...
0
by: ryjfgjl | last post by:
ExcelToDatabase: batch import excel into database automatically...
0
isladogs
by: isladogs | last post by:
The next Access Europe meeting will be on Wednesday 6 Mar 2024 starting at 18:00 UK time (6PM UTC) and finishing at about 19:15 (7.15PM). In this month's session, we are pleased to welcome back...
0
by: jfyes | last post by:
As a hardware engineer, after seeing that CEIWEI recently released a new tool for Modbus RTU Over TCP/UDP filtering and monitoring, I actively went to its official website to take a look. It turned...
0
by: ArrayDB | last post by:
The error message I've encountered is; ERROR:root:Error generating model response: exception: access violation writing 0x0000000000005140, which seems to be indicative of an access violation...
1
by: PapaRatzi | last post by:
Hello, I am teaching myself MS Access forms design and Visual Basic. I've created a table to capture a list of Top 30 singles and forms to capture new entries. The final step is a form (unbound)...
1
by: Defcon1945 | last post by:
I'm trying to learn Python using Pycharm but import shutil doesn't work
0
by: af34tf | last post by:
Hi Guys, I have a domain whose name is BytesLimited.com, and I want to sell it. Does anyone know about platforms that allow me to list my domain in auction for free. Thank you
0
isladogs
by: isladogs | last post by:
The next Access Europe User Group meeting will be on Wednesday 3 Apr 2024 starting at 18:00 UK time (6PM UTC+1) and finishing by 19:30 (7.30PM). In this session, we are pleased to welcome former...

By using Bytes.com and it's services, you agree to our Privacy Policy and Terms of Use.

To disable or enable advertisements and analytics tracking please visit the manage ads & tracking page.