[barrkel]

I'm not talking about locking the table. I'm talking about locking a table resource.

https://dev.mysql.com/doc/refman/5.7/en/innodb-locks-set.htm...

Quote:

"While initializing a previously specified AUTO_INCREMENT column on a table, InnoDB sets an exclusive lock on the end of the index associated with the AUTO_INCREMENT column. In accessing the auto-increment counter, InnoDB uses a specific AUTO-INC table lock mode where the lock lasts only to the end of the current SQL statement, not to the end of the entire transaction. Other sessions cannot insert into the table while the AUTO-INC table lock is held; see Section 14.5.2, “InnoDB Transaction Model”. "

If you have a long-running statement, it can block concurrent transactions. I've seen it specifically with 'load data infile', IIRC. We had to go through some painful migrations to remove auto-increment on some fairly large tables when we started seeing this.

[jeltz]

That is an odd design. PostgreSQL only holds the lock long enough to increment a counter in memory, and every 32th time also write a write-ahead log memory in RAM. I can't see why one would need to lock the counter for the duration of the query.

[chrisconroy]

Gotcha. Thanks for the clarification!

Yes, even if you're using normal insert statements, it's good to limit the size of any one transaction to the extent you can for a host of reasons!