SNOW-1983343: Deadlock during result pre-fetch

Python version

3.11

Operating system and processor architecture

Linux-6.5.0-1024-aws-x86_64-with-glibc2.29

Installed packages

snowflake-connector-python==3.14.0

What did you do?

We are experiencing a deadlock. Here's the relevant backtraces.

Holding the lock, waiting for thread pool to close during GC:

Thread 34 (Thread 0x781d0b5d2700 (LWP 422)):
Traceback (most recent call first):
  File "/usr/lib/python3.11/threading.py", line 1139, in _wait_for_tstate_lock
    if lock.acquire(block, timeout):
  File "/usr/lib/python3.11/threading.py", line 1119, in join
    self._wait_for_tstate_lock()
  File "/usr/lib/python3.11/concurrent/futures/thread.py", line 235, in shutdown
    t.join()
  File "/usr/lib/python3.11/concurrent/futures/_base.py", line 647, in __exit__
    self.shutdown(wait=True)
  File "/venv/dbt-latest/lib/python3.11/site-packages/snowflake/connector/result_set.py", line ?, in result_set_iterator
    (failed to get frame line number)
  File "/venv/dbt-latest/lib/python3.11/site-packages/snowflake/connector/cursor.py", line 1594, in reset
    self._result = None
  File "/venv/dbt-latest/lib/python3.11/site-packages/snowflake/connector/cursor.py", line 585, in close
    self.reset(closing=True)
  File "/venv/dbt-latest/lib/python3.11/site-packages/snowflake/connector/cursor.py", line 440, in __del__
    self.close()
  Garbage-collecting
  File "/venv/dbt-latest/lib/python3.11/site-packages/asn1crypto/core.py", line 3928, in _parse_children
    child = parts + (field_spec, field_params)
  File "/venv/dbt-latest/lib/python3.11/site-packages/asn1crypto/core.py", line 3508, in __getitem__
    self._parse_children()
  File "/venv/dbt-latest/lib/python3.11/site-packages/asn1crypto/x509.py", line 2547, in subject
    return self['tbs_certificate']['subject']
  File "/venv/dbt-latest/lib/python3.11/site-packages/snowflake/connector/ocsp_asn1crypto.py", line 100, in read_cert_bundle
    storage[crt.subject.sha256] = crt
  File "/venv/dbt-latest/lib/python3.11/site-packages/snowflake/connector/ocsp_snowflake.py", line 1467, in _lazy_read_ca_bundle
    self.read_cert_bundle(certifi.where())
  File "/venv/dbt-latest/lib/python3.11/site-packages/snowflake/connector/ocsp_asn1crypto.py", line 428, in create_pair_issuer_subject
    self._lazy_read_ca_bundle()
  File "/venv/dbt-latest/lib/python3.11/site-packages/snowflake/connector/ocsp_asn1crypto.py", line 411, in extract_certificate_chain
    return self.create_pair_issuer_subject(cert_map)
  File "/venv/dbt-latest/lib/python3.11/site-packages/snowflake/connector/ocsp_snowflake.py", line 1145, in validate
    cert_data = self.extract_certificate_chain(connection)
  File "/venv/dbt-latest/lib/python3.11/site-packages/snowflake/connector/ssl_wrap_socket.py", line 91, in ssl_wrap_socket_with_ocsp
    ).validate(server_hostname, ret.connection)
  File "/venv/dbt-latest/lib/python3.11/site-packages/snowflake/connector/vendored/urllib3/connection.py", line 419, in connect
    self.sock = ssl_wrap_socket(
  File "/venv/dbt-latest/lib/python3.11/site-packages/snowflake/connector/vendored/urllib3/connectionpool.py", line 1058, in _validate_conn
    conn.connect()
  File "/venv/dbt-latest/lib/python3.11/site-packages/snowflake/connector/vendored/urllib3/connectionpool.py", line 404, in _make_request
    self._validate_conn(conn)
  File "/venv/dbt-latest/lib/python3.11/site-packages/snowflake/connector/vendored/urllib3/connectionpool.py", line 715, in urlopen
    httplib_response = self._make_request(
  File "/venv/dbt-latest/lib/python3.11/site-packages/snowflake/connector/vendored/requests/adapters.py", line 486, in send
    resp = conn.urlopen(
  File "/venv/dbt-latest/lib/python3.11/site-packages/snowflake/connector/vendored/requests/sessions.py", line 703, in send
    r = adapter.send(request, **kwargs)
  File "/venv/dbt-latest/lib/python3.11/site-packages/snowflake/connector/vendored/requests/sessions.py", line 589, in request
    resp = self.send(prep, **send_kwargs)
  File "/venv/dbt-latest/lib/python3.11/site-packages/snowflake/connector/result_batch.py", line 332, in _download
    response = session.request("get", **request_data)
  File "/venv/dbt-latest/lib/python3.11/site-packages/snowflake/connector/result_batch.py", line 673, in _create_iter
    response = self._download(connection=connection)
  File "/venv/dbt-latest/lib/python3.11/site-packages/snowflake/connector/result_batch.py", line 745, in create_iter
    return self._create_iter(iter_unit=iter_unit, connection=connection)
  File "/usr/lib/python3.11/concurrent/futures/thread.py", line 58, in run
    result = self.fn(*self.args, **self.kwargs)
  File "/usr/lib/python3.11/concurrent/futures/thread.py", line 83, in _worker
    work_item.run()
  File "/usr/lib/python3.11/threading.py", line 982, in run
    self._target(*self._args, **self._kwargs)
  File "/usr/lib/python3.11/threading.py", line 1045, in _bootstrap_inner
    self.run()
  File "/usr/lib/python3.11/threading.py", line 1002, in _bootstrap
    self._bootstrap_inner()

3 other threads are waiting for lock and not finishing:

Traceback (most recent call first):
  <built-in method __enter__ of _thread.RLock object at remote 0x781d0c72eec0>
  File "/venv/dbt-latest/lib/python3.11/site-packages/snowflake/connector/ocsp_snowflake.py", line 1418, in _lazy_read_ca_bundle
    with SnowflakeOCSP.ROOT_CERTIFICATES_DICT_LOCK:
  File "/venv/dbt-latest/lib/python3.11/site-packages/snowflake/connector/ocsp_asn1crypto.py", line 428, in create_pair_issuer_subject
    self._lazy_read_ca_bundle()
  File "/venv/dbt-latest/lib/python3.11/site-packages/snowflake/connector/ocsp_asn1crypto.py", line 411, in extract_certificate_chain
    return self.create_pair_issuer_subject(cert_map)
  File "/venv/dbt-latest/lib/python3.11/site-packages/snowflake/connector/ocsp_snowflake.py", line 1145, in validate
    cert_data = self.extract_certificate_chain(connection)
  File "/venv/dbt-latest/lib/python3.11/site-packages/snowflake/connector/ssl_wrap_socket.py", line 91, in ssl_wrap_socket_with_ocsp
    ).validate(server_hostname, ret.connection)
  File "/venv/dbt-latest/lib/python3.11/site-packages/snowflake/connector/vendored/urllib3/connection.py", line 419, in connect
    self.sock = ssl_wrap_socket(
  File "/venv/dbt-latest/lib/python3.11/site-packages/snowflake/connector/vendored/urllib3/connectionpool.py", line 1058, in _validate_conn
    conn.connect()
  File "/venv/dbt-latest/lib/python3.11/site-packages/snowflake/connector/vendored/urllib3/connectionpool.py", line 404, in _make_request
    self._validate_conn(conn)
  File "/venv/dbt-latest/lib/python3.11/site-packages/snowflake/connector/vendored/urllib3/connectionpool.py", line 715, in urlopen
    httplib_response = self._make_request(
  File "/venv/dbt-latest/lib/python3.11/site-packages/snowflake/connector/vendored/requests/adapters.py", line 486, in send
    resp = conn.urlopen(
  File "/venv/dbt-latest/lib/python3.11/site-packages/snowflake/connector/vendored/requests/sessions.py", line 703, in send
    r = adapter.send(request, **kwargs)
  File "/venv/dbt-latest/lib/python3.11/site-packages/snowflake/connector/vendored/requests/sessions.py", line 589, in request
    resp = self.send(prep, **send_kwargs)
  File "/venv/dbt-latest/lib/python3.11/site-packages/snowflake/connector/result_batch.py", line 332, in _download
    response = session.request("get", **request_data)
  File "/venv/dbt-latest/lib/python3.11/site-packages/snowflake/connector/result_batch.py", line 673, in _create_iter
    response = self._download(connection=connection)
  File "/venv/dbt-latest/lib/python3.11/site-packages/snowflake/connector/result_batch.py", line 745, in create_iter
    return self._create_iter(iter_unit=iter_unit, connection=connection)
  File "/usr/lib/python3.11/concurrent/futures/thread.py", line 58, in run
    result = self.fn(*self.args, **self.kwargs)
  File "/usr/lib/python3.11/concurrent/futures/thread.py", line 83, in _worker
    work_item.run()
  File "/usr/lib/python3.11/threading.py", line 982, in run
    self._target(*self._args, **self._kwargs)
  File "/usr/lib/python3.11/threading.py", line 1045, in _bootstrap_inner
    self.run()
  File "/usr/lib/python3.11/threading.py", line 1002, in _bootstrap
    self._bootstrap_inner()

Due to this, our application does not exit, because it is waiting for the ThreadPoolExecutor to be closed.

What did you expect to see?

I expected the snowflake connector to allow our application to exit.

Can you set logging to DEBUG and collect the logs?

I can do this if you need it, but I think the backtrace should give you a good amount of information to go on. Let me know if you have any questions.