Mercurial > p > roundup > code
comparison roundup/backends/back_sqlite.py @ 5143:9cebf686c552
issue2550727: db.newid is broken with sqlite.
Added proper transaction lock around the sql code to get a new id. The
the locking that pysqlite attempts had to be defeated because it is
broken. Had to explicitly manage transactions with BEGIN IMMEDIATE
and call sql_commit.
Note that this reduces performance by 30% in return for accuracy.
Also use update call set newid=newid+1 rather than incrementing in
python.
| author | John Rouillard <rouilj@ieee.org> |
|---|---|
| date | Wed, 13 Jul 2016 22:30:07 -0400 |
| parents | e74c3611b138 |
| children | e1e40674a0bc |
comparison
equal
deleted
inserted
replaced
| 5142:93832cec4c31 | 5143:9cebf686c552 |
|---|---|
| 364 | 364 |
| 365 # old-skool id generation | 365 # old-skool id generation |
| 366 def newid(self, classname): | 366 def newid(self, classname): |
| 367 """ Generate a new id for the given class | 367 """ Generate a new id for the given class |
| 368 """ | 368 """ |
| 369 | |
| 370 # Prevent other processes from reading while we increment. | |
| 371 # Otherwise multiple processes can end up with the same | |
| 372 # new id and hilarity results. | |
| 373 # | |
| 374 # Defeat pysqlite's attempts to do locking by setting | |
| 375 # isolation_level to None. Pysqlite can commit | |
| 376 # on it's own even if we don't want it to end the transaction. | |
| 377 # If we rewrite to use another sqlite library like apsw we | |
| 378 # don't have to deal with this autocommit/autotransact foolishness. | |
| 379 self.conn.isolation_level = None; | |
| 380 # Manage the transaction locks manually. | |
| 381 self.sql("BEGIN IMMEDIATE"); | |
| 382 | |
| 369 # get the next ID | 383 # get the next ID |
| 370 sql = 'select num from ids where name=%s'%self.arg | 384 sql = 'select num from ids where name=%s'%self.arg |
| 371 self.sql(sql, (classname, )) | 385 self.sql(sql, (classname, )) |
| 372 newid = int(self.cursor.fetchone()[0]) | 386 newid = int(self.cursor.fetchone()[0]) |
| 373 | 387 |
| 374 # update the counter | 388 # leave the next larger number as the next newid |
| 375 sql = 'update ids set num=%s where name=%s'%(self.arg, self.arg) | 389 sql = 'update ids set num=num+1 where name=%s'%self.arg |
| 376 vals = (int(newid)+1, classname) | 390 vals = (classname,) |
| 377 self.sql(sql, vals) | 391 self.sql(sql, vals) |
| 392 | |
| 393 # reset pysqlite's auto transact stuff to default since the | |
| 394 # rest of the code expects it. | |
| 395 self.conn.isolation_level = ''; | |
| 396 # commit writing the data, clearing locks for other processes | |
| 397 # and create a new cursor to the database. | |
| 398 self.sql_commit(); | |
| 378 | 399 |
| 379 # return as string | 400 # return as string |
| 380 return str(newid) | 401 return str(newid) |
| 381 | 402 |
| 382 def setid(self, classname, setid): | 403 def setid(self, classname, setid): |