How can I Cause a Deadlock in MySQL for Testing Purposes?

You want something along the following lines.

You want something along the following lines. Parent. Py import subprocess c1= subprocess.

Popen( "python", "child. Py", "1", stdin=subprocess. PIPE, stdout=subprocess.

PIPE ) c2= subprocess. Popen( "python", "child. Py", "2", stdin=subprocess.

PIPE, stdout=subprocess. PIPE ) out1, err1= c1. Communicate( "to 1: hit it!" ) print " 1:", repr(out1) print "*1:", repr(err1) out2, err2= c2.

Communicate( "to 2: ready, set, go! " ) print " 2:", repr(out2) print "*2:", repr(err2) out1, err1= c1.communicate() print " 1:", repr(out1) print "*1:", repr(err1) out2, err2= c2.communicate() print " 2:", repr(out2) print "*2:", repr(err2) c1.wait() c2.wait() child.Py import yourDBconnection as dbapi2 def child1(): print "Child 1 start" conn= dbapi2. Connect( ... ) c1= conn.cursor() conn.begin() # turn off autocommit, start a transaction ra= c1.

Execute( "UPDATE A SET AC1='Achgd' WHERE AC1='AC1-1'" ) print ra print "Child1", raw_input() rb= c1. Execute( "UPDATE B SET BC1='Bchgd' WHERE BC1='BC1-1'" ) print rb c1.close() print "Child 1 finished" def child2(): print "Child 2 start" conn= dbapi2. Connect( ... ) c1= conn.cursor() conn.begin() # turn off autocommit, start a transaction rb= c1.

Execute( "UPDATE B SET BC1='Bchgd' WHERE BC1='BC1-1'" ) print rb print "Child2", raw_input() ra= c1. Execute( "UPDATE A SET AC1='Achgd' WHERE AC1='AC1-1'" ) print ta c1.close() print "Child 2 finish" try: if sys. Argv1 == "1": child1() else: child2() except Exception, e: print repr(e) Note the symmetry.

Each child starts out holding one resource. Then they attempt to get someone else's held resource. You can, for fun, have 3 children and 3 resources for a really vicious circle.

Note that difficulty in contriving a situation in which deadlock occurs. If your transactions are short -- and consistent -- deadlock is very difficult to achieve. Deadlock requires (a) transaction which hold locks for a long time AND (b) transactions which acquire locks in an inconsistent order.

I have found it easiest to prevent deadlocks by keeping my transactions short and consistent. Also note the non-determinism. You can't predict which child will die with a deadlock and which will continue after the other died.

Only one of the two need to die to release needed resources for the other. Some RDBMS's claim that there's a rule based on number of resources held blah blah blah, but in general, you'll never know how the victim was chosen. Because of the two writes being in a specific order, you sort of expect child 1 to die first.

However, you can't guarantee that.It's not deadlock until child 2 tries to get child 1's resources -- the sequence of who acquired first may not determine who dies. Also note that these are processes, not threads. Threads -- because of the Python GIL -- might be inadvertently synchronized and would require lots of calls to time.

Sleep( 0.001 ) to give the other thread a chance to catch up. Processes -- for this -- are slightly simpler because they're fully independent.

I'm not familar with Python, so excuse my incorrect language If I'm saying this wrong... but open two sessions (in separate windows, or from separate Python processes - from separate boxes would work ... ) Then ... . In Session A: Begin Transaction Insert TableA() Values()... . Then In Session B: Begin Transaction Insert TableB() Values()... Insert TableA() Values() ... .

Then go back to session A Insert TableB() Values () ... You'll get a deadlock...

Then after you start session A, start SessionB... The second half of Session A (After the 10 sec delay, would happen after Session B and that would create the deadlock... – Charles Bretana Nov 6 '08 at 22:51 1 Or use thread synchronization primitives to coordinate the threads. – Ned Batchelder Nov 6 '087 at 2:52.

Not sure if either above is correct. Check out this: xaprb.com/blog/2006/08/08/how-to-deliber....

You can always run LOCK TABLE tablename from another session (mysql CLI for instance). That might do the trick. It will remain locked until you release it or disconnect the session.

– Greg Nov 6 '08 at 22:10 @Greg: I think they're talking about one session doing LOCK TABLE A, and another session doing LOCK TABLE B. T have to synchronize somehow at this point. Then session one attempts a LOCK TABLE B.

When session two attempts LOCK TABLE A -- it will deadlock. – S. Lott Nov 7 '08 at 10:56.

S. Lott Looks promising but here's what I get when I run it: 1: 'Child 1 start\r\n0\r\nChild1 to 1: hit it! \r\n0\r\nChild 1 finished\r\n' *1: None 2: 'Child 1 start\r\n0\r\nChild1 to 2: ready, set, go!

\r\n0\r\nChild 1 finished \r\n' *2: None 1: '' *1: None 2: '' *2: None (I couldn't comment this on your post since there's no formatting in comments).

Autocommit is probably true by default. Use explicit connection.begin() calls. – S.

Lott Nov 7 '08 at 20:42 I tried adding c1. Execute("SET AUTOCOMMIT=0");c1. Execute("START TRANSACTION") after the c1= conn.cursor() lines but still the same result.

– Greg Nov 7 '08 at 21:51 I think you need to call begin() on the connection to start a transaction. – S. Lott Nov 7 '08 at 22:30 Also, locking depends on your storage engine.

Which engine are you using? – S. Lott Nov 7 '08 at 23:12 Hmm, still same result, and I get this: "DeprecationWarning: begin() is non-standard and will be removed in 1.3".

– Greg Nov 7 '08 at 23:25.

Here's some pseudocode for how I do it in PHP: Script 1: START TRANSACTION; INSERT INTO table ; SLEEP(5); UPDATE table SET field = 'foo'; COMMIT; Script 2: START TRANSACTION; UPDATE table SET field = 'foo'; SLEEP(5); INSERT INTO table ; COMMIT; Execute script 1 and then immediately execute script 2 in another terminal. You'll get a deadlock if the database table already has some data in it (In other words, it starts deadlocking after the second time you try this). Note that if mysql won't honor the SLEEP() command, use Python's equivalent in the application itself.

I cant really gove you an answer,but what I can give you is a way to a solution, that is you have to find the anglde that you relate to or peaks your interest. A good paper is one that people get drawn into because it reaches them ln some way.As for me WW11 to me, I think of the holocaust and the effect it had on the survivors, their families and those who stood by and did nothing until it was too late.