Allow other users to run SELECT while TRANSACTION is being run by one user

• khatri03

  SSC Journeyman

  Points: 76

  More actions

  December 8, 2009 at 11:18 pm

  #135808

  Assume that a user U1 is running a transaction on a table T1.

  While the transaction is in process another user U2 tries to read from same table e.g T1.

  Now what happens here sql server lock the table T1 for other users except than U1 for any kind of query execution.

  Is it possible to allow user U2 to execute statement like SELECT * FROM T1 while transaction for user U1 is in process.

  Please explain with examples.

• Pavel Pawlowski

  Ten Centuries

  Points: 1244

  More actions

  December 9, 2009 at 4:07 am

  #1089736

  Hi,

  depends on what data you want read and depends on the database settings etc. You should think about correct transaction isolation levels etc.

  But.. If you want you to read uncommitted data you can use NOLOCK table hint:

  SELECT * FROM T1 WITH(NOLOCK)

  If you have database option READ_COMMITTED_SNAPSHOT ON, you can read also committed data with READCOMMITTED table hint

  SELECT * FROM T1 WITH(READCOMMITTED)

  See BOL for the "Table hints" and "TRANSACTION ISOLATION LEVEL" topics for more details.