Inserts resulting in Exclusive Key Lock

I'm relatively new to SQL and I've come across something that doesn't seem quite right. When an insert becomes part of an transaction I notice an exclusive KEY lock in Enterprise Manager. The table in question was using a Clustered index but I changed that, dropped the table and brought it back in but I still get the lock which keeps all others out of the table. Is this the expected behavior or is there something I am missing? Could the size of the tabe affect things? This is a very small table currently. I'm using MSSQL 7 sp3.

Thanks,
JohnWell put the clustered index back...

It's locking the row and/or page..

Did 7 have row? And was it a 2k or 8k page?

I can't remeber any more,

BUT, you need to keep your transactions brief...like lightning breif...

What's the code do?

Is this a sproc?|||Yes, 7 has row locking, not sure about the page size. I am converting and old Btrieve app and trying to keep modifications to the code to a minimum. This is acutally happening with only one user in. Within a tranasction its reading a table, inserting a row, then cycyling again - multiple times. After the first insert its not able to read the table again. Before I went in and checked if the code can be changed I wanted to make sure there isn't something else I could do - aside from changing the isolation level.

Thanks

Originally posted by Brett Kaiser
Well put the clustered index back...

It's locking the row and/or page..

Did 7 have row? And was it a 2k or 8k page?

I can't remeber any more,

BUT, you need to keep your transactions brief...like lightning breif...

What's the code do?

Is this a sproc?|||You mean looping?

You're probably blocking yourself...

Are you doing INSERT INTO the table you're reading from?

Why not write to a temp table, then if all the executions are fine, insert the temp to the final destination table...

can you post the code?|||Yes, I am blocking myself...

Would that be a user temp table? Will give that a try. Can't really post the code as its being generated from an application called Magic.

Even with the temp, when inserting the temp to the final dest, won't that still result in excl key locks? - although in another transaction.

Is this just the way insert locks work - has behavior changed in 2000?

Originally posted by Brett Kaiser
You mean looping?

You're probably blocking yourself...

Are you doing INSERT INTO the table you're reading from?

Why not write to a temp table, then if all the executions are fine, insert the temp to the final destination table...

can you post the code?|||Magic...that's funny...

Let's make some Magic

CREATE TABLE #myTable99(Col list)

DECLARE @.x int

SELECT @.x = 0

WHILE @.x < 10
BEGIN
SELECT @.loclavariable1 = Col1, ect FROM TABLE WHERE Predicate = guarentee 1 row
INSERT INTO #myTable99(Col1, ect) SELECT @.loclavariable1, ect
SELECT @.x = @.x + 1
INSERT INTO FinalDetaTable (Collist)
SELECT Col list FROM #myTable99

Like that...or are you using a cursor?

If it is a cursor, you can probably do without it...

Hey, I know, I'll create an app and call it Miracle

Good Luck|||Oh for Pete's sake!...|||What's wrong, my advice or the miracle thing?

Some say it would be a miracle if I ever get this third party extract done...

They keep chenging their mind...

I'm on revision 12 now...purely painful...for a simple (ok, well not so simple) extract...|||Hey Brett, you rock! I just don't understand why people are trying to write a script to send a rocket to Mars while doing an insert?! ;)|||Sounds like a 4GL that generates the code...

Ever see SQ-R

Painful...

I guess you can you it and write good code, but it leads down the cursor path..

I once saw a "program" that ran 7 hours..

8 levels of nested curors...

Wrote in pure sql...

5 minutes...|||LOL

So in actuallity, it really was my advice

:D