June 3, 2013 at 3:05 am
HI
I have a table with 80 million records with PK on an int column. A service selects data from this table multiple times/sec in live environment.
I have to change the datetype of this PK column from int to BigInt. So I need to drop the PK constraint. But when I run the ALTER TABLE statement to drop it, it gets blocked by Select statements (they have NOLOCK in them). So i think it will be never ending wait.
Is there any other better way to achieve it?
Can I put some lock on table while altering it so that Select statements just wait for their turn but son't block the ALTER statement?
thanks
Online Trainer For SQL DBA and Developer @RedBushTechnologies with 18 yrs exp.
June 3, 2013 at 3:18 am
An ALTER needs a TABLE lock so you need to work around this.
I usually do it like this.
- First create a duplicate table based on the table that needs to be altered. Also don't miss any additional indexes or constraints
- Do the DDL modification on the duplicated table
- Rename the live table to another name. Rename the duplicated table to the live table (in 1 transaction). Don't forget to rename any indexes/contraints.
- Move the transactions missed from the old live table to the new live table
Note that this method is not always fail safe in all cases! Especially not if you have time dependant transactions in your table.
__________________
MS-SQL / SSIS / SSRS junkie
Visit my blog at dba60k.net
June 3, 2013 at 3:22 am
Do you have any FK's referencing that PK?
June 3, 2013 at 3:28 am
No. None are there.
SQLSACT (6/3/2013)
Do you have any FK's referencing that PK?
Online Trainer For SQL DBA and Developer @RedBushTechnologies with 18 yrs exp.
June 3, 2013 at 3:34 am
Table+index size is around 25 gigs. So building new table and indexes will take hours. But you are right that atleast it won't have impact on live server.
Online Trainer For SQL DBA and Developer @RedBushTechnologies with 18 yrs exp.
June 3, 2013 at 3:46 am
Just to add, there are no insert updates on table during daytime. Only SELECTS.
Online Trainer For SQL DBA and Developer @RedBushTechnologies with 18 yrs exp.
June 3, 2013 at 6:20 am
There will certainly be an impact on the server. While you're creating your duplicate table, the server will be processing lots of disk I/O, which will impact reads in other tables. How much impact depends on the server's horsepower and disk throughput. I would also make sure you have adequate disk space available before doing the copy. During the rename operation, reads against the original table will fail until the rename of the copy is complete. It will be minimized by doing both renames at once as recommended above, but it won't be avoided completely.
June 4, 2013 at 4:38 pm
S_Kumar_S (6/3/2013)
HII have a table with 80 million records with PK on an int column. A service selects data from this table multiple times/sec in live environment.
I have to change the datetype of this PK column from int to BigInt. So I need to drop the PK constraint. But when I run the ALTER_ TABLE statement to drop it, it gets blocked by Select statements (they have NOLOCK in them). So i think it will be never ending wait.
Is there any other better way to achieve it?
Can I put some lock on table while altering it so that Select statements just wait for their turn but son't block the ALTER statement?
thanks
The SELECT statements *will* wait their turns, as will the ALTER statement. SELECT statements acquire a SchS (schema stability) lock on objects they reference to prevent schema changes to those objects until they complete. An ALTER statement acquires a SchM (schema modification) lock on an object. The SchS and SchM locks are incompatible, which means a SchS lock can't be granted on an object while a SchM lock is already granted and vice versa.
SQL Server manages the grant of locks in a "relaxed first-in first-out" process. When a task requires a lock, the request is submitted to the lock manager. If the requested lock is compatible with all granted or pending lock requests, it is immediately granted (this is the "relaxed" aspect of the process). If the requested lock is incompatible with any lock request in the "granted" or "converting" state, it will be placed in a waiting state for a lock on the resource. Waiting lock requests are processed in the order received - that's the "first-in first-out" aspect of the process.
So, when you issue the ALTER statement, it requests a SchM lock on the table. If there are SELECT statements holding or waiting for SchS locks on the table, the ALTER statement's request is blocked and goes into a waiting state. As later SELECT statements request SchS locks, they are blocked by the ALTER statement's pending SchM request and must queue up to wait behind it. When the ALTER statement reaches the head of the line and all the SELECT statements holding granted SchS locks have released them, the lock manager will grant the ALTER statement's SchM lock and the change in datatype can occur. Of course, all SELECT statements with pending SchS lock requests on the table will be blocked until that operation completes and releases its SchM lock.
By the way, the NOLOCK hint in a SELECT statement only allows it to read through *data* locks held by other tasks - it will have no effect on a SchM lock. Also, the NOLOCK hint does not prevent other tasks from taking locks on an object, so it will not prevent an ALTER statement from obtaining a SchM lock.
So, what you should see is that your ALTER statement will be in a wait state until all the SELECT statements holding locks ahead of it release those locks. Once the ALTER statement reaches the head of the line and all other incompatible locks have been released, it will obtain its SchM lock and complete. SELECT statements that come in after the ALTER statement will have to queue up behind it for their locks.
Jason Wolfkill
June 7, 2013 at 5:26 am
Thanks All. for someone who falls on this thread, I did:
1.create new table with same structure
2.Move data in new tables in batches of 1L
3.create all indexes
4.rename table (happnened in millisecs without issue)
goood
Online Trainer For SQL DBA and Developer @RedBushTechnologies with 18 yrs exp.
Viewing 9 posts - 1 through 8 (of 8 total)
You must be logged in to reply to this topic. Login to reply