0

I have 2 tables in my database. They are Item and Field. Item is a self referenced table to implement a tree. That means in Item table, I have ID and ParentId columns. In this way, we can store a tree in the Item table. Field is a table to store other extend properties for an item. Field table has a column named "Item_ID" to refer to item table.

OK, now we just want to add transaction to the database operation. We found some strange behavior as the title.

As Microsoft asked, we run the following sql to enable snapshot isolation of our database:

ALTER DATABASE AdventureWorks2008R2
SET READ_COMMITTED_SNAPSHOT ON;

ALTER DATABASE AdventureWorks2008R2
SET ALLOW_SNAPSHOT_ISOLATION ON;

And then we do the following test：

  1. Open 2 test programs

  2. The first program update Item 1 and be blocked before the transaction is commit.

  3. Use the second program to try to delete Item 2. Item 1 have no relation ship with Item 2. That means Item 1 do not is a parent/child of Item 2.

  4. Then we found second program is blocked by first program. If we continue the first program, then we second program will finish if it is not timeout.

I feel strange about this result. Because I think I can delete Item 2 even Item 1 is in transaction. Who knows why this happens?

Brian Tompsett - 汤莱恩
  • 5,753
  • 72
  • 57
  • 129
Coofucoo
  • 119
  • 7
  • 1
    If there really is no direct relationship between the two records, it sounds like the database server is performing table locking rather than row locking. – gorjusborg Mar 14 '12 at 13:39
  • @YuRDeD, I think you're correct. Because of the parent/child relationship (and I admit, I do not know the internals of SQL Server's locking strategies), I suspect it has to do a table-level lock. – Moo-Juice Mar 14 '12 at 13:44
  • @Pleepleus Yes, I think so too. But I feel it is not necessary to do that. And I want to know whether there is any solution. – Coofucoo Mar 15 '12 at 02:48

1 Answers1

0

It sounds like you're not getting SNAPSHOT ISOLATION at all (since clashing updates/delete should cause a failure, and not eventual completion).

You didn't literally execute ALTER DATABASE AdventureWorks2008R2... - did you?

As "AdventureWorks2008R2" needs to be replaced with the name of your database...

Ivan Hamilton
  • 3,205
  • 1
  • 23
  • 14
  • Yes, sure, I replaced the database name. I think the problem is my item table is a self reference table. – Coofucoo Aug 10 '13 at 05:31