Try to start SQL from a command line (not as a service) with -c -T1204 options
This should give you full info about deadlocks including blocking transactions listings. -----Original Message----- From: Rich Wild [mailto:[EMAIL PROTECTED] Sent: Friday, October 17, 2003 4:21 PM To: '[EMAIL PROTECTED]' Subject: RE: [ cf-dev ] sql deadlocks I don't have either of the scenarios you described. Just a page with a single insert, being run by many people (whilst others do selects on other pages) It's the inserts that are getting deadlocked (occasionally) though. > -----Original Message----- > From: Albert Popkov [mailto:[EMAIL PROTECTED] > Sent: 17 October 2003 16:18 > To: [EMAIL PROTECTED] > Subject: RE: [ cf-dev ] sql deadlocks > > > > Sorry. > Must be "commit tran" in the end of transaction samples > below. "coming tran" is the outlook's version of it. > > > > -----Original Message----- > From: Albert Popkov > Sent: Friday, October 17, 2003 4:12 PM > To: [EMAIL PROTECTED] > Subject: RE: [ cf-dev ] sql deadlocks > > > > If you're not using transactions you wouldn't get deadlock > simply because or inserts. You can get waiting locks - but > the're not the same as deadlocks. If fact I can't really > think of any way of getting deadlock without transaction. If > you not specifically using SQL transactions, you need to > check CFTRANSACTION tags in your code. > > With transactions there are 2 typical causes of deadlock: > > 1. if you select some data from the same table and then try > to update it within the same transaction: Eg begin tran > select mydata from mytable where key=somevalue update mytable > set something=value where key=somevalue coming tran > > Would fail if run concurrently. The solution is to use with > (updtable) in the first select. > > 2. if you have 2 transactions updating 2 tables in different > order Eg. begin tran update tableA update tableB coming tran > and begin tran update tableB update tableA coming tran > > would cause a deadlock. The solution is to make sure you're > updating tables In the same order when possible, or implement > some kind of syncronisation to make sure they will not run > concurrently. > (using select .. from ... with (updlock) for example)). > > > You can also play around redusing isolation level for some > transactions but I wouldn't recommend it. > > > Hope it helps, > Albert. > > > > > -----Original Message----- > From: Rich Wild [mailto:[EMAIL PROTECTED] > Sent: Friday, October 17, 2003 3:44 PM > To: '[EMAIL PROTECTED]' > Subject: RE: [ cf-dev ] sql deadlocks > > > Its not about the 2 pages, thinking about it, its just the > one that adds posts - I've got too many users runing that > page at the same time, and the sql server is deadlocking > them. Obviously the sql server is locking the entire table, > as other users can't add new rows at the same time as another user. > > All I have is a single simple cfquery INSERT. Nothing odd > about it - its not trying to do anything fancy. > > The forum is experiencing high traffic - which is causing this. > > > -----Original Message----- > > From: Justin [mailto:[EMAIL PROTECTED] > > Sent: 17 October 2003 15:42 > > To: [EMAIL PROTECTED] > > Subject: RE: [ cf-dev ] sql deadlocks > > > > > > > > Post the code? Justin > > > > > I have 2 pages - one to add a post to a thread and another > > to select > > > the entire thread and display it. > > > > > > -- > > ** Archive: > http://www.mail-archive.com/dev%> 40lists.cfdeveloper.co.uk/ > > > > > To unsubscribe, e-mail: > [EMAIL PROTECTED] > > For additional commands, e-mail: > [EMAIL PROTECTED] For > > human help, e-mail: [EMAIL PROTECTED] > > > > > -- > ** Archive: http://www.mail-archive.com/dev%40lists.cfdeveloper.co.uk/ > > To unsubscribe, e-mail: [EMAIL PROTECTED] > For additional commands, e-mail: > [EMAIL PROTECTED] For human help, e-mail: > [EMAIL PROTECTED] > > > -- > ** Archive: http://www.mail-archive.com/dev%40lists.cfdeveloper.co.uk/ > > To unsubscribe, e-mail: [EMAIL PROTECTED] > For additional commands, e-mail: > [EMAIL PROTECTED] For human help, e-mail: > [EMAIL PROTECTED] > > > -- > ** Archive: http://www.mail-archive.com/dev%40lists.cfdeveloper.co.uk/ > > To unsubscribe, e-mail: [EMAIL PROTECTED] > For additional commands, e-mail: > [EMAIL PROTECTED] For human help, e-mail: > [EMAIL PROTECTED] > -- ** Archive: http://www.mail-archive.com/dev%40lists.cfdeveloper.co.uk/ To unsubscribe, e-mail: [EMAIL PROTECTED] For additional commands, e-mail: [EMAIL PROTECTED] For human help, e-mail: [EMAIL PROTECTED] -- ** Archive: http://www.mail-archive.com/dev%40lists.cfdeveloper.co.uk/ To unsubscribe, e-mail: [EMAIL PROTECTED] For additional commands, e-mail: [EMAIL PROTECTED] For human help, e-mail: [EMAIL PROTECTED]
