CODEWITHSUNDEEP
mysql
Brandon
Brandon

Reputation: 889

MySQL Locking table from Stored FUNCTION

I have a function in a MySQL Database that determines some sync parameters for a mobile device. The function determines the last date/time the user synchronized with the database. During my sync operation I call this server side function twice. As soon as I call it the second time - the entire Sync_Records table is locked. I cannot write to it from any other connection anywhere (note, after first call, the table is not locked). I changed the function to a Procedure - and all is fine - no locking after the second call. The entire sync operation (including both calls to the function/procedure) is within a transaction. This is an InnoDb table.

The function/procedure simply does two select statements. They are storing results in local variables and then returning the date time variable. I don't understand why the tables are locked. Does anyone have any ideas?

Upvotes: 0

Views: 2586

Answers (1)

Alex
Alex

Reputation: 46

It is specified in stored program restrictions section

A stored function acquires table locks before executing, to avoid inconsistency in the binary log due to mismatch of the order in which statements execute and when they appear in the log. When statement-based binary logging is used, statements that invoke a function are recorded rather than the statements executed within the function. ...

In contrast, stored procedures do not acquire table-level locks. All statements executed within stored procedures are written to the binary log, even for statement-based binary logging

Upvotes: 3

Related Questions