Does using set_transient() function can lead to MySQL problems?
No, although if you used it so much that the MySQL server ran out of disk space, then yes it could cause issues. However, you would know that’s the case by checking the disk space, and if you manage to do this then I would consider that something is very wrong with your usage of transients.
The same is true of any table though, this isn’t a transients issue, it’s a storing so much stuff that things are clearly going wrong. Like when a large drive is filled by a log, or a program enters an infinite loop.
What will happen when I reach the last option_id?
You will not reach the last option ID.
I have never seen a site encounter this, to breach a BIGINT 20, you’d need 2 to the 64 options, or 18,446,744,073,709,552,000 options. That’s 18,446,744,073,709 million options, or 18,446,744,073 trillion options, or 18,446,744 quadtrillion options.
Your server will run out of memory and disk space long before this is an issue. So this isn’t worth worrying about. Keep in mind though that if you need lots of transients and you have hundreds, or even thousands of transients, this is indicative of a scaling problem in the code.
Even if we say that 400k entries will be added to the options table each day, and we have a machine with infinite storage CPU and RAM, it would still take at least 126 trillion years to reach the upper limit. The heat death of the universe may happen sooner than that.
Does WordPress have some security trigger to reset option_id or there is some other rule?
No, it isn’t necessary, or a security issue, and I doubt any WP site has ever come close to even using half of the value. This is something MySQL/MariaDB would have to handle.
Or do I just not have to worry about it?
Correct! The option ID field is a complete red herring. It is not worth your time and energy. Do not worry about it.
As a plugin author, I have to think about many things. One of my plugins uses a temporarily PHP session to save the JSON data that receives through its API in array type.
This is a problem. On most WP hosts PHP sessions just do not work. PHP sessions are incompatible with the vast majority of plugin based and server based caching mechanisms. PHP sessions also run counter to how WordPress manages its own sessions using cookies. Additionally, PHP sessions have to be stored in memory, and because of the way they work, it will struggle to scale up with traffic.
It’s also insecure, a user can meddle with PHP sessions to switch them around. You were correct to investigate alternatives.