Shantanu's Blog

How do I know which connection is holding on to the lock?

Identifying what user and thread are holding on to a meta data lock that is preventing other queries from running is not easily traceable. In some cases, a "stale" transaction holds a row lock.

mysql> select trx_mysql_thread_id, trx_started from information_schema.innodb_trx where trx_rows_locked > 0 and trx_query IS NULL order by trx_started;
+---------------------+---------------------+
| trx_mysql_thread_id | trx_started |
+---------------------+---------------------+
| 1 | 2012-06-15 13:48:20 |
| 5 | 2012-06-15 13:58:26 |
+---------------------+---------------------+

mysql> select now() - trx_started as active_period, trx_mysql_thread_id, trx_started from information_schema.innodb_trx where trx_rows_locked > 0 and trx_query IS NULL and (now() - trx_started > 600) order by trx_started;
+---------------+---------------------+---------------------+
| active_period | trx_mysql_thread_id | trx_started |
+---------------+---------------------+---------------------+
| 5689.000000 | 1 | 2012-06-15 13:48:20 |
| 4683.000000 | 5 | 2012-06-15 13:58:26 |
+---------------+---------------------+---------------------+

import sqlite3
conn = sqlite3.connect('/root/accounts.db', timeout=10)
c = conn.cursor()

c.execute('''CREATE TABLE stocks (date text, trans text, symbol text, qty real, price real)''')
c.execute("INSERT INTO stocks VALUES ('2006-01-05','BUY','RHAT',100,35.14)")

# Do this instead
t = ('2013-01-05','BUY','RHAT',100,35.14)

c.execute('INSERT INTO stocks VALUES (?,?,?,?,?)', t)

# Larger example that inserts many records at a time
purchases = [('2014-03-28', 'BUY', 'IBM', 1000, 45.00),
             ('2015-04-05', 'BUY', 'MSFT', 1000, 72.00),
             ('2016-04-06', 'SELL', 'IBM', 500, 53.00),
            ]
c.executemany('INSERT INTO stocks VALUES (?,?,?,?,?)', purchases)

# print all rows
for row in c.execute('SELECT * FROM stocks ORDER BY price'):
        print row

conn.commit()
conn.close()

_____

conn = sqlite3.connect('/Users/test/Desktop/my-accounts.db')
currentAccount = None

for row in conn.execute('SELECT email FROM accounts WHERE active=0'):
    currentAccount = row[0]
    print "Checking out: ",currentAccount
    break

if currentAccount is None:
    print "No available accounts"
else:
    conn.execute('UPDATE accounts SET active=1 WHERE email=?', [currentAccount,])
conn.close()

conn.commit()

_____

# A minimal SQLite shell for experiments

import sqlite3

con = sqlite3.connect(":memory:")
con.isolation_level = None
cur = con.cursor()

buffer = ""

print "Enter your SQL commands to execute in sqlite3."
print "Enter a blank line to exit."

while True:
    line = raw_input()
    if line == "":
        break
    buffer += line
    if sqlite3.complete_statement(buffer):
        try:
            buffer = buffer.strip()
            cur.execute(buffer)

            if buffer.lstrip().upper().startswith("SELECT"):
                print cur.fetchall()
        except sqlite3.Error as e:
            print "An error occurred:", e.args[0]
        buffer = ""

con.close()

_____


sqlite3 is the obvious alternative for a persistent store in python.
But its powerful SQL interface can be too complex when you just want a dict.

import sqlite3dbm
db = sqlite3dbm.sshelve.open('mydb.sqlite3')
db['foo'] = {'count': 100, 'ctr': .3}
db['bar'] = {'count': 314, 'ctr': .168}
db.items()
[('foo', {'count': 100, 'ctr': 0.29999999999999999}), ('bar', {'count': 314, 'ctr': 0.16800000000000001})]

db['foo']['count']
100

// connect
import boto.dynamodb
conn = boto.connect_dynamodb (aws_access_key_id='', aws_secret_access_key='')

// list tables
conn.list_tables()
[u'MTA', u'reply']

// select table
table = conn.get_table('reply')

// select a record
item = table.get_item(hash_key='Amazon DynamoDB#DynamoDB Thread 2', range_key='2012-01-31 23:28:40')
print item

// update record
item['PostedBy'] = 'User X'
item.put()

// delete record
item.delete()

// add record
item_data = {
        'Message': 'DynamoDB Thread 3 Reply 4 text',
        'SentBy': 'User A',
        'PostedBy': 'User oksoft',
        'ReceivedTime': '12/9/2011 11:36:03 PM',
    }

item = table.new_item(
        # Our hash key is 'forum'
        hash_key='Amazon DynamoDB#DynamoDB Thread 3',
        # Our range key is 'subject'
        range_key='2013-04-24 23:28:40',
        # This has the
        attrs=item_data
    )

item.put()

It is possible to recover a deleted file if S3 versioning is enabled.
We simply need to find the version ID of the deleted file and restore it back on S3.

import boto
c = boto.connect_s3('access-key','secret-key')
bucket = c.get_bucket('todelapr15')

rs = bucket.list_versions()
for key in rs:
  print key, key.version_id

The above code will list all the files and their version ID as shown below.

74eHPzvO1HpNvyujXn1.hs.UG5yObfh_
p7uMSJjrNLwJbclPPbYo56TPyb1L4asu
gjhBuYB4RgpwGJt6DCbIFHVBX7Od_hP.
IoBQ7FB1h4kDJrvzLlDmhPtBRts.14RF
WN4oSvCvoOmfmnAPT5pZS.YFCohyrvjh
Im3ci98wSsng8vaAZNDBsw8hoaTYcR2l

The deleted file needs to be copied first to a new location before it can be downloaded.

bucket.copy_key('new_ptty.exe', 'todelapr15/testing', 'ptty.exe', metadata=None, src_version_id='WN4oSvCvoOmfmnAPT5pZS.YFCohyrvjh')

YAML is a human friendly data serialization standard for all programming languages including python.

>>> import yaml
>>> document = """
  a: 1
  b:
    c: 3
    d: 4
"""

>>>print yaml.dump(yaml.load(document))
a: 1
b: {c: 3, d: 4}

>>> print yaml.dump(yaml.load(document), default_flow_style=False)
a: 1
b:
  c: 3
  d: 4

https://pypi.python.org/pypi/PyYAML