Read Uncommitted, locks, and transactions

SQLServerCentral is supported by Red Gate Software Ltd.

Popular Topics

Home

Members

Calendar

Who's On

Home » Article Discussions » Article Discussions by Author » Discuss content posted by Craig Farrell » Read Uncommitted, locks, and transactions

54 posts, Page 2 of 6

««1 2 3 4 5 »»»

Read Uncommitted, locks, and transactions

Rate Topic

Display Mode

Topic Options

Author

Message

paul.knibbs

Posted Monday, February 13, 2012 3:08 AM

Ten Centuries

Group: General Forum Members
Last Login: Yesterday @ 3:43 AM
Points: 1,257, Visits: 4,255

Managed to get it right by a process of elimination--the other three answers looked wrong (especially the one about TRUNCATE not being logged!), so got the right one by default!

Post #1251065

Hugo Kornelis

Posted Monday, February 13, 2012 3:26 AM

SSCertifiable

Group: General Forum Members
Last Login: Today @ 9:48 AM
Points: 5,235, Visits: 7,037

cengland0 (2/13/2012)

This was a good question. I like how it was written in the terms of a real problem to solve.

What other ways can you change the entire contents of a table and still have the reports available without dirty data?

I've thought of creating a view that points to the table. Then, you can populate another table with the new information and then update the view to point to the new table. Once all the queries are done running on the old dataset table, the old table can then be deleted. I've never tried this so I'm curious what the experts on this forum have to say about this. Does the view get locked if a query is running it? Any negative sides to my proposed solution?

I didn't check it, but I fully expect an ALTER VIEW statement to require an exclusive schema lock. Anything less would be extremely worrying. (And if anyone wants to test, it's fairly easy - just start a transaction in a window, then run ALTER VIEW, and don't commit or rollback yet; from another window, you can now run sp_lock to see the locks being held by the uncommitted transaction.

For the situation depicted by Craig, the best solution would probably be to use one of the SNAPSHOT isolation levels.

Hugo Kornelis, SQL Server MVP
Visit my SQL Server blog: http://sqlblog.com/blogs/hugo_kornelis

Post #1251072

rfr.ferrari

Posted Monday, February 13, 2012 4:20 AM

Ten Centuries

Group: General Forum Members
Last Login: Thursday, May 02, 2013 10:51 AM
Points: 1,219, Visits: 13,507

Great question!!!!

Thanks Craig!!!!!

rfr.ferrari
DBA - SQL Server 2008
MCITP | MCTS

remember is live or suffer twice!

Post #1251099

Stewart "Arturius" Campbell

Posted Monday, February 13, 2012 4:37 AM

Hall of Fame

Group: General Forum Members
Last Login: Yesterday @ 3:48 AM
Points: 3,125, Visits: 4,311

Thanks for the question, Craig.

____________________________________________
Space, the final frontier? not any more...
All limits henceforth are self-imposed.
“libera tute vulgaris ex”

Post #1251108

paul s-306273

Posted Monday, February 13, 2012 5:02 AM

Ten Centuries

Group: General Forum Members
Last Login: Friday, May 17, 2013 4:24 AM
Points: 1,158, Visits: 642

Craig - interesting question.

Thanks.

Post #1251117

Iulian -207023

Posted Monday, February 13, 2012 5:04 AM

SSC Eights!

Group: General Forum Members
Last Login: Saturday, March 16, 2013 9:53 AM
Points: 847, Visits: 768

Nice one, thank you

Iulian

Post #1251119

Evil Kraig F

Posted Monday, February 13, 2012 5:56 AM

SSCertifiable

Group: General Forum Members
Last Login: Today @ 2:04 PM
Points: 5,662, Visits: 6,103

cengland0 (2/13/2012)

I've thought of creating a view that points to the table. Then, you can populate another table with the new information and then update the view to point to the new table. Once all the queries are done running on the old dataset table, the old table can then be deleted. I've never tried this so I'm curious what the experts on this forum have to say about this. Does the view get locked if a query is running it? Any negative sides to my proposed solution?

In this case, the view and the underlying tables will end up with a Sch-S lock. This will negate any chance you have of either altering the view or performing a table rename while any component is using it.

To completely avoid dirty data and locking issues in a case like this, you'll have to do the equivalent of a drainstop, which you can't do in SQL Server directly. This is one of the few cases where I allow for limited Dynamic SQL, because you'll have to actually alter the call to the database, and leave a hanging transaction to 'deactivate' the older version once all the locks have cleared.

