title: Cassandra CQL UPDATE with IF
body: <p>Newbie here (and it seems like it might be a newbie question).
  Using <code>Ubuntu 14.04</code> with a fresh install of <code>Cassandra 2.1.1, CQL 3.2.0</code> (it says). 
  Writing a back-end database for a <strong>CherryPy site,</strong> initially as a session database.</p>
  
  <p>I've come up with a scheme for a kind of <strong>'row locking'</strong> as a session lock, but it doesn't seem to be hanging together, so I've reduced it to a simple test program running against a local <strong>Cassandra</strong> instance.  To run this test, I open two terminal windows to run two python instances of it at the same time, each with different instance numbers ('1' and '2').</p>
  
  <pre><code>import time, sys, os, cassandra
  from cassandra.cluster      import Cluster
  from cassandra.auth         import PlainTextAuthProvider
  instance = sys.argv[1] 
  cluster = Cluster( auth_provider=PlainTextAuthProvider( username='cassandra', password='cassandra'))
  cdb = cluster.connect()
  cdb.execute("CREATE KEYSPACE IF NOT EXISTS test WITH replication = {'class':'SimpleStrategy', 'replication_factor' : 1}")
  cdb.execute("CREATE TABLE IF NOT EXISTS test.test ( id text primary key, val int, lock text )")
  cdb.execute("INSERT INTO test.test (id, val, lock) VALUES ('session_id1', 0, '') ")
  raw_input( '&lt;Enter&gt; to start ... ')
  
  i = 0
  while i &lt; 10000:
      i += 1
      # set lock
      while True:
          r = cdb.execute( "UPDATE test.test SET lock = '%s' WHERE id = 'session_id1' IF lock = '' " % instance)
          if r[0].applied == True:
              break
      # check lock and increment val
      s0 = cdb.execute("SELECT val,lock FROM test.test WHERE id = 'session_id1' " )[0]
      if s0.lock != instance:
          print 'error: instance [%s] %s %s' % (instance, s0, r[0])
      cdb.execute( "UPDATE test.test SET val = %s WHERE id = 'session_id1'", (s0.val + 1,))        
      # clear lock
      cdb.execute( "UPDATE test.test SET lock = '' WHERE id = 'session_id1' ")        
      time.sleep( .01)
  </code></pre>
  
  <p>So if I understand correctly, the UPDATE..IF should be 'applied' (and the break taken) only if the existing value of lock is '' (an empty string), so this should give an effective exclusive lock on the row.</p>
  
  <p>The problem is that the <code>'s1.lock != instance'</code> test quite frequently fires, showing that despite the UPDATE being applied, the value of lock afterwards is variously still '' or that of the other instance...</p>
  
  <p>I know that when I roll out to a cluster I'm going to have to manage consistency issues, but this is against a single local Cass instance - surely consistency shouldn't be a problem here?</p>
  
  <p>I can't imagine this CQL form is broken (tm), so it must be me.  What am I doing wrong, or what is it I don't understand? TIA.</p>
  
  <p><strong>UPDATE:</strong> Ok, I googled a lot on this before I posted here, and now have spent the day since posting doing the same.</p>
  
  <p>In particular, the stackoverflow posting <a href="https://stackoverflow.com/questions/24406252/cassandra-optimistic-locking">Cassandra Optimistic Locking</a> is addressing a similar issue (for a different reason), and his solution was: </p>
  
  <pre><code>"update table1 set version_num = 5 where name = 'abc' if version_num = 4"
  </code></pre>
  
  <p>which he says works for him - but is really exactly what I am doing, but which isn't working for me.</p>
  
  <p>So I believe my approach to be sound, but clearly I have a problem.</p>
  
  <p>Are there any environmental issues that could be affecting me? (installation, pythonic, whatever...) </p>