Table and Index Deadlocks

You might be confusing "noholdlock" with "nolock". If the "second
procedure" is the "select" statement, then nohold lock has no meaning unless
you are using isolation level 3. Noholdlock simply states that you don't
wish to hold the shared lock for the duration of the transaction. By
default (isolation level 2), you don't hold a shared lock for the duration
of the transaction. You only hold the lock for as long as you are reading
the page.

If you can tolerate reading uncommited data, then try using isolation level
0, or "read uncommitted" in your select statement. That will not require a
shared lock for the SELECT statement but at the risk of reading data that
may not be committed.

wrote in message news:449c583c.3e7a.1681692777@sybase.com...
> We have 2 stored procedures. One inserts into a table and
> commits after every transaction and the other one selects
> from the table. We have noholdlocks on the from clause in
> the second procedure.
> We are conducting performance tests on the table using 50
> concurrent users performing 5 inserts into the table and we
> have been having deadlocks (both table and index). How can
> we resolve the deadlocks. The locking scheme is allpages

Charles Bonsu wrote:
>
> We have 2 stored procedures. One inserts into a table and
> commits after every transaction and the other one selects
> from the table. We have noholdlocks on the from clause in
> the second procedure.
> We are conducting performance tests on the table using 50
> concurrent users performing 5 inserts into the table and we
> have been having deadlocks (both table and index). How can
> we resolve the deadlocks. The locking scheme is allpages

Have you turned on deadlock information printing in the
errorlog and examined said information?

-am © MMVI

We do not want to do a dirty read. Are there any other
options

Charles

> You might be confusing "noholdlock" with "nolock". If the
> "second procedure" is the "select" statement, then nohold
> lock has no meaning unless you are using isolation level
> 3. Noholdlock simply states that you don't wish to hold
> the shared lock for the duration of the transaction. By
> default (isolation level 2), you don't hold a shared lock
> for the duration of the transaction. You only hold the
> lock for as long as you are reading the page.
>
> If you can tolerate reading uncommited data, then try
> using isolation level 0, or "read uncommitted" in your
> select statement. That will not require a shared lock
> for the SELECT statement but at the risk of reading data
> that may not be committed.
>
> wrote in message
> > news:449c583c.3e7a.1681692777@sybase.com... We have 2
> > stored procedures. One inserts into a table and commits
> > after every transaction and the other one selects from
> > the table. We have noholdlocks on the from clause in the
> > second procedure. We are conducting performance tests on
> > the table using 50 concurrent users performing 5 inserts
> > into the table and we have been having deadlocks (both
> > table and index). How can we resolve the deadlocks. The
> locking scheme is allpages
>
>

Charles, see below:

http://infocenter.sybase.com/help/in...tsg/title.htm&

HTH,
Eugene
Charles Bonsu wrote:

>We have 2 stored procedures. One inserts into a table and
>commits after every transaction and the other one selects
>from the table. We have noholdlocks on the from clause in
>the second procedure.
>We are conducting performance tests on the table using 50
>concurrent users performing 5 inserts into the table and we
>have been having deadlocks (both table and index). How can
>we resolve the deadlocks. The locking scheme is allpages
>
>