Ayende @ Rahien

Vitaly commented on Transactions and concurrency

Thu, 05 Apr 2007 03:29:30 GMT

Semantically, they're the same. From what I understand, the SNAPSHOT isolation leve is effective at the transaction level (i.e. the snapshot is created when the transaction starts), whereas the READ COMMITTED with the snapshot option is at a statement level (i.e. snapshot is created before the statement is executed).

Ayende Rahien commented on Transactions and concurrency

Thu, 05 Apr 2007 01:58:01 GMT

Is it the same as SNAPSHOT isolation?

Vitaly commented on Transactions and concurrency

Thu, 05 Apr 2007 01:52:42 GMT

Ayende, Have you tried to run your code with the READ_COMMITTED_SNAPSHOT db option set to ON? This will keep a row version for rows that are affected by the statement in the transaction, and wil present those versions of the row instead of doing locking. I would imagine that this setting (which is OFF by default) would create consistent reads/writes while not impacting concurrency too much.

Ayende Rahien commented on Transactions and concurrency

Thu, 05 Apr 2007 00:20:18 GMT

I have run into this issue on production, in a table that most definitely had a PK (identity). An identity PK (clustered) would basically sort the table, so the scan would tend to be linear. The code that I had used a where clause that filtered by several columns, thus probably producing a non linear table scan.

Alessandro Riolo commented on Transactions and concurrency

Wed, 04 Apr 2007 18:42:41 GMT

I just wrote a post about this issue: http://ale.riolo.co.uk/2007/04/transaction-and-concurrency-on-sql.html I found out that using a table with a primary key (I used a surrogate key through identity column) everything work as expected.

Alessandro Riolo commented on Transactions and concurrency

Wed, 28 Mar 2007 10:21:46 GMT

Incidentally, while looking for the SQL-92 standard, I found this very interesting paper from Microsoft Research: ftp://ftp.research.microsoft.com/pub/tr/tr-95-51.pdf It is the theorical foundation for the Snapshot Isolation Level, the feature introduced in SQL Server 2005. Although probably they didn't invented that, it is quite amazing to discover that the time to market from their R&D awareness to the moment in which it was released in their flagship database product was more or less 10 years ..

Ayende Rahien commented on Transactions and concurrency

Wed, 28 Mar 2007 10:12:58 GMT

I don't have an issue with that, I have an issue with geting a _partial_ view of the results of T2

Alessandro Riolo commented on Transactions and concurrency

Wed, 28 Mar 2007 10:11:10 GMT

I took the following from the definition of the Phantom phenomena in the SQL standard (actually I took it from SQL-99, but I recall it as being more or less the same on SQL-92): P3 (‘‘Phantom’’): SQL-transaction T1 reads the set of rows N that satisfy some . SQL-transaction T2 then executes SQL-statements that generate one or more rows that satisfy the used by SQL-transaction T1. If SQL-transaction T1 then repeats the initial read with the same , it obtains a different collection of rows.

Ayende Rahien commented on Transactions and concurrency

Tue, 27 Mar 2007 22:37:13 GMT

I thought that phantom reads where for records that were deleted, not partially created. What this shows is that some of the information is made available to the second transaction, but not all of it.

Ayende Rahien commented on Transactions and concurrency

Tue, 27 Mar 2007 22:35:41 GMT

They definitely have a where clause.

Alessandro Riolo commented on Transactions and concurrency

Tue, 27 Mar 2007 22:30:16 GMT

SQL Server 2005 currently support 6 different Isolation Level behaviours, and only 2 of them (Snapshot and Serializable) are preventing Phantom reads. About your code, are you sure the actual issued queries have not any WHERE clause?

Stuart Carnie commented on Transactions and concurrency

Mon, 26 Mar 2007 17:25:24 GMT

@Ayende, Whilst I'm probably stating the obvious, it is interesting that you can change the semantics of the select using table hints (HOLDLOCK), so that whilst the INSERT is using ReadCommitted, the SELECT can use the consistency of the SERIALIZABLE locking semantics. I did try this, and it never failed. Also, the smaller the size of the insert transaction, the less frequently it fails. Cheers, Stuart

