Insert failed on transaction

General
#1

hi.

I often fail, while insert with transaction, and i got this error:
cockroach 1.0.3 installed from brew on macbook. just insert 2 rows every second.

pq: restart transaction: HandledRetryableTxnError: TransactionRetryError: retry txn “sql txn” id=bf0322b6 key=/Table/317/1/264328066611281921/0 rw=true pri=0.01383815 iso=SERIALIZABLE stat=PENDING epo=0 ts=1500736924.182353200,1 orig=1500736924.155020158,0 max=1500736924.155020158,0 wto=false rop=false seq=2

#2

Could you describe your workload?

#3

Just 1 goroutine, and insert 2 rows per second. run 1 node cockroachdb on my MacBook .

I am using golang and pq, but in my data layer, all operations with a transaction.
the same data layer code, used other databases(pgsql,mysql,oracle), has no problem.

I’m going to change the database to postgresql, for test this problem.

sorry for my bad english, it’s not my mother language.

#4

hi,

i just changed database to postgresql, using same code, it’s all right, no this problem.
so, maybe this is cockroach’s problem, i don’t know, maybe…

same code on postgresql it’s ok…
on cockroachdb:

pq: restart transaction: HandledRetryableTxnError: TransactionRetryError: retry txn “sql txn” id=31f92055 key=/Table/317/1/264621399404642305/0 rw=true pri=0.01119883 iso=SERIALIZABLE stat=PENDING epo=0 ts=1500826442.285114624,1 orig=1500826442.249170220,0 max=1500826442.249170220,0 wto=false rop=false seq=2

#5

@jorise, I’m afraid we won’t be able to debug this problem without more information. Could you provide us with the exact schema and queries you’re running? (If your schema/queries are sensitive, a sanitized version that exhibits the same problem would be great.)

The only thing that jumps out at me from the information you’ve posted so far is that you have quite a few tables in your database—or, at least, you’ve created and dropped quite a few tables.

#6

I’ve written a test code on https://github.com/jorise7/cockroach-test

test method is:

  1. run test.go
  2. open your browser and visit http://127.0.0.1:8080/numbers
  3. keep refresh (hold down Command+R on mac, F5 on windows)
  4. then you will see error message in the console

Both cockroach and PostgreSQL can run.
on postgresql it’s ok, but chcoroach, i got this error:

pq: restart transaction: HandledRetryableTxnError: TransactionRetryError: retry txn “sql txn” id=1641ac4b key=/Table/322/1/264740306979487745/0 rw=true pri=0.01068246 iso=SERIALIZABLE stat=PENDING epo=0 ts=1500862730.007064217,1 orig=1500862730.006540229,0 max=1500862730.006540229,0 wto=false rop=false seq=2

I think I know what the problem is, because in my data driver, all operations with a transaction.
and I need time to the rewrite data driver to confirm it.
but on postgresql is ok, so …

Thank you so provide excellent cockroach and response.
I like cockroachdb very much, I’m going to use it in production.

#7

Your program is doing two things concurrently:

  • inserting rows in the background (every 1-2 seconds),
  • responding to HTTP requests with a SELECT query.

Because CockroachDB uses optimistic concurrency control, there is no locking (like in Postgres). Because your SELECT queries access the same rows that are being inserted, conflicts are unavoidable.

The solution to your problem is to ensure both your inserts and queries retry when they encounter a retry error. This is explained here: https://www.cockroachlabs.com/docs/stable/transactions.html#client-side-transaction-retries