How to I add column for a table online?

• salliven

  SSCrazy

  Points: 2644

  More actions

  August 4, 2012 at 9:21 am

  #259701

  Hello!

  I have a database with a big tabla (600 millions row).

  My task: add 3 column for this table online. (ALTER TALE table_name ADD column_name INT NULL).

  How to I add column for this table online?

  My problem:

  Session 1:

  BEGIN TRAN

  SELECT columns FROM table WITH (xlock) WHERE id = 3

  -- This is a row lock

  Session 2:

  ALTER TABLE table ADD column_name INT NULL

  -- This is a Sch-M lock, wait for Session 1

  Session 3:

  UPDATE table SET columns = values WHERE id = 2554

  -- wait for Session 2

  (But Session 1 and Session 3 are compatibility without session 2).

• salliven

  SSCrazy

  Points: 2644

  More actions

  August 6, 2012 at 11:28 am

  #1522192

  My task is done, with this idea:

  1.) Using lock_timeout on the session

  2.) Search object with sp_lock stored procedure

  If the object is not locked, then alter table ... add column

  Thanks