Lock issue during insert

:confused:

I opened 2 sql analizer windows to simulate 2 users:

In the first one I did this:
begin transaction;
insert into tst values (15);

In the second one I sent
begin transaction;
select * from tst where col=3;

The second statement is blocked waiting for the lock to be released.
Why? I tried update in plce of the insert and then there is no lock.

What is the problem with insert ?

regards

phildo you have an index on col?
if not then the select will do a table scan and be blocked. It will return nothing until the output buffer is full or the lock is released.

In both cases you should find a few intent exclusive locks and at least one exclusive.

If the select tries to access a resource that is locked then it will be blocked - if not it won't be.

Maybe your insert was forcing some page splits whereas the update wasn't?|||You're right, it does not happen when an index is on the table.
Why this happen only for inserts , and not updates.
What do you mean by lock return when the buffer is full.
regards

phil|||>> What do you mean by lock return when the buffer is full.
When you run a query in query analyser the output will be dumped to the result window when the output buffer is full or when the query completes. That's why if you run a large select you will see the results in batches - and why it doesn't mean that a query is stuck after the last result displayed.

It should happen for both inserts and updates - it just depends on what is being locked.