Deadlock problem when using SQL server

I have a deadlock problem when using SQL server.
It seems that the deadlock occurs when several threads udpate and then query
different rows of the same table. I was able to reduce it to a simple
example:
A table containing named counters:
The table has 2 columns:
name varchar( 100 )
value int( 4 )
The table has a clustered primary key on the column 'name'.
To get a unique counter value the following SQL statement (in a transaction)
are executed:
UPDATE counters SET value = value + 1 WHERE name = ?;
SELECT value FROM counters WHERE name = ?;
The counters table contains about 100 rows.
The transaction isolation level is set to read committed.
When more than one thread at a time executes the SQL statements on different
rows, deadlocks occur.
The following observations were made during tests:
- When the threads all access the same row, everything works fine.
- The deadlocks do not occur when adding a 'with ( nolock )' hint to the
select.
My question now is: Is there a way to avoid the deadlocks without using hint
s?
I do not want to use hints, because we have an EJB application, in which we
get deadlocks in several similar cases, some of them beeing CMP Entity Beans
.
It would be possible to add hints to some statements, but I cannot control
the SQL statements generated for CMP Entity Beans.
Most of our customers are using Oracle as a database, and with Oracle no
deadlocks are occurring. Adding hints to SQL statements makes them database
dependant and I would like to avoid this.
Thanks in advance for any help.Hi
http://www.sql-server-performance.com/deadlocks.asp
"giespte" <giespte@.discussions.microsoft.com> wrote in message
news:8F3D976F-A89A-46F3-8D1A-873948405241@.microsoft.com...
> I have a deadlock problem when using SQL server.
> It seems that the deadlock occurs when several threads udpate and then
query
> different rows of the same table. I was able to reduce it to a simple
> example:
> A table containing named counters:
> The table has 2 columns:
> name varchar( 100 )
> value int( 4 )
> The table has a clustered primary key on the column 'name'.
> To get a unique counter value the following SQL statement (in a
transaction)
> are executed:
> UPDATE counters SET value = value + 1 WHERE name = ?;
> SELECT value FROM counters WHERE name = ?;
> The counters table contains about 100 rows.
> The transaction isolation level is set to read committed.
> When more than one thread at a time executes the SQL statements on
different
> rows, deadlocks occur.
> The following observations were made during tests:
> - When the threads all access the same row, everything works fine.
> - The deadlocks do not occur when adding a 'with ( nolock )' hint to the
> select.
> My question now is: Is there a way to avoid the deadlocks without using
hints?
> I do not want to use hints, because we have an EJB application, in which
we
> get deadlocks in several similar cases, some of them beeing CMP Entity
Beans.
> It would be possible to add hints to some statements, but I cannot
control
> the SQL statements generated for CMP Entity Beans.
> Most of our customers are using Oracle as a database, and with Oracle no
> deadlocks are occurring. Adding hints to SQL statements makes them
database
> dependant and I would like to avoid this.
> Thanks in advance for any help.|||"giespte" <giespte@.discussions.microsoft.com> wrote in message
news:8F3D976F-A89A-46F3-8D1A-873948405241@.microsoft.com...
>I have a deadlock problem when using SQL server.
> It seems that the deadlock occurs when several threads udpate and then
> query
> different rows of the same table. I was able to reduce it to a simple
> example:
> A table containing named counters:
> The table has 2 columns:
> name varchar( 100 )
> value int( 4 )
> The table has a clustered primary key on the column 'name'.
> To get a unique counter value the following SQL statement (in a
> transaction)
> are executed:
> UPDATE counters SET value = value + 1 WHERE name = ?;
> SELECT value FROM counters WHERE name = ?;
>
. . .
: Is there a way to avoid the deadlocks without using hints?
> I do not want to use hints, because we have an EJB application, in which
> we
> get deadlocks in several similar cases, some of them beeing CMP Entity
> Beans.
> It would be possible to add hints to some statements, but I cannot control
> the SQL statements generated for CMP Entity Beans.
> Most of our customers are using Oracle as a database, and with Oracle no
> deadlocks are occurring. Adding hints to SQL statements makes them
> database
> dependant and I would like to avoid this.
>
Are these two statements being run in a transaction?
If, in Oracle, you are running
BEGIN
UPDATE counters SET value = value + 1 WHERE name = ?;
SELECT value FROM counters WHERE name = ?;
END;
The equivilent in Sql Server is not|||"giespte" <giespte@.discussions.microsoft.com> wrote in message
news:8F3D976F-A89A-46F3-8D1A-873948405241@.microsoft.com...
>I have a deadlock problem when using SQL server.
> It seems that the deadlock occurs when several threads udpate and then
> query
> different rows of the same table. I was able to reduce it to a simple
> example:
> A table containing named counters:
> The table has 2 columns:
> name varchar( 100 )
> value int( 4 )
> The table has a clustered primary key on the column 'name'.
> To get a unique counter value the following SQL statement (in a
> transaction)
> are executed:
> UPDATE counters SET value = value + 1 WHERE name = ?;
> SELECT value FROM counters WHERE name = ?;
>
Are you wrapping these statements in an explicit transaction?
What is the index structure for the table?
Oracle and Sql Server are just different. In Oracle the statement
SELECT value FROM counters WHERE name = ?;
Generates no locks whatsoever, so I'm not sure you're going to get this to
work without some customization for the different databases.
David|||giespte wrote:
> I have a deadlock problem when using SQL server.
> It seems that the deadlock occurs when several threads udpate and
> then query different rows of the same table. I was able to reduce it
> to a simple example:
> A table containing named counters:
> The table has 2 columns:
> name varchar( 100 )
> value int( 4 )
> The table has a clustered primary key on the column 'name'.
> To get a unique counter value the following SQL statement (in a
> transaction) are executed:
> UPDATE counters SET value = value + 1 WHERE name = ?;
> SELECT value FROM counters WHERE name = ?;
> The counters table contains about 100 rows.
> The transaction isolation level is set to read committed.
> When more than one thread at a time executes the SQL statements on
> different rows, deadlocks occur.
> The following observations were made during tests:
> - When the threads all access the same row, everything works fine.
> - The deadlocks do not occur when adding a 'with ( nolock )' hint to
> the select.
> My question now is: Is there a way to avoid the deadlocks without
> using hints?
> I do not want to use hints, because we have an EJB application, in
> which we get deadlocks in several similar cases, some of them beeing
> CMP Entity Beans. It would be possible to add hints to some
> statements, but I cannot control the SQL statements generated for CMP
> Entity Beans.
> Most of our customers are using Oracle as a database, and with Oracle
> no deadlocks are occurring. Adding hints to SQL statements makes them
> database dependant and I would like to avoid this.
> Thanks in advance for any help.
Wrap them in a transaction.
David Gugick
Imceda Software
www.imceda.com|||"David Browne" wrote:

