--
為什麼？
會造成資料庫損壞的原因大部分的是同時寫入。所以開發自己測試時一切正常，上線就會出問題。
--
修復
導出資料
|
1 |
sqlite3.exe Game.db .dump > dump.sql |
建立新資料庫及匯入資料
|
1 |
sqlite3.exe Game.db < dump.sql |
使用互動模式
|
1 2 3 |
sqlite3.exe Game.db sqlite>.read dump.sql sqlite>.quit |
--
Python 多線程單一寫入
app.py
|
1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22 23 24 25 26 27 28 29 30 31 32 33 34 35 36 37 38 39 40 41 42 43 44 |
# App.py 主要部分 import queue ... class App: def __init__(self): self.environment = 'company' self.rs = None self.cursor = None self.client = None self.room_play = {} # === DB相關 === self.db_queue = queue.Queue() self.db_writer_running = True self.db_lock = threading.Lock() self.conn = self._init_db_conn() threading.Thread(target=self._db_writer_thread, daemon=True).start() def _init_db_conn(self): if self.environment == 'company': conn = sqlite3.connect('Game.db', isolation_level=None, check_same_thread=False) else: conn = sqlite3.connect('/dev/shm/EquationHiLow.sqlite3', isolation_level=None, check_same_thread=False) conn.row_factory = sqlite3.Row return conn def db_connect(self): # 只用於 SELECT return self.conn.cursor() def execute_db(self, sql, params=None): self.db_queue.put((sql, params or {})) def _db_writer_thread(self): while self.db_writer_running: try: sql, params = self.db_queue.get() with self.db_lock: self.conn.execute(sql, params) self.conn.commit() except Exception as e: logging.error(f"[DB Write Error] {e}") |
db.py
|
1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22 23 24 25 26 27 28 29 30 31 32 33 34 35 36 37 38 39 40 41 42 43 44 45 46 47 48 49 50 51 52 53 54 55 |
def run(client, self, j): cursor_select = self.db_connect() room_number = 0 i = 0 while i < 10: room_number = random.randint(100000, 999999) sql = "SELECT * FROM Room WHERE RoomId=:RoomId" cursor_select.execute(sql, {'RoomId': room_number}) room = cursor_select.fetchone() if not room: break i += 1 try: sql = "SELECT * FROM Room WHERE OwnerId=:OwnerId" cursor_select.execute(sql, {'OwnerId': j['user_id']}) room = cursor_select.fetchone() if room: # 清理使用者舊資料（透過 queue 寫入） self.execute_db("DELETE FROM Room WHERE OwnerId=:OwnerId", {'OwnerId': j['user_id']}) self.execute_db("DELETE FROM RoomStatus WHERE RoomId=:RoomId", {'RoomId': room['RoomId']}) self.execute_db("DELETE FROM Player WHERE RoomId=:RoomId", {'RoomId': room['RoomId']}) self.execute_db( "INSERT INTO Room (RoomId, Name, OwnerId, Status) VALUES (:RoomId, :Name, :OwnerId, 0)", { 'RoomId': room_number, 'Name': j['name'], 'OwnerId': j['user_id'], } ) self.execute_db( "INSERT INTO RoomStatus (RoomId, `Order`, People) VALUES (:RoomId, 1, :People)", { 'RoomId': room_number, 'People': int(j['people']), } ) except Exception as e: logging.error(e) send_command = { "command": 'create_room_ok', } send_command = str(json.dumps(send_command)) client.publish('EquationHiLow/' + j['user_id'], send_command.encode()) # 呼叫 get_lobby 更新 get_lobby_run(client, self, j) |
--
405 total views, 2 views today