Click here to monitor SSC
SQLServerCentral is supported by Red Gate Software Ltd.
 
Log in  ::  Register  ::  Not logged in
 
 
 
        
Home       Members    Calendar    Who's On


Add to briefcase ««12345»»»

Read Uncommitted, locks, and transactions Expand / Collapse
Author
Message
Posted Monday, February 13, 2012 3:08 AM
SSCommitted

SSCommittedSSCommittedSSCommittedSSCommittedSSCommittedSSCommittedSSCommittedSSCommitted

Group: General Forum Members
Last Login: Friday, July 25, 2014 3:12 AM
Points: 1,610, Visits: 5,482
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
Posted Monday, February 13, 2012 3:26 AM


SSCertifiable

SSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiable

Group: General Forum Members
Last Login: Yesterday @ 3:22 PM
Points: 5,925, Visits: 8,174
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
Posted Monday, February 13, 2012 4:20 AM


Ten Centuries

Ten CenturiesTen CenturiesTen CenturiesTen CenturiesTen CenturiesTen CenturiesTen CenturiesTen Centuries

Group: General Forum Members
Last Login: Wednesday, July 23, 2014 11:04 AM
Points: 1,254, Visits: 13,552
Great question!!!!

Thanks Craig!!!!!



rfr.ferrari
DBA - SQL Server 2008
MCITP | MCTS

remember is live or suffer twice!
Post #1251099
Posted Monday, February 13, 2012 4:37 AM


Hall of Fame

Hall of FameHall of FameHall of FameHall of FameHall of FameHall of FameHall of FameHall of FameHall of Fame

Group: General Forum Members
Last Login: Today @ 3:50 AM
Points: 3,861, Visits: 5,002
Thanks for the question, Craig.

____________________________________________
Space, the final frontier? not any more...
All limits henceforth are self-imposed.
“libera tute vulgaris ex”
Post #1251108
Posted Monday, February 13, 2012 5:02 AM


Ten Centuries

Ten CenturiesTen CenturiesTen CenturiesTen CenturiesTen CenturiesTen CenturiesTen CenturiesTen Centuries

Group: General Forum Members
Last Login: Friday, July 4, 2014 9:03 AM
Points: 1,415, Visits: 796
Craig - interesting question.

Thanks.
Post #1251117
Posted Monday, February 13, 2012 5:04 AM
SSC Eights!

SSC Eights!SSC Eights!SSC Eights!SSC Eights!SSC Eights!SSC Eights!SSC Eights!SSC Eights!

Group: General Forum Members
Last Login: 2 days ago @ 4:54 AM
Points: 910, Visits: 840
Nice one, thank you

Iulian
Post #1251119
Posted Monday, February 13, 2012 5:56 AM


SSCertifiable

SSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiable

Group: General Forum Members
Last Login: Yesterday @ 7:26 PM
Points: 6,157, Visits: 7,217
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.



- 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
Posted Monday, February 13, 2012 6:06 AM
Ten Centuries

Ten CenturiesTen CenturiesTen CenturiesTen CenturiesTen CenturiesTen CenturiesTen CenturiesTen 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
Posted Monday, February 13, 2012 6:28 AM


SSChampion

SSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampion

Group: General Forum Members
Last Login: Yesterday @ 3:56 PM
Points: 11,192, Visits: 11,096
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
Posted Monday, February 13, 2012 6:33 AM


SSChampion

SSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampion

Group: General Forum Members
Last Login: Yesterday @ 3:56 PM
Points: 11,192, Visits: 11,096
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 »

Add to briefcase ««12345»»»

Permissions Expand / Collapse