sql

All things SQL and JDBC...
g 2020-04-20T20:22:14.306700Z

i’ve been hitting my head against a wall trying to figure some odd db behavior, perhaps someone could give me a hand: i have two identical insert statements with 5000 rows. executed individually they are pretty quick (ms). executed under a dorun pmap , one of them will timeout at the default of 50 s, and the other will take 4 minutes or more. what’s going on here?

g 2020-04-20T20:22:30.306900Z

(timeout = lock wait timeout)

g 2020-04-20T20:23:31.307500Z

here’s a description of the table:

+-------+------------+------+-----+---------+----------------+
| Field | Type       | Null | Key | Default | Extra          |
+-------+------------+------+-----+---------+----------------+
| id    | bigint(20) | NO   | PRI | NULL    | auto_increment |
| x     | binary(32) | NO   | UNI | NULL    |                |
| y     | mediumblob | YES  |     | NULL    |                |
+-------+------------+------+-----+---------+----------------+
i’m inserting x-y pairs

g 2020-04-20T20:24:15.307900Z

alternatively what are some metrics i could have a look at to figure out the problem?

g 2020-04-20T20:25:14.308200Z

sorry, critical point, both queries are the same. namely have the same rows

g 2020-04-20T20:29:20.308400Z

(and the statement is insert ignore )

seancorfield 2020-04-20T20:40:56.309700Z

Well, pmap is almost never the right solution for concurrency... but... are you trying to use a single connection across both inserts? Are you trying to do them inside a transaction?

g 2020-04-20T20:41:45.310800Z

no, they are separate jdbc execute statements

g 2020-04-20T20:42:06.311500Z

it’s not so much that i need pmap as i’m trying to replicate a db-layer bug that i have elsewhere, and this setup is exhibiting the same behaviour

seancorfield 2020-04-20T20:42:16.311800Z

And what type of db spec / datasource are you passing in the execute?

seancorfield 2020-04-20T20:42:57.312600Z

Also, which JDBC library are you using? clojure.java.jdbc or next.jdbc? And which database is this?

g 2020-04-20T20:43:03.312900Z

{
      :host "localhost"
      :dbtype "mysql"
      :password "test"
      :rewriteBatchedStatements true
      :user "user"
      :allowPublicKeyRetrieval true
      :useSSL false
      :useUnicode true
      :characterEncoding "UTF-8" }

g 2020-04-20T20:43:43.313400Z

clojure.java.jdbc, mysql | innodb_version | 5.7.29 |

g 2020-04-20T20:44:58.314800Z

(jdbc/execute! conn (concat ["INSERT IGNORE INTO table (x,y) VALUES (?,?)"] rows) {:transaction? false :multi? true})

seancorfield 2020-04-20T20:45:21.314900Z

OK. So each execute call will stand up a new connection to the DB. You'll definitely get contention in mysql from attempting two large locking inserts at the same time.

seancorfield 2020-04-20T20:46:12.315800Z

Oh, so you're avoiding a transaction around each SQL op and you are trying a single batch operation with 5,000 sets of parameters in the batch...

g 2020-04-20T20:46:31.316100Z

yeah

g 2020-04-20T20:46:41.316600Z

no good?

seancorfield 2020-04-20T20:46:56.317100Z

rewriteBatchedStatements isn't going to help you here FYI

dpsutton 2020-04-20T20:47:12.317500Z

auto_increment . could it be the key state? would using UUID make it easier?

g 2020-04-20T20:47:21.317900Z

ah ok - i’ll revisit that. may be an artifact of an earlier version

g 2020-04-20T20:47:40.318400Z

hmmm, interesting thought

seancorfield 2020-04-20T20:47:49.318700Z

MySQL doesn't handle concurrent large inserts on a single table very well in my experience. You tend to get deadlocks.

seancorfield 2020-04-20T20:48:36.319900Z

Are you wrapping the (two) calls with your own transaction? If not, why are you trying avoid c.j.j setting up a transaction?

g 2020-04-20T20:54:06.320900Z

i believe it was changed to try and avoid deadlock issues, but it seems it hasn’t achieved that

g 2020-04-20T20:59:26.322200Z

alright, so perhaps the solution is to re-enable that and write a bit more stuff on top to handle ‘concurrency’

g 2020-04-20T20:59:46.322700Z

it does seem that enabling it at least fails fast with a deadlock error instead of spinning for a minute