Thread: mysql and locks

    #1
  1. No Profile Picture
    Registered User
    Devshed Newbie (0 - 499 posts)

    Join Date
    Dec 2004
    Posts
    12
    Rep Power
    0

    mysql and locks


    Hi,

    I have a program that creates a handful threads. Those threads access the database for reading and writing whenever they feel for it. Will it be sufficient to use the built in mysql locks like this:

    Code:
    def lock(db, tables):
        sql = 'LOCK TABLES %s READ'%(tables)
        db.execute(sql)
    
    def lockw(db, tables):
        sql = 'LOCK TABLES %s WRITE'%(tables)
        db.execute(sql)
    
    def unlock(db):
        sql = 'UNLOCK TABLES'
        db.execute(sql)
    I received an error:
    Commands out of sync; You can't run this command now.

    The error is refering to db.execute(sql) in lock(db, tables)

    It has worked like this for the last week... and suddenly i get this anoing error... and it gives me some hint that i might have a scale problem. Which is not good at all
  2. #2
  3. Contributing User
    Devshed Newbie (0 - 499 posts)

    Join Date
    Jan 2005
    Posts
    174
    Rep Power
    11
    it's possible that as your db grew over the week one of the threads is out of sync, finishing before the other, causing an exception. I remember somethings about threading.Rlock() in the doc's but I haven't put it to use yet myself
  4. #3
  5. No Profile Picture
    Registered User
    Devshed Newbie (0 - 499 posts)

    Join Date
    Dec 2004
    Posts
    12
    Rep Power
    0
    I don't get it... those locks should take care of the syncronization dont they? and additional locks would just be waste or what? they handle multiple reads and single write... atleast what the documentation said...
  6. #4
  7. No Profile Picture
    Registered User
    Devshed Newbie (0 - 499 posts)

    Join Date
    Dec 2004
    Posts
    12
    Rep Power
    0
    Or does those mysql locks only work between different processes accessing the database?
  8. #5
  9. No Profile Picture
    Registered User
    Devshed Newbie (0 - 499 posts)

    Join Date
    Dec 2004
    Posts
    12
    Rep Power
    0
    sorry... my explorer stoped for a while... causing a frustrated daniel enter submit to many times...
  10. #6
  11. No Profile Picture
    Registered User
    Devshed Newbie (0 - 499 posts)

    Join Date
    Dec 2004
    Posts
    12
    Rep Power
    0
    During a good night sleep a solution came out in my dreams...

    It is a server which is suppose to handle many requests. These requests require access to a database. My first solution which ended in the error stated above it looked like this:

    Code:
    import MySQLdb, thread, marshal, sys
    from socket import *
    
    # my modules
    import mygl 
    import mydb
    
    class XMPP_server:
    
        def __init__(self):
            
            # open the database
            self.db = mydb.openDatabase()
    
            self.port = mygl.SPORT
            # create the necessary socket connections
            self.s = socket(AF_INET, SOCK_STREAM)
            self.s.bind(('',self.port))
            self.s.listen(5)
            
            # start the core loop
            self.server_loop()
    
        # the core loop starts new thread for each new connection
        def server_loop(self):
            
            while 1:
                client, addr = self.s.accept()
                print 'Got connection from ',addr
                thread.start_new_thread(self.handle_request,(client, addr))
            sels.s.close()
    The handle request will leade to different database accesses simultaneous... but if change the server loop to:

    Code:
    # the core loop starts new thread for each new connection
        def server_loop(self):
            
            while 1:
                client, addr = self.s.accept()
                print 'Got connection from ',addr
                self.handle_request(client, addr)
            sels.s.close()
    This solves my problem. But what happends? Will this solution cause my server to process less messages than before? On the other hand I save some time not spawning off a thread and the locks are superfluous. This causes the processing of messages a litle bit faster. Most of the requests have a constant complexity (select from the database and send the result back). Only a few of them have a bad complexity of N^3 (three for-loops). Those last requests will be time consuming when number of users increase. I could spawn a thread for those requests but then again I will need locks again and the same problem arise.
  12. #7
  13. Contributing User
    Devshed Newbie (0 - 499 posts)

    Join Date
    Jan 2005
    Posts
    174
    Rep Power
    11

    threads and databases


    My big long hairy response not germane to question so I removed it...

    I need to understand the Thread method of the threading module more if I want to subclass it etc during a socket connection.

    I'd be interested in your solution

IMN logo majestic logo threadwatch logo seochat tools logo