Results 1 to 4 of 4
  1. #1
    Join Date
    Feb 2002
    Posts
    2

    Unanswered: Lock issue during insert



    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


    phil

  2. #2
    Join Date
    Oct 2001
    Location
    England
    Posts
    426
    do 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?

  3. #3
    Join Date
    Feb 2002
    Posts
    2
    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

  4. #4
    Join Date
    Oct 2001
    Location
    England
    Posts
    426
    >> 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.

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •