Delete Lock

Hi,
I'm trying to clean up some old mess. We've got an sql server that keeps
reporting deadlocks. There is a loging application that keeps inserting
and deleting records from a table. The delete statement will always delete
only 1 row. The table is without primary keys and indexes. My question is
this: How will the delete statement lock the rows ? Will it apply a
rowlock, pagelock or table lock ?
Using Opera's revolutionary e-mail client: http://www.opera.com/m2/If the table does not have primary key or indexes, then sql server have to d
o
a table scan to find the row and may be it is scalating the lock. See "Lock
Escalation" in BOL. If possible, create a pk.
AMB
"kfu" wrote:

> Hi,
> I'm trying to clean up some old mess. We've got an sql server that keeps
> reporting deadlocks. There is a loging application that keeps inserting
> and deleting records from a table. The delete statement will always delete
> only 1 row. The table is without primary keys and indexes. My question is
> this: How will the delete statement lock the rows ? Will it apply a
> rowlock, pagelock or table lock ?
> --
> Using Opera's revolutionary e-mail client: http://www.opera.com/m2/
>|||Who is blocking who and under what circumstances. Even a query will acquire
shared locks, meaning that a long running query can block an
insert/update/delete for the duration of it's select or an i/u/d can block a
query for the duration of it's transaction. Read up on options for "set
transaction isolation level". Perhaps allowing queries to the log table to
read uncommitted will help resolve you issue. Also, if you are logging
things like user activity (for example logging the last time a record was
viewed by the user), then do not do this in a fact table, instead use a
seperate log table just for that purpose.
"kfu" <kfurnes@.netcom.no> wrote in message
news:op.sraov9ia0gg7rt@.kjafur2.sense.sensetech.no...
> Hi,
> I'm trying to clean up some old mess. We've got an sql server that keeps
> reporting deadlocks. There is a loging application that keeps inserting
> and deleting records from a table. The delete statement will always delete
> only 1 row. The table is without primary keys and indexes. My question is
> this: How will the delete statement lock the rows ? Will it apply a
> rowlock, pagelock or table lock ?
> --
> Using Opera's revolutionary e-mail client: http://www.opera.com/m2/