При выполнении длительного запроса к БД из одного потока, блокируется параллельный запрос к БД из другого потока
СУБД - MS SQL SERVER
Возникла ситуация, когда я логинюсь к БД, то длительный запрос, снова тормозит первый поток.
Не могу понять по какой причине, вероятно ему не нравится, что я некорректно добавил подключение к серверу.
Примерно вот так получилось, только здесь не добавил запросы в потоки:
import threading
import time
from ui import *
import sys
from PyQt5.QtGui import QIcon
from PyQt5 import QtCore, QtGui, QtWidgets
from PyQt5.QtCore import *
from PyQt5.uic import loadUi
from PyQt5.QtWidgets import QDialog, QApplication
import pyodbc
from redminelib import Redmine
from dateutil.relativedelta import relativedelta
from io import BytesIO
from PyQt5.QtWidgets import *
import webbrowser
class Connect:
def __init__(self):
super(Connect, self).__init__()
self.ui = Ui_MainWindow()
self.server = None
self.password = None
self.username = None
def server_conn(self):
self.server = self.ui.connwin.text()
if self.ui.connwin.text() == '':
self.ui.connwin.setPlaceholderText('Empty')
return
self.username = self.ui.ent_login.text()
if self.ui.ent_login.text() == '':
self.ui.ent_login.setPlaceholderText('Empty')
return
self.password = self.ui.pass_word.text()
if self.ui.pass_word.text() == '':
self.ui.pass_word.setPlaceholderText('Empty')
return
try:
"""Создается подключение к серверу"""
self.conn = conn = pyodbc.connect(
'DRIVER={ODBC Driver 17 for SQL Server};SERVER=' + self.server + ';DATABASE=' + self.database + ';UID='
+ self.username + ';PWD=' + self.password, autocommit=True) # запускает соединение с SQL-сервером
self.cursor = cursor = conn.cursor() # создается cursor для соединения
print('connect succcesfull')
self.ui.onlineligth.setStyleSheet('background-color: green; '
'border-radius:5px;'
'font-size: 16px;')
self.ui.statusonline.setText('Online')
# If connection is not successful
except Exception:
print("Can't connect to database")
self.ui.statusonline.setText('Offline')
self.ui.onlineligth.setStyleSheet('background-color: red;'
'border-radius:5px;'
'font-size: 16px;')
return
class MyThread(QtCore.QObject, Connect):
mysignal1 = QtCore.pyqtSignal(str, object) # !!!
def __init__(self, parent=None):
QtCore.QObject.__init__(self, parent)
self._stopped = True
self.ui = Ui_MainWindow()
def run(self):
self._stopped = False
print('start')
self.change_x()
def change_x(self):
while not self._stopped:
print('My Thread работает')
application.cursor.execute(""" select TOP 1
case when j.name like '%index%' then j.name
when j.name like '%update%' then j.name
when j.name like '%rchive%' then j.name
when j.name like '%HS_check%' then j.name else 'No heavy jobs'
end jobname
from sys.dm_exec_requests r
inner join sys.dm_exec_sessions s
on r.session_id = s.session_id
cross apply sys.dm_exec_sql_text ( sql_handle ) t
inner join msdb.dbo.sysjobs j
on s.program_name like '%' + master.dbo.fn_varbintohexstr( j.job_id ) + '%' """)
records = application.cursor.fetchall()
self.mysignal1.emit('heavy_job', records)
QtCore.QThread.msleep(1000*1)
application.cursor.execute(""" SELECT
CAST((
MAX(CASE WHEN counter_name = 'CPU usage %' THEN t.cntr_value * 1. END) /
MAX(CASE WHEN counter_name = 'CPU usage % base' THEN t.cntr_value END)
) * 100 as numeric(15,0)) AS cpu_sql
FROM (
SELECT TOP(2) cntr_value, counter_name
FROM sys.dm_os_performance_counters
WHERE counter_name IN ('CPU usage %', 'CPU usage % base')
AND instance_name = 'default'
) t """)
records = application.cursor.fetchall()
records = 'тработал запрос для cpu_sql()'
self.mysignal1.emit('cpu_sql', records)
def stop(self):
self._stopped = True # Видимо данный метод является остановкой для выполнения потока
class MyThread4(QtCore.QObject, Connect):
mysignal = QtCore.pyqtSignal(object) # !!!
def __init__(self, parent=None):
QtCore.QObject.__init__(self, parent)
self._stopped = True
self.ui = Ui_MainWindow()
def run(self):
print('start Mythread2')
count = 0
self._stopped = False
# + ... тут ваш длительный запрос !!!
application.cursor.execute("""SELECT top 100
CASE WHEN indexstats.avg_fragmentation_in_percent>30 THEN ' REBUILD '
WHEN indexstats.avg_fragmentation_in_percent>=5 THEN 'REORGANIZE'
end [Fix],
ind.type_desc as Index_Type,
OBJECT_NAME(ind.OBJECT_ID) AS TableName,
ind.name AS IndexName,
str(indexstats.avg_fragmentation_in_percent) as fragmentation,
'ALTER INDEX ' + QUOTENAME(ind.name) + ' ON ' +QUOTENAME(object_name(ind.object_id)) +
CASE WHEN indexstats.avg_fragmentation_in_percent>30 THEN ' REBUILD '
WHEN indexstats.avg_fragmentation_in_percent>=5 THEN 'REORGANIZE'
ELSE NULL END as [SQLQuery]
-- if <5 not required, so no query needed
FROM sys.dm_db_index_physical_stats(DB_ID(), NULL, NULL, NULL, NULL) indexstats
INNER JOIN sys.indexes ind ON ind.object_id = indexstats.object_id
AND ind.index_id = indexstats.index_id
WHERE
indexstats.avg_fragmentation_in_percent >15 and
ind.Name is not null
ORDER BY TableName asc""")
records = application.cursor.fetchall()
self.mysignal.emit(records)
def stop(self):
self._stopped = True
#######################################################################
class mywindow(QtWidgets.QMainWindow, Connect):
def __init__(self):
super(mywindow, self).__init__()
self.ui = Ui_MainWindow()
self.ui.setupUi(self)
self.tran_text = None
self.tran = None
self.task_id = None
self.conn = None
self.cursor = None
self.database = None
self.ui.connwin.setPlaceholderText('Введите адрес сервера')
self.ui.ent_login.setPlaceholderText('Введите логин')
self.ui.pass_word.setPlaceholderText('Введите пароль')
self.setWindowFlags(Qt.FramelessWindowHint)
self.ui.butt_conn.clicked.connect(self.server_conn)
self.ui.startcheckindex.clicked.connect(self.handleButton)
self.ui.start_check.clicked.connect(self.butt_connButton)
self.thread1 = QtCore.QThread(self) # +++
self.worker1 = MyThread()
self.worker1.moveToThread(self.thread1)
self.worker1.mysignal1.connect(self.worker1_mysignal1) # !!!
# self.butt_conn.clicked.connect(self.worker1.change_x)
# self.thread1.start()
self.thread1.started.connect(self.worker1.run) # !!!
self.thread = QtCore.QThread(self) # +++
self.worker = MyThread4()
self.worker.moveToThread(self.thread)
self.worker.mysignal.connect(self.index_frag_inf) # !!!
# self.startcheckindex.clicked.connect(self.worker.second_work)
# self.thread.start()
self.thread.started.connect(self.worker.run) # !!!
self.ui.tableWidget.setHorizontalHeaderLabels(
('Fix',
'Index Type',
'TableName',
'IndexName',
'fragmentation %',
'SQLQuery'
)
)
self.ui.tableWidget.setColumnWidth(0, 100)
self.ui.tableWidget.setColumnWidth(1, 100)
self.ui.tableWidget.setColumnWidth(2, 200)
self.ui.tableWidget.setColumnWidth(3, 300)
self.ui.tableWidget.setColumnWidth(4, 80)
self.ui.tableWidget.setColumnWidth(5, 300)
self.ui.Monitoractivity.clicked.connect(lambda: self.ui.stackedWidget.setCurrentWidget(self.ui.dashboard))
self.ui.indexfragment.clicked.connect(lambda: self.ui.stackedWidget.setCurrentWidget(self.ui.indexfrag))
def handleButton(self):
if self.thread.isRunning():
self.worker.stop()
else:
self.thread.start()
def butt_connButton(self):
if self.thread1.isRunning():
self.worker1.stop()
self.thread1.quit()
else:
self.thread1.start()
def worker1_mysignal1(self, text, records): # <----
if text == 'heavy_job':
# self.ui.heavyjob.setText(str(records))
self.ui.heavyjob.setText(str(records))
elif text == 'cpu_sql':
# self.ui.cpupercent.setText(str(records))
self.ui.cpupercent.setText(str(records))
def index_frag_inf(self, records): # <----
self.thread.quit()
self.ui.tableWidget.clearContents()
# self.ui.tableWidget.setRowCount(100) void QTableWidget::insertRow(int row)
# tablerow = 0
self.ui.tableWidget.setRowCount(100)
tablerow = 0
for index, record in enumerate(records):
self.ui.tableWidget.setItem(index, 0, QtWidgets.QTableWidgetItem(record[0]))
self.ui.tableWidget.setItem(index, 1, QtWidgets.QTableWidgetItem(record[1]))
self.ui.tableWidget.setItem(index, 2, QtWidgets.QTableWidgetItem(record[2]))
self.ui.tableWidget.setItem(index, 3, QtWidgets.QTableWidgetItem(record[3]))
self.ui.tableWidget.setItem(index, 4, QtWidgets.QTableWidgetItem(record[4]))
self.ui.tableWidget.setItem(index, 5, QtWidgets.QTableWidgetItem(record[5]))
tablerow += 1
if __name__ == '__main__':
app = QtWidgets.QApplication([])
application = mywindow()
application.show()
application.setWindowTitle('Console')
sys.exit(app.exec())