Ayende Rahien commented on Transactions and concurrency

Mon, 26 Mar 2007 13:58:51 GMT

@El, Yes, they do. That doesn't mean that it is easy to figure out. Like most concurrent stuff, this is hard.

El Guapo commented on Transactions and concurrency

Mon, 26 Mar 2007 13:32:11 GMT

Does anybody actually know how the isolation levels work? Does they guy who wrote it even know?

Tomas Restrepo commented on Transactions and concurrency

Mon, 26 Mar 2007 13:09:11 GMT

@Ayende: As I understand them, phantoms apply to both added as well as deleted rows. But I'll be the first one to say I'm not sure if that's the case here!

Ayende Rahien commented on Transactions and concurrency

Mon, 26 Mar 2007 12:47:24 GMT

@Tomas, This isn't the case of phantom rows, at least not as I understand them. There isn't any deletion done at any point in time. The producer inserts 500 rows per transaction, the consumer reads the counts per id. If there was true isolation, it would always see 500 records per ID, the issue is that it manages to see a partial result, and get a number of rows that is different than 500.

Tomas Restrepo commented on Transactions and concurrency

Mon, 26 Mar 2007 12:41:55 GMT

I haven't tried this yet, but as far as I remember, neither the Read Committed or Repeatable Reads isolation levels will prevent you from reading phantoms, which might be part of the issue. However, I'm also not clear as to why the consumer is expecting to get a count of 500... since the select query groups by the ID. To be consistent, shouldn't the consumer also loop asking for the count of records for a *specific* id?

Ayende Rahien commented on Transactions and concurrency

Mon, 26 Mar 2007 07:41:50 GMT

@Jeremy, I understand what is going on, it is basically scanning the table and finding newly commited rows, but skipping some of the rows commited in the same transaction. It looks like exactly the wrong thing to do as far as I am concerned, certainly a very nasty trap. I guess that is why Oracle devs are laughing at SQL Server, and why there is the new Snapshot level

Ayende Rahien commented on Transactions and concurrency

Mon, 26 Mar 2007 07:39:40 GMT

@Stuart, That was what I was thinking, but this is plain ugly. I expected that either all the new rows or none of the new rows would be visible to the transaction. This makes me wonder what is the point of read commited in this scenario.

Jeremy Boyd commented on Transactions and concurrency

Mon, 26 Mar 2007 01:33:16 GMT

Interesting issue. Replicating your code I can yield the same results and the behavior seems clearly wrong. From subjective testing it seems to clearly be some kind of race issue around the row/page locks being acquired and released between the two as you can make it work cleanly by changing the Consumer statement to: SELECT COUNT(*) FROM t WITH (TABLOCK) GROUP BY id While it seems that technicallly the transaction is working correctly this is a bit of a trap for the unknowing about the consistency of the read results..

Stuart Carnie commented on Transactions and concurrency

Mon, 26 Mar 2007 00:34:36 GMT

Read this article : http://sqlblogcasts.com/blogs/tonyrogerson/archive/2006/11/16/1345.aspx I think this has your answer. Cheers, Stu

Ayende Rahien commented on Transactions and concurrency

Sun, 25 Mar 2007 19:25:31 GMT

@Andrew, There is no dead look. it is just that the second transaction is able to see partial results from the first transaction

Ayende Rahien commented on Transactions and concurrency

Sun, 25 Mar 2007 19:24:02 GMT

@Stuart, I mean that it fails by giving a result that is not 500. This means that it is able to see only a partial result from the second transaction.

Andrew commented on Transactions and concurrency

Sun, 25 Mar 2007 19:08:53 GMT

Deadlock probably?

Stuart Carnie commented on Transactions and concurrency

Sun, 25 Mar 2007 18:01:28 GMT

Oren, what is the actual failure? Just not sure what you mean by 'fails in a couple of seconds'. Cheers, Stuart