2

I use:

SELECT ROW_COUNT()

in some MySQL procedure and I'm wondering what if my database will have many connections and the same procedure will be call dozens of times of the same time? Is it possible to ROW_COUNT() returns me number of insterted rows that was actually insterted in another procedure that is run at the same time?

So, does ROW_COUNT() returns the number of last affected rows for whole database or the current connection?

pronngo
  • 820
  • 11
  • 26
  • 2
    I would imagine that it is for the most recent statement per connection. Surprisingly, the documentation is not clear on this point. – Gordon Linoff Jan 29 '16 at 18:47

2 Answers2

2

That particular information function is specific to your session, so you'll be safe to use it while other connections/sessions are active and doing writes (they will not affect your result).

Christopher McGowan
  • 1,351
  • 10
  • 10
-1

This will depend on your database engine also. please check some useful links already provided.

https://dba.stackexchange.com/questions/31823/what-does-system-lock-mean-in-mysql-profiling-a-load-data-infile-statement

How do I lock read/write to MySQL tables so that I can select and then insert without other programs reading/writing to the database?

This will help you.

Community
  • 1
  • 1
Deepak Dholiyan
  • 1,774
  • 1
  • 20
  • 35
  • 1
    I don't think this applies to [`ROW_COUNT()`](http://dev.mysql.com/doc/refman/5.5/en/information-functions.html#function_row-count). It returns the number of rows affected by the last statement run by this connection. I wrote a whole answer about transactions, too, before realizing this. – Schwern Jan 29 '16 at 18:55
  • Hi Schwern, I was just trying to explain whole system. I also think this will not effect result – Deepak Dholiyan Jan 29 '16 at 19:00
  • @ Schwern Hehe...me too...even got out the old transaction isolation level link :-D – Christopher McGowan Jan 29 '16 at 19:00
  • Schwern and Christopher Thanks for your comment here , please vote my answer if you like this. :) – Deepak Dholiyan Jan 29 '16 at 19:04
  • 1
    Sorry, I downvoted because A) your first statement is wrong, the isolation of `ROW_COUNT` doesn't depend on your database engine (add a reference in the answer if I'm mistaken) and your links don't apply to that and B) the rest of the links are irrelevant to the question and/or the relevance is not explained. If you expanded on B under the assumption that they'll use `ROW_COUNT` in another query, that would add something (that's what I started to do). – Schwern Jan 29 '16 at 19:22
  • sorry but can you please tell me how locking will not effect the row_count? – Deepak Dholiyan Jan 29 '16 at 19:26
  • 1
    @DeepakDholiyan [`ROW_COUNT()`](http://dev.mysql.com/doc/refman/5.7/en/information-functions.html#function_row-count) only returns the number of rows affected by the previous statement *in the current session*. Locking will have no effect, there's no need to lock because it's basically reading a session variable. Perhaps you're confusing it with `COUNT`? – Schwern Jan 29 '16 at 19:30
  • 1
    Thanks for reply I appreciate – Deepak Dholiyan Jan 29 '16 at 19:33