question Re: You can't execute queries until the end of the 'atomic' block in Support Questions

You can't execute queries until the end of the 'atomic' block

ChineduLB — Tue, 06 Aug 2019 22:15:07 GMT

Hi,

I am getting above error from Hive while trying to query a table.

This is coincidentally during an insert operation on the table...

does it mean I cant access the table while a hive insert operation is ongoing...?

table contains lots of rows partitioned by two columns

Re: You can't execute queries until the end of the 'atomic' block

EricL — Fri, 09 Aug 2019 08:53:24 GMT

Hi,

I don't see any errors posted in the post, can you please share again?

In general, Hive have locking mechanism enabled, so when INSERT is happening on a table, an exclusive lock will be place on the table and any query tries to run against the same table will have to wait, and there is a timeout to control how long the wait will be, and the query will fail with error if timeout exceeded, otherwise it will just keep waiting until lock is released.

So to understand more on the issue, I need to see what error was reported.

Cheers
Eric

Re: You can't execute queries until the end of the 'atomic' block

syedshakir — Sun, 01 Mar 2020 09:51:06 GMT

Hi ,

im facing same issue ,

attached is the error screenshot

Re: You can't execute queries until the end of the 'atomic' block

kunzhao — Fri, 25 Dec 2020 08:11:56 GMT

I am facing the same error when executing sql query through hue server。Now I have no idea to solve the error。

Please。

Re: You can't execute queries until the end of the 'atomic' block

syedshakir — Mon, 19 Jul 2021 11:23:39 GMT

Hi,

Im also facing same issue ,

increased connections numbers to HUE DB, still issue exits.

Thanks,

Re: You can't execute queries until the end of the 'atomic' block

asish — Tue, 20 Jul 2021 08:06:56 GMT

Can you check "show locks" if the table is acquired EXCLUSIVE lock ?

Re: You can't execute queries until the end of the 'atomic' block

syedshakir — Mon, 26 Jul 2021 18:42:34 GMT

Hi,

No its not acquiring lock , result was empty set in hue DB.

Thanks,

syed

Re: You can't execute queries until the end of the 'atomic' block

asish — Tue, 03 Aug 2021 10:36:47 GMT

DOes the same issue happen when you run via beeline ? I researched ,I found this occurs only in hue.

Re: You can't execute queries until the end of the 'atomic' block

syedshakir — Thu, 05 Aug 2021 06:34:01 GMT

Hi,

yes its happening at hue browser only

Re: You can't execute queries until the end of the 'atomic' block

Sean464 — Sun, 08 Aug 2021 02:41:09 GMT

Hue is unable to handle the slow responses from Hive servers and hence the error. This happens when Hive is unable to return the query handle to Hue in a timely manner. So, the ideal solution here would be to investigate Hive slowness and not Hue.

Hue relies on server_conn_timeout parameter in its configuration (default 120s), after which the active sessions with hiveserver2 is teared down.

If Hue is using MySQL as the underlying DB, then we can tweak the innodb-lock-wait-timeout to be greater than the server_conn_timeout in Hue's configuration, that way we get fail-fast system and the locked tables are released. Please note that this is only a workaround until the cause of Hive performance issue is found and remediated.

Re: You can't execute queries until the end of the 'atomic' block

asish — Tue, 24 Aug 2021 09:06:15 GMT

Is hive generating slow response? Is the query slow??

Re: You can't execute queries until the end of the 'atomic' block

VidyaSargur — Mon, 30 Aug 2021 05:16:44 GMT

@syedshakir, Has any of the replies helped resolve your issue? If so, please mark the appropriate reply as the solution, as it will make it easier for others to find the answer in the future. If you are still experiencing the issue, can you provide the information @asish has requested?

Re: You can't execute queries until the end of the 'atomic' block

syedshakir — Mon, 30 Aug 2021 06:56:10 GMT

Hi,

Issue resolved by increasing number of conection and session timeout as a workaround.

Actually need to use dedicated disk for DB's.

Thanks,

Syed

Re: You can't execute queries until the end of the 'atomic' block

VidyaSargur — Mon, 30 Aug 2021 08:01:11 GMT

Thanks for the confirmation @syedshakir. Much appreciated.