Hugo is correct, the best solution in cases like this is Snapshot isolation. It's just not always feasible, particularly if you're limited in TempDB. This is one of those 'events' that bit me in the arse once or twice along the way and when I wrote up last week's truncation question this seemed like a good one as well, and an easy way to present it.

However, to go back to the root, in most scenarios I come upon this I look into an alternate solution entirely. Flushing a table once an hour is usually because you're reloading off an external feed. Say, current stock ticker prices. There are alternate ways to store this data without stepping on yourself. On of my preferred methods is to use a version key on the new data while allowing the old to continue to exist, and a settings table to tell all views and queries which data is most current.

So, you'll import new data into the same structure, assign it a key, and when that's complete you'll update the record in the settings table that indicates to all queries (via WHERE or ON clause) which set of data to use. At the final part of the import you'll clear any data not in the last two versions (or store it, or whatever), so you don't end up with a monster archive table in your active areas.

However, you don't always have time to completely rebuild an existing import process when you're looking for 'quick hits' to help optimize the system. Swapping a DELETE FROM with no WHERE clause to a TRUNCATE TABLE clause seems an easy and effective way to reduce a good chunk of logfile load. I find it's good to know the pitfalls of easy fixes. Wink

- Craig Farrell

Never stop learning, even if it hurts. Ego bruises are practically mandatory as you learn unless you've never risked enough to make a mistake.

For better assistance in answering your questions | Forum Netiquette
For index/tuning help, follow these directions. |Tally Tables
Twitter: @AnyWayDBA

Post #1251139

SanDroid

Posted Monday, February 13, 2012 6:06 AM

Ten Centuries

Group: General Forum Members
Last Login: Thursday, January 31, 2013 8:01 AM
Points: 1,232, Visits: 1,046

I like the question.

I don't like the confilicting documentation.

TRUNCATE TABLE always locks the table and page but not each row.

http://msdn.microsoft.com/en-us/library/ms177570.aspx

Same article also mentions that this is a DDL command, so I guess the Schema M lock is infered like most ddl commands.

Post #1251143

Paul White

Posted Monday, February 13, 2012 6:28 AM

SSChampion

Group: General Forum Members
Last Login: Wednesday, May 08, 2013 10:33 AM
Points: 10,989, Visits: 10,529

Hugo Kornelis (2/13/2012)

I didn't check it, but I fully expect an ALTER VIEW statement to require an exclusive schema lock.

This is true, but it's not really a problem - the ALTER VIEW statement will eventually get the lock it needs, complete very quickly, and then release that lock. If you don't mind how long the ALTER VIEW blocks for before succeeding, this can work well (though a deadlock might occur).

For the situation depicted by Craig, the best solution would probably be to use one of the SNAPSHOT isolation levels.

RCSI doesn't help here since it provides versioning at the statement level. An RCSI query that starts after the modification starts will block waiting to acquire Sch-S. Full snapshot isolation doesn't work either: it will also block waiting to acquire Sch-S, and if the DDL change succeeds, the query will then fail with error 3961: "Snapshot isolation transaction failed in database {name} because the object accessed by the statement has been modified by a DDL statement in another concurrent transaction since the start of this transaction. It is disallowed because the metadata is not versioned. A concurrent update to metadata can lead to inconsistency if mixed with snapshot isolation."

Paul White
SQL Server MVP
SQLblog.com
@SQL_Kiwi

Post #1251152

Paul White

Posted Monday, February 13, 2012 6:33 AM

SSChampion

Group: General Forum Members
Last Login: Wednesday, May 08, 2013 10:33 AM
Points: 10,989, Visits: 10,529

cengland0 (2/13/2012)

What other ways can you change the entire contents of a table and still have the reports available without dirty data?

Aside from the VIEW suggestion, similar alternatives include using a SYNONYM, using ALTER TABLE SWITCH (which does not require the table to be explicitly partitioned), a local partitioned view (where only one of the underlying tables is TRUNCATEd) or sp_rename. As Craig mentioned, yet another alternative is to use explicit keys to perform your own 'versioning'.

Paul White
SQL Server MVP
SQLblog.com
@SQL_Kiwi

Post #1251158

« Prev Topic | Next Topic »

54 posts, Page 2 of 6

««1 2 3 4 5 »»»

Permissions