> "giespte" <giespte@.discussions.microsoft.com> wrote in message
> news:8F3D976F-A89A-46F3-8D1A-873948405241@.microsoft.com...
> Are you wrapping these statements in an explicit transaction?
Yes.
> What is the index structure for the table?
The table has a clustered primary key on 'name', created with no additional
parameter. I suppose SQL Server uses a BTree as Default.
I remember that years ago, i had a performance problem with OpenIngres. A
hash Index improved the situation then. Is it possible to use hash indexes
with SQL Server ? Would they help in this situation ?
The Profiler logs a udpate key lock acquired and released for every row when
executing the update and a shared key lock acquired and released for every
row for the select. This looks like an index page scan to me.
Other locks ( Intended Update and Intended Share ) were set at table and
page level. The profiler skipped some log messages, so i have not been able
to analyze it in full detail, but it was lots of data to go through.
> Oracle and Sql Server are just different. In Oracle the statement
> SELECT value FROM counters WHERE name = ?;
> Generates no locks whatsoever, so I'm not sure you're going to get this to
> work without some customization for the different databases.
>
I know. That′s why i tried the nolock hint.
The thing that really surprised me was, that the deadlock occurred even
though the threads work on a separate data rows. There has to be some lockin
g
at page or table level.
Thanks for the prompt reply.
Peter

> David
>
>