1

I can't seem to find a definitive answer for this, the question being, if I do a "cursor.execute('begin immediate')" statement before a select statement followed by a delete statement and then an insert statement and then the commit statement, will the database be locked before the select statement and all the way through to the commit statement?

The reason I want to do this is because I have a table that I basically rotate, taking the first record in the table, creating a couple variables from the result, and then taking that record, deleting it and then inserting it again as the last record in the table. The problem that I am having is that I run 4 scripts at the same time that all do the same thing and even though the table starts out looking something like this:

rowid 1, num 1
rowid 2, num 2
rowid 3, num 1
rowid 4, num 2
rowid 5, num 1
rowid 6, num 2
rowid 7, num 1
rowid 8, num 2

After some time, the table can end up looking like this:

rowid 250, num 2
rowid 251, num 1
rowid 252, num 1
rowid 253, num 2
rowid 254, num 2
rowid 255, num 2
rowid 256, num 1
rowid 257, num 1

I assume it ends up like that because sometimes a second script does a select right after the first script but before the first script has a chance to rotate the table.

Here's the script I have now:

##### get the next number from the db and rotate the table records
curs.execute('SELECT rowid, num FROM tbl_wheel ORDER BY rowid ASC LIMIT 1')
for row in curs:
    rowid = row[0]
    num = row[1]
query_rotate = 'DELETE FROM tbl_wheel WHERE rowid = ' + str(rowid)
curs.execute('begin')
curs.execute(query_rotate)
curs.execute('INSERT INTO tbl_wheel (num) VALUES (?)', str(num))
conn.commit()

I am wondering if I can fix the problem I'm having like this:

##### get the next number from the db and rotate the table records
curs.execute('begin immediate')
curs.execute('SELECT rowid, num FROM tbl_wheel ORDER BY rowid ASC LIMIT 1')
for row in curs:
    rowid = row[0]
    num = row[1]
query_rotate = 'DELETE FROM tbl_wheel WHERE rowid = ' + str(rowid)
curs.execute(query_rotate)
curs.execute('INSERT INTO tbl_wheel (num) VALUES (?)', str(num))
conn.commit()

EDIT: To clarify, the 4 scripts I have running that do the same thing, I should have said they each do mostly the same thing but a little bit different, depending on the variable it gets from the db. They then each do there own thing but then return to do exactly the same thing as each other, i.e. return to grab the next variable out of the db.

Jeff F
  • 975
  • 4
  • 14
  • 24
  • Are you sure you couldn't find an answer? It says in the documentation of course! http://initd.org/psycopg/docs/cursor.html .My suggestion for you is create one cursor and be absolutely sure you do your manipulation before deleting and committing. You are probably going to get some strange behavior if threading.. see link – FirebladeDan Aug 09 '15 at 22:48
  • The 4 scripts are each independent of the others but utilize the same database. Sometimes only 1 script is running, sometimes only 3 scripts are running, etc. Basically, I just want each script to use the variable it gets from the db in the order that I originally set up the table; and rotate the records in the table in the same order. Is there a way to prevent the other scripts from doing a select while the other script is executing the rotation? – Jeff F Aug 09 '15 at 23:09
  • Luckily you're using sqllite. http://stackoverflow.com/questions/9070369/locking-a-sqlite3-database-in-python-re-asking-for-clarification You're good to go with the answer by hops :) – FirebladeDan Aug 09 '15 at 23:19
  • I think there is still a problem. One of the comments: "One important thing to note is that when the database is locked, it means that it doesn't accept multiple writers. However, it does accept multiple readers." And, I believe multiple readers is the problem as 2 scripts could read the table nearly at the exact same time, set the same variable and then "rotate" the records using the same variable, so I would still get, instead of 1, 2...1, 1 inserted at the end of the table. – Jeff F Aug 10 '15 at 00:19
  • At that point it sounds like some of your logic is duplicated. Either run your scripts in series or use if statements in your script to determine logic direction. Imagine three file writers reading the same file at the same time doing the same logic..... WHY? If logic is dupped do it in the first one and then fire the subcases – FirebladeDan Aug 10 '15 at 01:09
  • Show the table definition. – CL. Aug 10 '15 at 12:43
  • I am now using the 2nd example shown above, putting the "curs.execute('begin immediate')" before the select statement in all 4 scripts, and it seems to be working. BEGIN IMMEDIATE does not prevent selects from being performed but because the only other processes that might do this particular select are the other scripts I have running, well, any other script I have running has to wait until the first script has done it's own select followed by the delete and insert. I'm not sure I'm explaining this right but anyway, hope it helps someone. – Jeff F Aug 10 '15 at 18:56

0 Answers0