On Thursday, October 5, 2017 at 12:35:44 PM UTC-7, Adriano Dadario wrote:
>
> Resuscitating the subject
>
> Following the Jurgens idea, I created a plugin to apply directly in models
> and datasets. As follow
>
> module Sequel
> module Plugins
> module NoLockTable
> def self.configure(model)
> model.instance_exec do
> self.dataset = dataset if @dataset
> end
> end
> module ClassMethods
> private
> def convert_input_dataset(ds)
> super.nolock
> end
> end
> end
> end
> end
>
>
> *But that don't apply in associations and joins. Are there any kind of
> instruction to apply in these others (associations and joins)?*
> *Or some place common for all queries to identify tables and apply WITH
> (NOLOCK) instruction?*
>
> The follow queries that I use, try to examplify the problem
>
> # Using plugin, apply successful
> job = Job.first
> # SELECT TOP (1) * FROM [JOBS] WITH (NOLOCK)
>
>
> # Object call create a dataset, but doesn't apply.
> job.company
> # SELECT * FROM [COMPANIES] WHERE [ID] = 38866
>
This is due to an optimization Sequel performs, which doesn't do what you
want because it doesn't respect the NOLOCK setting. You can disable it via:
Company.send(:simple_table=, nil)
>
>
>
> # Calling dataset method, adjust the query with correct statement
> job.company_dataset.first
> # SELECT TOP (1) * FROM [COMPANIES] WITH (NOLOCK) WHERE ([COMPANIES].[ID]
> = 38866)
>
>
> # Creating a dataset, create a subselect, not desired
> Job.join(DB[:companies].nolock, id: :company_id).first
> # or
> Job.join(Company.dataset, id: :company_id).first
> # SELECT TOP (1) * FROM [JOBS] WITH (NOLOCK)
> # INNER JOIN (SELECT * FROM [COMPANIES] WITH (NOLOCK)) AS [T1] ON
> ([T1].[ID] = [JOBS].[COMPANY_ID])
>
> # Create the expect join, but without WITH(NOLOCK) instruction, not
> desired too
> Job.join(:companies, id: :company_id).first
> # SELECT TOP (1) * FROM [JOBS] WITH (NOLOCK)
> # INNER JOIN [COMPANIES] ON ([COMPANIES].[ID] = [JOBS].[COMPANY_ID])
>
>
>
Is MSSQL not smart enough to optimize the subselect join?
In any case, if you really want NOLOCK everywhere:
def DB.dataset
super.nolock
end
DB.extend_datasets do
def join_clause_sql_append(sql, jc)
super
sql << " WITH (NOLOCK)" unless jc.table.is_a?(Sequel::Dataset)
end
end
DB.send(:reset_default_dataset)
Thanks,
Jeremy
--
You received this message because you are subscribed to the Google Groups
"sequel-talk" group.
To unsubscribe from this group and stop receiving emails from it, send an email
to [email protected].
To post to this group, send email to [email protected].
Visit this group at https://groups.google.com/group/sequel-talk.
For more options, visit https://groups.google.com/d/optout.
