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 ««12

ROWLOCK Expand / Collapse
Author
Message
Posted Tuesday, March 19, 2013 4:51 AM
SSC Veteran

SSC VeteranSSC VeteranSSC VeteranSSC VeteranSSC VeteranSSC VeteranSSC VeteranSSC Veteran

Group: General Forum Members
Last Login: Sunday, November 23, 2014 5:13 AM
Points: 231, Visits: 759

Isolation level setting comming from application only ı can edit triger that s why ı try to find a hint
Post #1432565
Posted Tuesday, March 19, 2013 5:41 AM


SSC-Forever

SSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-Forever

Group: General Forum Members
Last Login: Today @ 6:32 AM
Points: 40,609, Visits: 37,064
Right before the BEGIN TRANSACTION, put SET TRANSACTION ISOLATION LEVEL REPEATABLE READ, since you are writing the begin tran, you can write that too.


Gail Shaw
Microsoft Certified Master: SQL Server 2008, MVP
SQL In The Wild: Discussions on DB performance with occasional diversions into recoverability

We walk in the dark places no others will enter
We stand on the bridge and no one may pass

Post #1432582
Posted Wednesday, March 20, 2013 2:20 AM
SSC Veteran

SSC VeteranSSC VeteranSSC VeteranSSC VeteranSSC VeteranSSC VeteranSSC VeteranSSC Veteran

Group: General Forum Members
Last Login: Sunday, November 23, 2014 5:13 AM
Points: 231, Visits: 759
I think this the same think
REPEATABLE isolation level and row base locking
SELECT ID FROM TESTROWLOCK (HOLDLOCK,ROWLOCK)
Post #1433053
Posted Wednesday, March 20, 2013 2:33 AM


SSC-Forever

SSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-Forever

Group: General Forum Members
Last Login: Today @ 6:32 AM
Points: 40,609, Visits: 37,064
Leave out the hints, just set the isolation level before you start the transaction. There's no good reason to use hints over the explicit isolation level.


Gail Shaw
Microsoft Certified Master: SQL Server 2008, MVP
SQL In The Wild: Discussions on DB performance with occasional diversions into recoverability

We walk in the dark places no others will enter
We stand on the bridge and no one may pass

Post #1433060
Posted Wednesday, March 20, 2013 2:44 AM
SSC Veteran

SSC VeteranSSC VeteranSSC VeteranSSC VeteranSSC VeteranSSC VeteranSSC VeteranSSC Veteran

Group: General Forum Members
Last Login: Sunday, November 23, 2014 5:13 AM
Points: 231, Visits: 759
it is trigger in that s why i set the hints application before update set isolation level read commited

Is it true to change isolation level in triger

UPDATE isolation level read commited
triger isolation level in repeatable read ?

and ı test it when ı used that hints in transaction nobody can update that rows and ı want it
Post #1433065
Posted Wednesday, March 20, 2013 3:05 AM


SSC-Forever

SSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-Forever

Group: General Forum Members
Last Login: Today @ 6:32 AM
Points: 40,609, Visits: 37,064
Sure you can change the isolation level in a trigger. The new isolation level takes effect from that point until it's set to something else. Isolation level specified by SET TRANSACTION ISOLATION LEVEL overrides whatever the app specified.


Gail Shaw
Microsoft Certified Master: SQL Server 2008, MVP
SQL In The Wild: Discussions on DB performance with occasional diversions into recoverability

We walk in the dark places no others will enter
We stand on the bridge and no one may pass

Post #1433071
Posted Wednesday, March 20, 2013 3:12 AM
SSC Veteran

SSC VeteranSSC VeteranSSC VeteranSSC VeteranSSC VeteranSSC VeteranSSC VeteranSSC Veteran

Group: General Forum Members
Last Login: Sunday, November 23, 2014 5:13 AM
Points: 231, Visits: 759
Thanks much
Post #1433073
Posted Thursday, March 21, 2013 9:15 AM


SSCarpal Tunnel

SSCarpal TunnelSSCarpal TunnelSSCarpal TunnelSSCarpal TunnelSSCarpal TunnelSSCarpal TunnelSSCarpal TunnelSSCarpal TunnelSSCarpal Tunnel

Group: General Forum Members
Last Login: Yesterday @ 5:39 AM
Points: 4,463, Visits: 6,391
I note that some form of index on ID should probably be in place here to help both with concurrency and performance.

Best,

Kevin G. Boles
SQL Server Consultant
SQL MVP 2007-2012
TheSQLGuru at GMail
Post #1433855
« Prev Topic | Next Topic »

Add to briefcase ««12

Permissions Expand / Collapse