Threads being blocked when trying to access SQL Server

Question

I'm having a rather strange problem. For some time now, I've been developing a project on SQL server and everything has been going fine.

Yesterday, I was given a new .bak file to restore, containing new data for the project, and after doing that, I noticed that certain operations that required gathering huge data weren't functioning when initiated, like they were stuck.

I checked the thread dump in my WEBLOGIC server to see what was happening, and I noticed this:

"[STANDBY] ExecuteThread: '6' for queue: 'weblogic.kernel.Default (self-tuning)'" Id=91 WAITING on weblogic.work.ExecuteThread@2a05ef56

                at java.lang.Object.wait(Native Method)

                -  waiting on weblogic.work.ExecuteThread@2a05ef56

                at java.lang.Object.wait(Object.java:502)

                at weblogic.work.ExecuteThread.waitForRequest(ExecuteThread.java:247)

                at weblogic.work.ExecuteThread.run(ExecuteThread.java:281)

            "Helper-Thread-1" Id=90 RUNNABLE (in native)

                at java.net.SocketInputStream.socketRead0(Native Method)

                at java.net.SocketInputStream.socketRead(SocketInputStream.java:116)

                at java.net.SocketInputStream.read(SocketInputStream.java:170)

                at java.net.SocketInputStream.read(SocketInputStream.java:141)

                at com.microsoft.sqlserver.jdbc.TDSChannel.read(IOBuffer.java:1782)

                at com.microsoft.sqlserver.jdbc.TDSReader.readPacket(IOBuffer.java:4838)

                -  locked com.microsoft.sqlserver.jdbc.TDSReader@3350842b

                at com.microsoft.sqlserver.jdbc.TDSCommand.startResponse(IOBuffer.java:6150)

                at com.microsoft.sqlserver.jdbc.SQLServerPreparedStatement.doExecutePreparedStatement(SQLServerPreparedStatement.java:402)

                at com.microsoft.sqlserver.jdbc.SQLServerPreparedStatement$PrepStmtExecCmd.doExecute(SQLServerPreparedStatement.java:350)

                at com.microsoft.sqlserver.jdbc.TDSCommand.execute(IOBuffer.java:5696)

                at com.microsoft.sqlserver.jdbc.SQLServerConnection.executeCommand(SQLServerConnection.java:1716)

                -  locked java.lang.Object@53ca4084

                at com.microsoft.sqlserver.jdbc.SQLServerStatement.executeCommand(SQLServerStatement.java:180)

                at com.microsoft.sqlserver.jdbc.SQLServerStatement.executeStatement(SQLServerStatement.java:155)

                at com.microsoft.sqlserver.jdbc.SQLServerPreparedStatement.executeQuery(SQLServerPreparedStatement.java:285)

                at weblogic.jdbc.wrapper.PreparedStatement.executeQuery(PreparedStatement.java:141)

                at org.hibernate.engine.jdbc.internal.ResultSetReturnImpl.extract(ResultSetReturnImpl.java:82)

                at org.hibernate.loader.Loader.getResultSet(Loader.java:2066)

                at org.hibernate.loader.Loader.executeQueryStatement(Loader.java:1863)

                at org.hibernate.loader.Loader.executeQueryStatement(Loader.java:1839)

                at org.hibernate.loader.Loader.doQuery(Loader.java:910)

                at org.hibernate.loader.Loader.doQueryAndInitializeNonLazyCollections(Loader.java:355)

                at org.hibernate.loader.Loader.doList(Loader.java:2554)

                at org.hibernate.loader.Loader.doList(Loader.java:2540)

                at org.hibernate.loader.Loader.listIgnoreQueryCache(Loader.java:2370)

                at org.hibernate.loader.Loader.list(Loader.java:2365)

                at org.hibernate.loader.criteria.CriteriaLoader.list(CriteriaLoader.java:126)

                at org.hibernate.internal.SessionImpl.list(SessionImpl.java:1718)

                at org.hibernate.internal.CriteriaImpl.list(CriteriaImpl.java:380)

                at org.greenpole.hibernate.query.impl.ClientCompanyComponentQueryImpl.createShareBonusHolderRecords_ThreadRunner(ClientCompanyComponentQueryImpl.java:9037)

                at org.greenpole.helper_thread.HelperController_Declaration_Addition.runShareBonusDeclaration(HelperController_Declaration_Addition.java:537)

                at org.greenpole.helper_thread.HelperController_Declaration_Addition.run(HelperController_Declaration_Addition.java:58)

                at java.util.concurrent.ThreadPoolExecutor.runWorker(ThreadPoolExecutor.java:1142)

                at java.util.concurrent.ThreadPoolExecutor$Worker.run(ThreadPoolExecutor.java:617)

                at java.lang.Thread.run(Thread.java:745)

                Number of locked synchronizers = 1

                - java.util.concurrent.ThreadPoolExecutor$Worker@40705003

When I restore the old data from my last backup, everything works fine. No locks. Nothing like that. However with this new data, these heavy processes aren't working.

The application operation goes thus: the Weblogic server receives a request to declare a share bonus, which involves querying information of share holders from the database.

When the request comes through, the application spawns a thread called "Helper-Thread" which handles that operation. The Helper-Thread - by way of Hibernate - gets the IDs of all shareholder records from the database (as many as 300,000) and then goes on to work on each of them.

Now, this operation has been working very well in the past, until recently when we restored our database with new migrated data. And suddenly, the Helper-Thread appears to be stuck, like it's not being allowed access to the necessary table yet.

I'm confused. How do I get around this locking issue? I need to work with this new set of data. Could it be that I didn't carry out the restoration process properly?

Answer 1

You can Isolate the issue if it is due to database by following below steps..

How SQL Server executes a query:
When a request arrives to SQL server ,Task is created and worker thread picks up from task qeueue,worker thread once the task is completed,will return to worker pool..

In the above scenario,worker thread can be blocked by some other request or wait for some request like IO..

Now that you understood ,how things work at high level,Lets see if you are facing issues with threadpool shortage..

Steps to check if SQL server Ran out of threads..

1.Check total Number of worker threads..

  select max_workers_count from sys.dm_os_sys_info

2.Now check if you are facing threadpool shortage..

select * from sys.dm_os_waiting_tasks where wait_type ='threadpool'

if you see any sessions with waittype "ThreadPool" in above query,this means some of the tasks are waiting to be picked up..

3.Now Lets see locking,blocking, current threads status...

--to see what current queries are doing..
---if if you see so many blocked requests,then you should start investigating from there

select status,command,txt.text,rqst.blocking_session_id,rqst.last_wait_type,rqst.wait_type
 from
sys.dm_exec_requests rqst
cross apply
sys.dm_exec_sql_text(rqst.sql_handle) txt

--to see worker threads status..

select rqst.session_id,rqst.status,tsk.task_state,
wrk.last_wait_type as workerlastwaittype,wrk.status as workeralaststatus,wrk.state as currentstateofworker
 from
sys.dm_exec_sessions rqst
inner join
sys.dm_os_tasks tsk
on tsk.session_id=rqst.session_id
inner join
sys.dm_os_workers wrk
on wrk.task_address=tsk.task_address
order by rqst.session_id

this is the troubleshooting process i would follow to isolate to see if this problem is with Database..

Not related to the way you check worker status in Hibernate,In IIS you can see what exactly is each thread doing Enabling FREB ..May be something of that sort might exist in hibernate which may help you to know what exactly is the thread doing

some threads which might be helpfull :
Threadpool Wait
https://msdn.microsoft.com/en-us/library/ms187024.aspx