Transactions

Transactions

• sdbleier

  That's not good approch. However you may obtain this information from sp_lock().

  Did you look at sp_getapplock() I think it would suits you best.

  
  

• gb_cary_nc

  gavrilenko_s wrote:

  That's not good approch. However you may obtain this information from sp_lock(). Did you look at sp_getapplock() I think it would suits you best.

  If you acquire an exclusive lock with sp_getapplock(), then the second transaction that will attempt to acquire the same lock will be blocked. If this is not an issue then really sp_getapplock() suits best. Otherwise, use the SyncRoot table-based lock approach. As I mentioned, it doesnt produce any deadlocks.

  
  
  

• Janne B

  The thing I am going to suggest is a rather artificial one yet it will work perfectly.
  Just create a table, name it, say, SyncRoot, insert a single row into it.
  Then, lock this row in exclusive mode at start of your transaction.
  Like this.
  
  If exists (select * from SyncRoot(XLOCK, READPAST))
  Begin
  //Your transaction code goes here.
  End
  
  If one instance of this transaction is run, it will lock the single row of SyncRoot in X mode. If a second instance of the transaction is run, it will not enter the Begin-End block, since the only row in SyncRoot is locked in X mode, and READPAST hint skips that row. The second instance will just exit on that point.
  Note that READPAST also prevents deadlocks from occuring.

  
  
  

• Ben Arnold

  i think transaction pertaining to

  begin tran and end tran is missued in this query.

  if you are  referring to a SQL server transaction

  you can set the transaction isolation level to serializable

  A transaction operating at the Serializable level guarantees that all concurrent transactions interact only in ways that produce the same effect as if each transaction were entirely executed one after the other. At this isolation level, dirty reads, nonrepeatable reads, and phantoms are not possible.

   

   

   

  
  
  

• Opteron64

  What i dont understand is......

  the transaction is running and the transaction has a name....why can't we query some table where this transaction name is and find out if it is running or not. is this possible in SQL 2000

  
  

• amitPrigozin

  Look at sp_getapplock() proc.
  
• Transactions